Quick, clean, and to the point

Time since start in day ranges

Excel formula: Time since start in day ranges

To calculate time durations and display the result in days with custom labels, you can use the IFS function. In the example shown, the formula in E5, copied down, is:

=IFS(C5<=24,"Day 0",C5<=48,"Day 1",C5<=72,"Day 2",C5>72,"Day 3+")

Start is the named range G5, which is used by formulas in columns C and D, as explained below. 


In this example, the goal is to calculate durations in "days" starting from the start date and time in cell G5 and ending at the dates and times shown in column B. The twist is that we want to classify the durations using the custom labels shown in column E, starting with "Day 0" for the first 24 hours and ending at "Day 3+" for durations greater than 72 hours. 

The first step is to calculate the decimal hours as seen in column C. We could also work with Excel time, but decimal hours are more convenient when we calculate the day labels later below. The formula in C5, copied down, is:


We subtract the start time from the end time and multiply by 24. This works because Excel times are fractional values of days.

Next, we calculate the decimal days that appear in column D by subtracting the start time from the end time. The formula in D5, copied down, is:


This works because Excel dates are just serial numbers. These values are only for reference, and are not used in any subsequent calculations.

Finally, to calculate the day labels as seen in column E, we use a formula based on the IFS function with 4 logical conditions:

=IFS(C5<=24,"Day 0",C5<=48,"Day 1",C5<=72,"Day 2",C5>72,"Day 3+")

For each logical test, we supply a text value that works like a bucket to collect times the appropriate day range. The IFS function is new in Excel 2019. If you don't have IFS available in your version of Excel, you can use a formula that "nests" together several IF functions:

=IF(C5<=24,"Day 0",IF(C5<=48,"Day 1",IF(C5<=72,"Day 2",IF(C5>72,"Day 3+"))))

For more information on nesting IFs, see: 19 tips for nested IF formulas.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.