How to Calculate Days of Inventory on Hand
(DOH) in Excel

Days of Inventory on Hand

1. Start with the base dataset

• Confirm your dataset includes:
  • Month-end inventory date
  • Unit cost and units sold or issued
  • Beginning inventory value and ending inventory value
• Identify the 4 output columns you will calculate: days in month, issues COGS, average inventory value, days on hand

2. Calculate days in the month

• Ensure the date column is a month-end date
• Use the DAY function to return the number of days for each month-end date
• Copy down the column to populate for each month

3. Calculate issues COGS

• Create a column that calculates cost of goods sold for issued or sold units
• Use simple multiplication between unit cost and units sold
• Fill down to apply for all rows

4. Calculate average inventory value

• Create a column that averages beginning inventory value and ending inventory value
• Use the AVERAGE function
• Fill down to apply for all rows

5. Calculate days of inventory on hand for each row

• Use the calculated average inventory value and the issues COGS
• Convert issues COGS into a daily amount using the days-in-month column
• Then compute days on hand using the daily amount
• Verify a few rows manually to confirm the logic is working as expected

6. Build the reporting grid for analysis

• Create a unique list of month-end dates for the rows using UNIQUE
• Format the dates to match your reporting style
• Copy and paste values to keep the list stable

7. Create the part category headers across the top

• Create a unique list of part categories using UNIQUE
• Sort alphabetically using SORT
• Convert the list from vertical to horizontal using Transpose
• Paste values so the header row stays fixed

8. Recreate the DOH calculation inside a single cell using summarized data

• Use SUMIFS to pull totals by:
  • Part category
  • Month-end date
• Use SUMIFS for:
  • Total average inventory value for that category and month
  • Total issues COGS for that category and month
• Use the DAY function on the month-end date to convert issues COGS into a daily number
• Combine those pieces in one cell so the grid calculates DOH for each month and category intersection

9. Fix references so the formula can be dragged

• Use absolute and mixed references so:
  • The part category reference updates as you drag left or right
  • The month reference updates as you drag up or down
• Use F4 to cycle through reference lock options
• Copy across and down to populate the full grid

10. Add a heat map for high DOH values

• Highlight the grid
• Apply Conditional Formatting
• Use “Highlight Cells Rules” and select “Greater Than”
• Set a threshold value (example: 80 days) to flag slow-moving inventory

11. Format the target label correctly so it does not break conditional formatting

• If you display the threshold on the sheet, ensure it is stored as a number, not text
• Use Ctrl + 1 to open formatting
• Apply a Custom number format so it displays a label like “Target: 80” while remaining numeric