How to Calculate Time Differences in Excel | 5 Examples

It’s common to need to calculate the difference between dates/times in Excel—whether you’re figuring out how long someone worked on a given day, or the duration of travel across multiple days.

In a hands-on tutorial, I’ll show you five different examples of how to work with time in Excel.

00:00 – Example #1: Calculate amount of time worked using H:MM format, Military Time
01:26 – Example #2: Calculate amount of time worked using H:MM format, AM-PM
02:33 – Example #3: Calculate amount of time worked in decimal numbers
03:44 – Example #4: Calculate amount of time worked in full hours, using the TEXT function
04:44 – Example #5: Calculate time duration across multiple dates, in hours, minutes, seconds

[TinyTechnicalTutorials]

What else do you want to learn in Excel? Let me know below in the comments!

[zerandervax]

Bonza mate, worked like a charm, thanks muchfully :0)

[shabirfk]

Thank you. Very precise tutorial. What if I need the result in days, hours & minutes?

[bennyblanka]

Hello great video! For the first example, how do you total the number of hours worked in the week?

[kyrelrosli]

Hi, what if the format is dd/mm/yy, hh:mm:ss AM/PM?
For example I have 11/15/23, 9:02:43 AM and 11/15/23, 5:04:17 PM, I want to calculate the time duration in hours, how do I do that? I follow exactly like 5:01 and it still wouldn't work. Please help!

[caraghabrielledorsett]

Is there a way to get the military time if it’s not showing up when you click format cells and time?

[mlmbbei]

What if the start and End time sits in same cell. viz. 06:00 PM - 08:23 PM, and output should be 2:23. Is it possible?

[AnnistonTiger]

When I calculate this it always returns a #value error. My spreadsheet has arrival and leave time in the same column. So A1 shows arrive and A2 shows leave. If I calculate =(A2-A1) I get a #value error and no calculation. Column A is date and time and it can't be changed. How can get rid of the #value error?

[khulekanindaba]

Hi TTT, how would I calculate the same if I had the dates and times in different cells?

[camayffoo]

how do you calculate the total timing worked ? base on the above example

[uppimage]

I am just about to watch the tutorial. I see that you actually respond to comments, so I will start my thought process (and delete it if the video can answer).

My goals/needs:
I need to enter Start Time, End Time, Total Hours (decimal form) and hourly rate. I am stumped when converting to decimal form.


Start time A1 (10:30)
End time B1 (16:00)
Find the difference and have it swapped to decimal format C1 (3.5)
My formula is listing the difference as 6.30 (instead of 6.5)
At this point, I can take the decimal and multiply by the hourly wage and get my correct total.

THE ISSUE:
I do NOT want to enter the full time with colon AM/PM.
For example, 9:30 AM I enter as 930, but I want it to appear as 9:30 AM. I am comfortable with entering military time, but without the colon, and I would like it to appear in the AM/PM. I would enter 1630, and have it appear as 4:30 PM.

It think it is because of this NOT entering colons that rendered inoperable the formulas I have found on most of the tutorials. When I followed your instructions, it worked, but not when I enter time as above. This is the glitch I would like to solve.

Once I get this workflow set up, things will go so much more smoothly for the rest of the process!

Thank you in advance.

[camayffoo]

how to you calculate the total timing pend ? base on the above example

[Rahul]

What if in one cell it has10 am - 8 pm what is the Excel formula to calculate this hour

[mahdipi]

What if i wanted to know how many hours they worked, after the regular 8 hours?

[ritesh]

How to calculate difference of 20:00 i.e 8PM to 8:00 i.e 8AM?

[chadclemens]

how can i write a formula that only compares 2 cells in a given row that will have 4 values? For example, cell B1 has a value of 6 and C1 has a value of 2. the "Difference cell" will total C1-B1 = -4. then when i add a value of 3 into cell D1 of 3, the "difference cell" will total D1-C1 = 1. then when i add a value of 3 into cell E1, the "difference cell" will total E1-D1 = 0. Hope this makes sense lol

[kelseyg]

what about the night shift that works past midnight?

[SorryPogiLang]

TIPS: For computing time, you will need to use ABS function especially when the result is negative so it can be converted to positive because it will only show number sign"#" in the cell if the time value is negative

[umbnmbu]

lol but what about substracting a smaller number AM from a larger number PM, isnt this a thing to speak about or show example also?? like Start time: 9 PM and End time 2 AM... how do you do this?

[vasiliibogdan]

@ 5:33 does not give me difference. Please tell how to get difference in hours between long dates. What you have on screen. I copied - does not work!