How to calculate elapsed time in Excel

Elapsed time from a start to an end

The sheet is a support queue. Each cell in Opened and Closed holds a full date and time, which Excel stores as one number: whole days before the decimal point, the time of day after it. Subtracting two of them gives the gap in days, with the fraction as part of a day. That is why a ticket open for a day and a half shows 1.5.

Turn the day count into the unit you need: *24 for hours, *1440 for minutes. Wrap it in ROUND so 26.583333 does not leak into reports. To show hours and minutes instead of a decimal, leave the plain =C2-B2 and format the cell with Home > Number Format > More Number Formats > Custom, type [h]:mm. The square brackets let the hours run past 24; plain h:mm would show 2:35 for this ticket and silently drop the day.

Elapsed time so far: use an "as of" cell, not NOW()

For tickets that are still open, you want the time since they were opened. The obvious formula is =NOW()-B3, but NOW() is volatile: it recalculates on every edit, so the hours change while you are reading them, a printed report never matches the file, and a sort or filter by age can reshuffle as you work. Two people opening the same file see different numbers.

Put the moment you measure from in one cell instead, here H1. Type it by pressing Ctrl+; (today's date), a space, then Ctrl+Shift+; (the current time) and Enter. That is a fixed value. Every formula points at $H$1, so the whole sheet is measured from the same instant, and you refresh it on purpose by retyping H1. If you do want a live clock, put =NOW() in H1 alone and the rest of the formulas need no change.

Combine both cases in one formula: use the closed time when there is one, otherwise the as-of time. =IF(C3="",$H$1,C3)-B3 works for every row, so you can fill it down the whole column.

Days, hours or minutes

Pick one unit per column, and decide what a "day" means. INT of the gap counts full 24-hour periods. Subtracting the dates alone counts calendar days. T-5102 was opened at 4:10 PM on Nov 1: at 2:30 PM on Nov 3 that is 1 full day (INT($H$1-B3) is 1) but 2 calendar days (INT($H$1)-INT(B3) is 2). SLAs usually mean the first, "opened 2 days ago" labels the second.

For minutes, round the result: =ROUND((C8-B8)*1440,0) gives 855 for T-5107. Without ROUND, times stored as fractions of a day often come out as 854.9999999, and a later INT or comparison with = turns that into the wrong number.

A "2d 3h 15m" label

For a readable age like "1d 2h 35m", work in whole minutes first, then split them with INT and MOD: INT(m/1440) is the days, INT(MOD(m,1440)/60) the hours left over, and MOD(m,60) the minutes. Rounding to minutes at the start is what keeps the label honest. If you split the raw fraction instead, a gap of exactly 3 days stored as 2.9999999 can show as "2d 23h 59m" or even "2d 23h 60m".

LET (Excel 365 and 2021) names the minute count once so the formula stays readable. In older versions, repeat the ROUND part three times: =INT(ROUND((C2-B2)*1440,0)/1440)&"d "&INT(MOD(ROUND((C2-B2)*1440,0),1440)/60)&"h "&MOD(ROUND((C2-B2)*1440,0),60)&"m".

You may see =TEXT(C2-B2,"d \d h \h m \m") suggested. It works for short gaps, but "d" in a format means day of the month, so it wraps back to 1 after 31 days and cannot show 45d. The result of either method is text: keep a numeric hours column for sorting, totals and averages.

Negative times and overdue countdowns

If the end is earlier than the start, the result is negative, and a cell formatted as a time or [h]:mm shows ##### because Excel's default 1900 date system cannot display negative times. T-5106 was closed at 10:55 but opened at 11:40: =C7-B7 is a negative fraction of a day. Usually that is a typing mistake, so the ##### is a useful alarm. Multiply by 24 or 1440 and keep the cell as a number to see the real value, -45 minutes.

Negative times are legitimate in countdowns. Time left until the due time is Due minus the as-of cell, and it goes negative once a ticket is overdue. Show the size with ABS and say the direction in words, which never produces #####.

For the "2d 3h 15m" label on a value that can be negative, take ABS of the minutes and add the sign in front: =LET(m,ROUND((C7-B7)*1440,0),IF(m<0,"-","")&INT(ABS(m)/1440)&"d "&INT(MOD(ABS(m),1440)/60)&"h "&MOD(ABS(m),60)&"m") gives "-0d 0h 45m". Switching the workbook to the 1904 date system (File > Options > Advanced) also lets negative times display, but it moves every existing date in the file by four years and a day, so avoid it in a workbook that already has dates.

Which method to pick

For totals, averages, sorting or comparing with a target, keep the elapsed time as a number: decimal hours with *24 or a [h]:mm formatted duration. For a dashboard label people read, add the "2d 3h 15m" text in a separate column. For anything still running, measure to an as-of cell and only make it =NOW() if the sheet is a live screen nobody prints. If start and end are times without dates and can cross midnight, use the MOD fix from the over-midnight guide instead of a plain subtraction.