How to Track Time to Fill in Excel and
Spot Hiring Bottlenecks Faster

How to Calculate Time to Fill (Days) in Excel

1. Set up the key hiring fields in your dataset

Start with a hiring dataset that includes:

• Job open date
• Fill date
• Role or position
• Hiring stages
• Accepted status

The two fields that matter most for this KPI are:

• The accepted column
• The fill date column

You will also need a column to calculate the month of the hire and another column to calculate the number of days it took to fill each role.

2. Create a month column based on the fill date

Use the EOMONTH function to convert each fill date into the end-of-month value for reporting.

• Base this on the fill date, not the open date
• Use zero as the month offset
• Format the result as a date so the month values display correctly

This creates a monthly grouping field you can use for counting hires and measuring time to fill.

3. Calculate days to fill only for accepted hires

Use an IF function to calculate the number of days between the open date and the fill date only when the accepted column says yes.

• If the accepted field is yes, subtract the open date from the fill date
• If the accepted field is blank, return a blank instead
• Fill the formula down through the dataset

This gives you a clean days-to-fill column that only includes completed hires.

4. Build a full month list manually

Do not rely on a unique list of filled months for this KPI.

Because some months may have no hires, a unique list will skip those months and create gaps in your reporting. Instead:

• Enter the month-end date for the first month
• Enter the month-end date for the second month
• Drag the pattern down through the full reporting period

This gives you a complete monthly timeline, even when there were no hires in certain months.

5. Count the number of hires by month

Use COUNTIFS to count how many hires were completed in each month.

Your criteria should include:

• Accepted equals yes
• Month matches the month in your summary table

Fill the formula down to generate a monthly count of hires across the full timeline.

6. Filter the days-to-fill values for each month

To calculate time to fill correctly, you need only the days-to-fill values that match two conditions:

• Accepted equals yes
• Month matches the month in your summary table

Since there is no built-in median-if function for this setup, use FILTER to pull only the matching days-to-fill values for each month.

This creates a dynamic array of valid values for each monthly period.

7. Calculate the monthly median time to fill

Wrap the filtered results inside the MEDIAN function.

• This returns the middle value for each month
• If a month has two values, Excel returns the midpoint between them
• Fill the formula down the monthly summary table

This gives you a median-based time-to-fill result for each month instead of a simple average.

8. Handle months with no hires

Some months will not return any matching values, which creates errors in the median calculation.

Use IFERROR to handle those months cleanly.

• Replace the missing-value result with NA
• This prevents unwanted zeros from appearing in the chart
• It also helps Excel treat missing months correctly in the visualization

9. Insert a combo chart for hires and time to fill

Once your summary table is complete, create the chart.

• Select the monthly summary table
• Go to Insert
• Open Recommended Charts
• Switch to All Charts
• Choose a Combo chart

Set up the chart so that:

• Number of hires appears as columns
• Time to fill appears as a line

10. Move time to fill to a secondary axis

Because the monthly hire count and time-to-fill values are on very different scales, they should not use the same axis.

• Keep the smaller metric on one axis
• Move the higher-value metric to the secondary axis

This makes the chart much easier to read and prevents the line from being compressed by the column scale.

11. Remove artificial drops in the line chart

If the line keeps dropping to zero in months with no hires, update the chart behavior.

After using IFERROR with NA, do the following:

• Right-click the chart
• Open Select Data
• Choose Hidden and Empty Cells
• Select Connect data points with line

This removes the sharp drops and makes the time-to-fill trend line flow more naturally across the months.

12. Clean up the chart formatting

Finish by improving the chart presentation.

• Format the primary axis with zero decimals if needed
• Format the secondary axis with one decimal if needed
• Update the chart title
• Adjust font size, bolding, and colors as desired

The final result is a Time to Fill (Days) chart that shows:

• Monthly hires as columns
• Median time to fill as a line
• A full monthly timeline, including months with no hires

This gives you a clean visual for monitoring hiring volume and recruiting speed over time.