How to Calculate Time Differences in Excel | 5 Examples
ฝัง
- เผยแพร่เมื่อ 31 ก.ค. 2024
- 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 - วิทยาศาสตร์และเทคโนโลยี
What else do you want to learn in Excel? Let me know below in the comments!
Thank you. However... if they start at 11:00 PM to 7:00 AM on the swing shift... the formula fails.
How do you handle that? (I'm guessing you have to do a full date at that time... or some more math to determine it changed days.)
@tinytechnicaltutorials What if i wanted to know how many hours they worked, after the regular 8 hours?
Thanks for the video, but you didn't mention if the second time is next day, then how to calculate the time difference?
Bonza mate, worked like a charm, thanks muchfully :0)
Thanks heaps, mate! 😄
Thank you so much.
You're welcome! Thanks for watching! 🙏🤓🌟
Thank you so much
You bet! Thanks for watching! 😊
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!
Hi Kyrel! 👋 Maybe this will help? stackoverflow.com/questions/22187581/calculate-the-difference-between-two-dates-and-time-on-excel
Thank you
You're welcome! Thanks for watching! 🙏🤓🌟
Masha Allah (WoW)
Thanks so much! 😎🌟🙏
Thank you. Very precise tutorial. What if I need the result in days, hours & minutes?
Hi Shabir! Thanks for watching! 😊 Here's an article that should help you get days, hours and minutes: exceljet.net/formulas/get-days-hours-and-minutes-between-dates.
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?
Hi HarleyRider! Hmmm...not totally sure why you'd be getting a value error. Feel free to send the spreadsheet to info@tinytechnicaltutorials.com and I can take a look. 😊
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?
Hmmm...it really complicates things if the times are in the same cell. 😊 I found one example that might be what you need here: superuser.com/questions/1770821/time-difference-when-times-are-in-one-cell. You'd basically have to split the data at the hyphen, but it gets pretty messy. Hopefully that helps!
Is there a way to get the military time if it’s not showing up when you click format cells and time?
Hi Cara! 👋 Hmmmm...so when you do Format Cells-->Time, on the right-hand side, there's nothing like a "13:30"? Bizarre! You could try doing a custom format. Here are some examples of that: www.ablebits.com/office-addins-blog/show-time-over-24-hours-excel
Hi TTT, how would I calculate the same if I had the dates and times in different cells?
Hi Khulekani! 👋 So sorry for the slow response! Were you able to figure this out?
Hello great video! For the first example, how do you total the number of hours worked in the week?
Hi Benny! 👋 Sorry for such a slow response! Were you able to figure this out?
how do you calculate the total timing worked ? base on the above example
Hey fairyangel fairyangel! I think you're asking how to get the total time worked for the five days in my example (like a weekly total time)? Sure! You'll want to be using the "Decimal" example (see 03:13 in the video). Then put your mouse in cell C9, and on the top ribbon, "Home" tab, click "AutoSum" (should be towards the right). That should automatically detect that you want to sum up the five cells above, and should give you the total. Just make sure that C9 is using a "Number" for the format. You can check this by right-clicking on the cell, then click "Format Cells." On the "Number" tab, make sure the "Category" (on the left) is "Number." Hope that helps! :)
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.
Hey @uppimage! 👋 I do respond to comments, but have been a bit slow this week! 😊 Were you able to sort this out? You should be able to specify the format for the time under Format Cells-->Custom-->Type. Here's a write-up that might help? www.causal.app/excel/entering-or-importing-times-without-colons
how to you calculate the total timing pend ? base on the above example
Replied in your other comment. :)
What if in one cell it has10 am - 8 pm what is the Excel formula to calculate this hour
Hi Sashi! 😊 You mean the single cell has the text "10 am - 8 pm"? If that's the case, that cell would probably be treated as text, and you wouldn't be able to do a calculation on it. But let me know if I'm misunderstanding!
How to calculate difference of 20:00 i.e 8PM to 8:00 i.e 8AM?
Hi Ritesh! Sorry for the delay...if you're still looking for an answer, you should be able to update the formula to be (24-start time on day 1)+(end time on day 2). This will give you the total hours on the first day (24 total hours minus the start time) plus the total hours on the second day. Hope that makes sense!
What if i wanted to know how many hours they worked, after the regular 8 hours?
Hi Mahdi! 👋 Ooh, interesting! There are several ways you could do this. I've created a screenshot of one way here: drive.google.com/file/d/1Z8sxW6vQLi4S-4q1jdyeq5cUcXyoZEHY/view?usp=sharing. Hopefully it makes sense! 🤓
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
Hi Chad! I'm following this part: "cell B1 has a value of 6 and C1 has a value of 2. the "Difference cell" will total C1-B1 = -4." But then got a little confused. :) Can you maybe try explaining in a different way?
Ok I'll try lol
B1 = 6, C1 = 2. Difference cell (F1) = -4
D1 & E1 do not have values yet
Then when I put a value of 3 into D1, the difference cell will subtract D1 from C1, which would be -1.
Then when I put a value of 3 into cell E1, the difference cell will subtract E1 from D1, which would be 0.
Each time I put a value in a cell, I want the difference cell to subtract the new value from the previous cell.
I hope this helps!
Hey Chad! Thanks for the updated explanation! That's super helpful.
I've tried a few things, and found one that I think (??) is what you need, but I haven't thought through all possible scenarios where it might not work.
-If D1 AND E1 are empty, then difference is C1-B1
-Otherwise, check if E1 is empty
-If it is, then difference is C1-D1
-If it’s NOT, then difference is D1-E1
Here's the formula for the "difference cell" (F1):
=IF(AND(ISBLANK(D1), ISBLANK(E1)), C1-B1, IF(ISBLANK(E1), C1-D1, D1-E1))
Hope that will at least get you started! :)
@@TinyTechnicalTutorials you are amazing, thanks!
You bet! Good luck!
👍
Thank you! 🙏🤓🌟
U r super
Thank you so much!!! 🙂
what about the night shift that works past midnight?
Oooh, good one! 😊 For that, you can try 24-(StartTime-EndTime). But that would only work for the night shift times. You'd have to use the formula from the video for day shift folks (EndTime-StartTime). Hope that helps! Thanks for watching! 🙏🌟🤓
Use the "IF" formula
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?
Oooh, an example I hadn't thought about! LOL! 😁 I haven't tried it for all the different formats, but on military time and the standard AM/PM time, you can do =(24 - StartTime) + EndTime. Let me know if that works!
@@TinyTechnicalTutorials You can use the function Mod(). e.g. if you have from A1(11:00 pm) to B1 (3:00 am), then Mod(B1-A1,1) 1 being the divisor
Ahhhh, okay! Didn't know that. Thanks for letting me know! 😃
@@TinyTechnicalTutorials How to deal with negative numbers? I am converting all check ins from CST to PST some check ins are at 1:00 am when converting goes into negative and ###### because the result goes back into a yesterday at 11:00 pm. Please help thank you.
Hi Erika! 👋 I think you could update the formula to be (24-start time on day 1)+(end time on day 2). This will give you the total hours on the first day (24 total hours minus the start time) plus the total hours on the second day. Does that work?
How do you deduct 1hr lunch time..eg. 12.00 to 1.00pm or 13.00
Hi Zaldaim! 👋 Sorry for the slow response! If you haven't already figured it out, maybe this will help? www.extendoffice.com/documents/excel/3610-excel-calculate-hours-worked-minus-lunch.html
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
Ahhhh...great point! Thanks for posting this tip! 🤓🔥
Hey TTT you really sound like my favourite band KKK, but seriously though it sounds like i heard someone from a certain website of black and orange
I'm not sure if this is a compliment?? 🤔😃
@ 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!
Hi Vasilii! 👋 What are your values for Travel Start and Travel End?
@@TinyTechnicalTutorials nvmd my Long Date fromat was messed up! got it working! Thanks though!