Summary

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.

Explanation 

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:

=(B5-start)*24

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:

=B5-start

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.

Related formulas

Excel formula: Add decimal hours to time

Add decimal hours to time

Times in Excel are factional values of 24 hours. One hour of time is 1/24, and 1 minute of time is 1/(24*60) = 1/1440. As a result, if you have a decimal value for 6 hours, and a time in A1, you can add 6 hours of time to the value in A1 like this: =A1+(6/24) With the TIME function You can also add...
Excel formula: Add decimal minutes to time

Add decimal minutes to time

Times in Excel are factional values of 24 hours. One hour of time is 1/24, and 1 minute of time is 1/(24*60) = 1/1440. As a result, if you have a decimal value for 15 minutes, and a time in A1, you can add 15 minutes of time to the value in A1 like this: =A1+(15/1440) With the TIME function You can...
Excel formula: Convert decimal minutes to Excel time

Convert decimal minutes to Excel time

In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Minutes Value Time 1 1/24 60 0.04167 1:00 3 3/24 180 0.125 3:00 6 6/24 360 0.25 6:00 4 4/24 240 0.167 4:00 8 8/24 480 0.333 8:00 12 12/24 720 0.5 12:00 18...
Excel formula: Convert decimal hours to Excel time

Convert decimal hours to Excel time

In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Value Time 1 1/24 0.04167 1:00 3 3/24 0.125 3:00 6 6/24 0.25 6:00 4 4/24 0.167 4:00 8 8/24 0.333 8:00 12 12/24 0.5 12:00 18 18/24 0.75 18:00 21 21/24 0...
Excel formula: Calculate number of hours between two times

Calculate number of hours between two times

The complexity of calculating the number of hours between two times stems from times that cross midnight. This is because times that cross midnight often have a start time that is later than the end time (i.e. start at 9:00 PM, end at 6:00 AM). This article provides several formula solutions,...
Excel formula: Get days, hours, and minutes between dates

Get days, hours, and minutes between dates

Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date. TEXT(C5-B5,"h"" hrs ""m"" mins """) This is an example of embedding text into a custom number format, and...
Excel formula: Time duration with days

Time duration with days

In the example shown, the goal is to enter a valid time based on days, hours, and minutes, then display the result as total hours. The key is to understand that time in Excel is just a number. 1 day = 24 hours , and 1 hour = 0.0412 (1/24). That means 12 hours = 0.5, 6 hours = 0.25, and so on...

Related functions

Excel IFS function

IFS Function

The Excel IFS function can run multiple tests and return a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas....
Excel IF function

IF Function

The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions