The tutorial explains different ways to calculate times in Excel and demonstrates several methods of adding times and calculating time difference. You will learn a few useful formulas to sum times and add hours, minutes or seconds to a given time. Continue reading
Comments on: How to calculate time in Excel - time difference, adding / subtracting times
by Svetlana Cheusheva, updated on
Comments page 5. Total comments: 622
I am needing a formula that subtracts hours worked from 40. I am needing to know the left over hours. Basically I need it to subtract worked hours for the week let’s say 25.50 from 40 hours allowed for the week and give me the sum of that. So 40 - 25.5 =14.50
Hello Amber!
If you enter working hours in the hh:mm:ss format, you can find the sum with the help of ordinary addition. Suppose you type the time in cells B2:B7 and get the sum in cell B8. However, when you sum several time intervals, the result can turn out to be over 24 hours. In this case, Excel resets the sum to zero and starts the operation anew. To show the correct sum of working hours, please apply a special format to B8: open the Format Cells dialog window, go to Number -> Time and choose "37:30:55" from the Type list. Then enter 40:00:00 which is your maximum working time in a separate cell, e.g. C1. After that, change the formula in cell B8 to see the left-over hours:
=IF(C1 < SUM(B2:B7), "Over 40 hours on "&TEXT((SUM(B2:E7)-C1), "hh:mm"), C1-SUM(B2:B7))
Ok, Thanks for your response, however I was not able figure out where to put that, should I put it in the very beginning? I just couldn't get it to work.I have IN, OUT, IN, Out, and if I put in say 6:30 for the first in without any of the others populated, the total time says 17.25 Hours, and I would like it to say 0 until I enter a out time. Thanks a lot for helping!
Hello Amos!
Please use the formula below:
=IF(COUNTA(C3:F3)>1, ((IF(C3>D3, D3+1, D3)-C3)*24)+(IF(E3>F3, F3+1, F3)-E3)*24, "")
Hi Alexander, Thanks so much for the formula that you posted, and it works great for when I enter the IN time in the morning, but when I enter the IN time after lunch it shows the 16.5 hours again. I suppose I just need to add something? I am lost when it comes to these long formulas. So I have IN, OUT, IN, OUT I enter the time to the closest 15 minutes, then the total column shows the hours. Please let me know if you have any thoughts to get the time to show only the hours in the forenoon if I enter the IN time in the afternoon. Thanks!
Ok, so what I would like is if G3 (which is the total hours) does not show a total unless D3 or F3 is populated. Currently I have this formula in G3.
=IF(COUNTA(C3:F3)>1, ((IF(C3>D3, D3+1, D3)-C3)*24)+(IF(E3>F3, F3+1, F3)-E3)*24, "")
Currently it shows 17 Hours if I have a time in E3 but not F3. It seems like C3 and D3 are working exactly how I want them. Thanks a lot! Amos
I have a formula to calculate time in excel, which works good, but if I clock in at say 6:30 AM, (cell, C3) it shows a total time, even if the other cells are empty. Is there a way to not calculate time until more then one cell is populated? It is to clock in and out, AM and PM. The formula is,
=((IF(C3>D3,D3+1,D3)-C3)*24)+(IF(E3>F3,F3+1,F3)-E3)*24
Can someone please help me with this, or is there a better way of doing it? Thanks!
Just add some more nests to your formula to check if D3 and F3 are empty.
=If(D3="","") will check D3 and if it is blank will make the cell with the formula in it blank as well. Build this check into your existing formula and you will get what you are after.
I have a schedule with start times and finish times.
I made separate column for how many hours each day.
How do I add these times up to find how many hours per week?
From there I will need to make a separate sheet to calculate hours over 80 per pay period.
I thought it would be something like this:
=TEXT(R5:X5, "h:mm") but it isn't working
Hi Beth
you have made your result to be TEXT and you cannot calculate with text.
Also when a result is more than 24 hours you have to be careful with formats otherwise Excel will not display what you are expecting. Use cell format "General" or "Number" but not time. If you use time everything goes back to zero after 23:59 like a clock.
Apart from that, then calculate as normal =sum(R5:X5) or if you really want to =Sum(R5:X5,"[h]:mm") The bracket around the h tells excel to go beyond the 24 hour constraint it normally uses and you need that. Make sure R5 through to X5 are numbers not text as well and watch those formats, it can mess up your calculations big time.
I have start time and total hours worked for the day. How can I get the end time?
Hello Cindy,
To find out the exact end time, just use the formula below:
=A1+TIME(B1, 0, 0)
Where A1 is the start time and B1 is the total hours to work.
Hello Alexander, Im working on an overtime sheet. Where I need to calculate times between(16:00 to 21:59) & (22:00 to 07:59) in different columns
i given one small example for this comment box
K Column 8:30 to L Column 10:00 =IF(K3>L3,1+L3,L3)-K3
M Column Result is 1:30
But in the Same M Column I need time difference in Minute in the same Cell like 90 minute
Change your formula to =(IF(K3>L3,1+L3,L3)-K3)x1440 and you will get your answer in minutes.
1440 being the number of minutes in a day.
Hello:
If I have start time and end time in one cell, how can i get the toal # of hours. For example: in one cell I have: 05:00 pm - 10:00 pm. How can i get the total # of hours which is 5 hours?
Thank you so much!
Is the end time always on the same day as the start time?
Total Hours = (End time - Start time) * 24
For example -> C1 = (B1-A1)*24
Where:
A1 = Start time
B1 = End time
C1 = Total Hours
Make sure the number format for the total hours cell is general or number.
Using the 12 hour format, I am struggling to calculate the duration of hours when the start time begins at PM and the out time ends at AM. For example, if I wanted to find the duration of hours from 12:30AM(A1) to 3PM(A2), I would use the formula =(A2-A1)*24, which is 14.5 hours. BUT this does not work if I were to calculate the duration from 3PM(A1) to 12:30AM(A2), which is 9.5 hours but formula =(A2-A1)*24 will give me -14.5. What formula would I use to find the difference of time from PM to AM?
You can use the formula =(IF([@Start],[@End],[@End])-[@Start]) and write the end time as over the 24 mark rather than going back to 00:00:00. For example: start= 23:00:00, end= 25:00:00, and the duration it gives you will be 02:00:00.
The formula provided by Mary also works! If you want the same formula for the whole collumn though, that's what I use.
Hello Brenda,
Here is the formula to solve your task:
=(IF(A2 > A1, A1+1, A1)-A2)*24
Need to perform a calculation on an employee schedule (utilizing the 24 hour clock) where start time is 19:00 (A11) and end time is 03:00 (A12) and get an answer that is not in negative hours ( =SUM(A12-A11) currently returns -16). When I use the hours 16:30 (B11) and 23:30 (B12) that do not span midnight (2400) then the calculations are correct ( =SUM(B12-B11) currently returns 7:30), which I can easily convert to 7.5 hours, any suggestions without having to program the dates into the start times?
Hi Woody,
just add 24 hours if the time will be negative :
=IF(A12<A11,A12-A11+24,A12-A11)
expl : check if value A12 is less then A11; if so add 24 to the difference, else just use the difference.
Think it will work for you.
This worked.
But when we SUM such results with SUM formula, it doesn't show the right value.
hI,
if I want to Calcutta the total working hours for an employee excluding break time. how I can do this? i.e. below
DUTY IN 23:00
BREAK OUT 0:30
IN 1:00
BREAK OUT 5:00
IN 6:00
LEFT 11:00
act WORKING HRS-....
Thanks
Calculate In to Out time.
In your case
3 time periods
Add all 3 of them.
If all employees strictly use 2 breaks in his shift.
Hi,
I need to be able to get the time worked from the start time, to the end time, 22:00 hrs start and 06:00hrs end.
Terry
Change the format in cells b2 and c2 to custom [h]:mm:ss
=IF(C2>=B2,C2-B2,C2+1-B2)*24
In cell B2 type in your start time
In cell C2 type in your end time
Copy and paste the top code in Cell D2
Thank you So Much!
You helped me a lot!
OMG!!!! You just saved me so much time!! This formula worked PERFECTLY!!! THANK YOU!!!
I would like to calculate elapse time how do I write a formualae to reflect that for e.g. start time 8:30 a.m. end time 7:40 p.m
=SUM(C9-B9)-D9
in C9 type your start time
In B9 type in your end time
Copy and paste the top line in cell D9
Wouldn't that cause a circular reference?
C9: 8:30
B9: 19:30 (which baffles me to put the End time in B and Stat time in C...?)
Then, in D9, we subtract those two AND subtract D9 itself from that?... ???
Plz help me in excel sheet i want total no of hours worked formula
See below..
Hi,
I tried to used all possible formula to calculate time difference , but the return is Error
I have used formula =$I2-$H2 or =INT(I4-H4) & " days, " & HOUR(I4-H4) & " hours, " & MINUTE(I4-H4) & " minutes and " & SECOND(I4-H4) & " seconds"
Both gave me Error
Here is example:
Start Time End Time MTBF
4/29/19 18:24:10 4/29/19 23:19:56 #VALUE! (use formula =$I2-$H2 or
I think I find the answer...
I use simple combine formula =A2+B2 , instead of formula =TEXT(A2,"m/dd/yy ")&TEXT(B2,"hh:mm:ss") , then the time difference formula =INT(D2-C2) & " days, " & HOUR(D2-C2) & " hours, " & MINUTE(D2-C2) & " minutes and " & SECOND(D2-C2) & " seconds" is working perfectly.
Sum of hours is not correctly shown: 09:20 + 08:45 + 09:22 + 08:56 + 06:08 should come to 42:31 but using the SUM function it calculates as 18:31. Why & How?
Hi Siddharth,
To display times over 24 hours, a special number format [h]:mm should be applied to the formula cell. You can find the detailed explanation with step-by-step instructions and screenshots in this tutorial:
How to show over 24 hours, 60 minutes, 60 seconds in Excel
Thank you Svetlana, it worked.
Kindly provide me a formula for from time - 9:45:00 PM & To time 1:15:00 AM, want time difference?? Using =xX9-XX9 formula. getting results ########
Charles,
Hash symbols are displayed if either the formula cell is not wide enough to accommodate the result or the result is a negative value. If the former, just make the cell wider. If the latter, please see How to calculate negative times in Excel
I am unable to calculate 24 hours time in excel if anybody knows please help me...
Hi Sunil,
Hopefully you will find an answer here:
How to add & subtract time in Excel to show over 24 hours, 60 minutes, 60 seconds
Beginning Time = 8:20 PM
Ending time = 4:32 AM
(Over Nite Sleep in hours/minutes)
I'm a beginner. Thanks
Bob
Hello, Bob,
If your cells contain the time values only, please try to use the formula like the one below:
=IF(Ending Time < Beginning Time, Ending Time+1, Ending Time) - Beginning Time
Just change the Beginning and Ending Time to your cell references.
Hope this is what you need.
I plot out the wildlife sightings from my trial cam. Specifically, I obtain the time and date attributes of each .jpg photo and plot them out. But, after daylight savings time I have to subtract one hour from the time. I have that figured out, but when the time and date are 11/11/2018 0:38, for example, subtracting one hour should be 11/10/2018 23:38. But instead I get ########… Also, the correct response needs to be in a plottable format, not text. I'm stumped. Please help.
Hi Dave,
Assuming the original time is in A2, you can use this formula to subtract 1 hour:
=A2-(1/24)
If the formula cell displays ########, just make the column a little wider to accommodate the date/time value.
Caption under figure in Formula 4 should say "less than" it seems.
Hi Dave,
Absolutely so. Fixed, thank you!
Cell A1 is having 20-09-2018 18:14:10
Cell B1 is having 20-09-2018 20:00:30
Objective is to find the time difference and used B1-A1.
But error is coming like this #VALUE!
Appreciate if you can suggest solution to get the time difference.
Siva:
I believe this is a formatting issue. Your Excel doesn't recognize 20-09-2018 as a date.
So, then the idea is to get the date formatted in a way your Excel sees as a date. The way it stands, Excel sees it as text.
Try changing the two dates to 9/20/2018, leave the time as is and see if you can then simply subtract the cells using B1-A1. If that works then you need to work on the formatting issue.
Try changing the formatting of the cells to the date using the location option. In the Date there's the option to specify a locale or location. Find yours and change it there.
You can try replacing the "-" with "/" and see if that works.
If these approaches don't work, then you can split the dates and times into two columns, then split the dates into three columns and concatenate them into one cell using DATEVALUE, format the cell as Date subtract the cells and Bob's your uncle. This sounds harder than it is, in fact I used this last approach many times with data sets containing thousands of rows of data.
Let me know how it goes.
Cell A1 is having 20-09-2018 18:14:10
Cell B1 is having 20-09-2018 20:00:30
Objective is to find the time difference and used B1-A1
As per your suggestion, I changed the date part to 09/20/2018 and applied B1-A1, it worked. But it is difficult to change it manually in all cells. Tried to format the cell using the location option. It didn't work if I replace the "-" with "/". Please suggest workable solution. My file is having thousands of rows of data with this format.
I have a question. I work in payroll at my work and we have 2 shifts. Is there an easy way to calculate differential time. What I mean is like this
We have shifts that start at 14:20 and the differential kicks in at 18:00 and they work to 22:50. Is there a way for excel to calculate the amount of differential time that they worked?
It would be nice if there was a way to do total time and differential time but since the guys cant make up their mind when they want to go to lunch it makes flat calculations hard. It is always nice to have the computer back up your numbers too.
Adam:
Do the employees clock in and out during their shifts?
Are these times recorded in Excel?
What's the differential pay?
Hai good day
Dear plz tell me how Sum in excel sheet day,hour,and minutes
Exampla: "4 days 3 hours 45 minute and 16 second"
Riaz:
What other times do you want to sum?
How can I get an AVERAGE of start times, Cleaning crew starts cleaning equipment at different times due to production:
Mon cleaning crew started at 12:50AM (entered as 00:50)
Tue cleaning crew started at 12:15AM (entered as 00:15)
Wed cleaning crew started at 12:10AM (entered as 00:10)
Thu cleaning crew started at 23:45PM (entered as 23:45)
Fri cleaning crew started at 23:50AM (entered as 23:50)
I need an AVAERAGE start time for the week.
Using AVERAGE(D4:D8) gives me 09:46AM. I'm looking for a time closer to 00:16. I appreciate your help, thank you in advance.
Greg
Greg:
Try entering the time as 12:50, 12:15, etc. and then AVERAGE. When I do this the return is 12:10.
I just want to add durations (D5 through D308)...I need a total in hrs, mins, and seconds. DOes anyone know a formula for this?
Joe:
If the data in D5:D308 is formatted as Custom [h}:mm:ss
you should be able to sum them with SUM(D5:D308) or whatever combination of cells you need to sum.
Right click, Format Cells, Custom and choose the [h]:mm:ss option.
Thanks Doug, I've been doing exactly as you prescribed and I'm only getting 0:00:00. I'll gladly try anything else you might have. Thanks
Desperately need formula for the following:
If < 6.05, then subtract .50
Thanks
Darren:
Where the data is in cell A1 the formula is:
=IF(A1<6.05,(A1-0.5),"Number is Bigger than 6.05")
If your data is in another cell, enter that cell address in place of A1.
After you've entered the formula in the appropriate cell, you can copy it down the column to calculate more cells.
2-May-18 4:35 1-May-18 14:19 HOW CAN I CALCULATE DIFF IN HOUR
Manoj:
The formula is:
=((End Date + End Time)-(Start Date + Start Time))*24
i want to know how to calculate time when u have a negative sign. see example below:
if
start time end time Hours worked
7:45am 3:15pm ???
so what is the way forward?
Hello, Andrew:
There are three ways negative time can be displayed in Excel. The first two ways are explained in the article above this post, so no need to rehash them here. I would recommend you use one of them.
The third way is to custom format the cell holding the negative time as -h:mm. This method works only if you always want a negative time value displayed. It also requires that you always subtract the earlier time from the later time. This means times returned really will be positive and will only appear negative. Probably not the best method.
I am hosting a running event where people guess how fast they can run a mile, then they run the mile, and I have to be able to calculate the difference between their guess and what they actually ran, but I can't figure out how to format the cells because everything is in time as it relates to the day. I type in 5.22 to represent 5 minutes and 22 seconds but no matter what I put as the format it changes it to 5:22 am or pm and throws off the calculation. Can someone please help???
Tiffany:
If you change the format of the cells that will contain the times to h:mm the cells and the subsequent calculations will work the way you want.
Select the cells, then right click and choose Format Cells, then choose Custom where you will find the h:mm option in the list. I know it means "hours:minutes" but maybe for your purposes it will work.
Hi there,
I am trying to calculate the time difference of the following function on my excel spreadsheet;
=TEXT(E4-D4,"HH:MM:SS") and i press enter but it gives me #VALUE
what does that mean and if you can help solve this...it took me an hour to fix this
thanks
bobby
Bobby
Bobby
What do you have in E4 and D4? Are the values in there formatted as time?
Hello, please help me with the formula for below:
Example 1:
If email Time received Date & Time =25/5/2018 18:00 Hrs and email processed Date and Time is 26/5/2018 14:15 Hrs
Example 2:
If email Time received Date & Time =25/5/2018 22:01 Hrs and email processed Date and Time is 26/5/2018 14:15 Hrs
The time should calculate total hours taken only between my shift is 13:00 to 22:00 Hrs. If email received is after 22:00 hrs the total time taken to process should calculate from next day 13:00Hrs
Hi Doug, any thoughts here?
Ramana:
After much searching I found a solution that works for your situation and several others, too.
The solution is waaaay too complicated to type here, so I'll provide the link.
You'll want to download the sample workbook that is at the bottom of the article as it is really nice and contains all the examples and formulas on several sheets.
To download the workbook click the enlarge icon at the bottom right of the embedded worksheet. When you can see the big sheet, click on the download button at the top of the sheet.
The link to the site is: https://www.exceltactics.com/calculate-net-work-hours-using-networkdays/
I'm using the following formula to calculate the number of working hours.
The result is a decimal number which can be later multiplied by the hourly rate if you need to.
For ex.
8h 15min = 8.25hrs
You can also enter the time over the midnight.
Ex.
Start 20:00 Finish 04:00 = 8.00h
=IF(E10=0,0,IF(E10>D10,(E10-D10)*24,(24-D10*24+E10*24)))
D10 - Start time
E10 - Finish time
Time format 12:00:00
Hi Yuriy Roshchupkin,
I tried your formula with the exact data that you have and it gave me a zero as the answer. The Time format is the same, is there something that I am missing?
Hi Yuriy Roshchupkin,
can you give me the formula for to change 8h 15min to 8.25hrs.
Thanks
Aditya
thanks a lot.
this one im looking for 1hour
Hi
Good Day
Please help me in excel sheet
Start Time Finish Time Mints
07:00am 17:00pm ?
Note: we don't want create any new calm & line between Start Time and Finish Time.
use this formula to calculate diffidence between both of time .mod(finish time-start time,1)
Change 17:00pm to 05:00pm and it should work. 1700 is 24 hour format while 05:00pm is 12 hour format. You cannot calculate using two different time formats.
hi,i have problem on time calculation:
have to find the elapsed time example.
start time= 23:45, end time= 02:15.answer must 2:30 .
but it is not work. pls help
Hello,
If I understand your task correctly, try to use one of the following formulas:
=TIME((HOUR(B1)+24)-HOUR(A1),MINUTE(B1)-MINUTE(A1),0)
or
=(B1+24)-A1
Where cell A1 is “23:45”, cell B1 is “02:15”
Hope it will help you.
hello got an easy one for you.. hopefully.
i have worked out my formula to calculate hours worked in total for a day eg;
06:00 - 16:00 = 10 works fine. however when the time goes past midnight it doesnt work because its a negative eg;
18:00-06:00 should be 12 hours but comes up #########
Hello,
Please try the following formula:
=(VALUE(A1)+1)-VALUE(B1)
Hope it will help you.
Hey,,
Thanks for the information you shared really useful!
But let's say that we have this table
Time of sample Seconds between samples*
9:30 5550
12:35 9450
14:45 6150
16:00 7650
19:00 5400
*assuming start sampling time 9:30 and end sampling time 19:00
How I will find the Seconds between samples as above using formulas in excel??
Hello,
If I understand your task correctly, please try the following formula:
=TEXT((HOUR(VALUE(A3)-VALUE(A2))*3600)+(MINUTE(VALUE(A3)-VALUE(A2))*60),"@")
Hope it will help you.
Hi,
I have a problem of summing spent time like below
An employee has spent time as below in two ocation
7hr and 48mnt
52 mnts
Altogether should be 8hrs and 40mnts
I have summed as below but it doesnt come
7.48+.52=8
How do i take 8.4 (8hrs and 40 mnts) as answer.
Appreciate your advice,
Thank You
Hello, Ruwanara,
Please try the following formula:
=TIME(7,48,0)+TIME(0,52,0)
Hope it will help you.
Hi I am working on a project where I would like to know the time duration between to given dates/times - HOWEVER I would like this to be based off of a work day (8 hours). So for example -
A1:3/13/2017 8:47 AM *Creation Date
B1:3/16/2017 7:29 AM *Closing Date
C1:7:00 AM *Time in each day
D1:4:00 PM *Time out each day
I would also like this to exclude weekends if possible.
I need the time difference between A1 and B1 based off of work day.
Any tips would be appreciated
Hello, BRITTANY,
Please try the following formula:
=IF(NETWORKDAYS(A1+1,B1-1)>0,NETWORKDAYS(A1+1,B1-1)*8,0)+HOUR(IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),0,IF((TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1))))+HOUR(IF(OR(WEEKDAY(B1)=1,WEEKDAY(B1)=7),0,IF((TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)))) & " hours " & MINUTE(IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),0,IF((TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1)))) + MINUTE(IF(OR(WEEKDAY(B1)=1,WEEKDAY(B1)=7),0,IF((TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)))) & " minutes"
Hope it will help you.
WHAT FORMULA DID I USE FOR THIS FORMAT
16:00 22:00 = 5
Hello, MARCO,
If I understand your task correctly, please try the following formula:
=HOUR(TIME(22,0,0)-TIME(16,0,0))
Hope this will help you!
Hi
I'm trying to produce a production schedule for a manufacturing plant how can I add production-time to calculate finish time/date. From this I need to subtract weekends and non production hours ie 10pm-6am.
Example
Start date & Time = 8/10/17 & 06:10
Total production time = 148hrs
End date & time (minus weekends and hours between 10pm-6am) = ?
Hi, Dave,
our tech specialist has come up with the following formulas for you:
1) if A1 contains start date & time, and A2 contains 148hrs, here's a formula to show day/month/year:
=TEXT(IF(VALUE(TIME(HOUR($A$1),MINUTE($A$1),SECOND($A$1)))+VALUE(TIME(HOUR($A$2/(22-6)),0,0))<=VALUE(TIME(22,0,0)),WORKDAY.INTL($A$1,INT($A$2/(22-6)))+(($A$2/(22-6))-INT($A$2/(22-6)))+VALUE(TIME(IF(HOUR($A$1)<6,6,HOUR($A$1)),MINUTE($A$1),SECOND($A$1))),WORKDAY.INTL($A$1,INT($A$2/(22-6))+1)+VALUE(TIME(6,0,0))+(VALUE(TIME(IF(HOUR($A$1)>22,22,HOUR($A$1)),MINUTE($A$1),SECOND($A$1)))-VALUE(TIME(22,0,0))+($A$2/(22-6)-INT($A$2/(22-6))))),"DD/MM/YY hh:mm AM/PM")
2) this one to show month/day/year:
=TEXT(IF(VALUE(TIME(HOUR($A$1),MINUTE($A$1),SECOND($A$1)))+VALUE(TIME(HOUR($A$2/(22-6)),0,0))<=VALUE(TIME(22,0,0)),WORKDAY.INTL($A$1,INT($A$2/(22-6)))+(($A$2/(22-6))-INT($A$2/(22-6)))+VALUE(TIME(IF(HOUR($A$1)<6,6,HOUR($A$1)),MINUTE($A$1),SECOND($A$1))),WORKDAY.INTL($A$1,INT($A$2/(22-6))+1)+VALUE(TIME(6,0,0))+(VALUE(TIME(IF(HOUR($A$1)>22,22,HOUR($A$1)),MINUTE($A$1),SECOND($A$1)))-VALUE(TIME(22,0,0))+($A$2/(22-6)-INT($A$2/(22-6))))),"MM/DD/YY hh:mm AM/PM")
Hope this helps!
Need help with how to calculate duration between date with time, less 30 minutes for example:
A1 08/22/17 8:00 am - B1 08/20/17 8:30 am = 2 days
Thanks
Maggie
Hello, Maggie,
Please try out the following formula: =ROUNDUP((A1-B1)*24,0)/24 & " days"
Hope this will help.
Hi Ekaterina,
Thanks for the help but it's not calculated proper. Maybe, I did not explain well, for example:
A1 5/2/16 17:32 - B1 5/3/16 19:59 should return 2 days not 1 day
Regards,
Maggie
I am trying to sum some values which are in word forms (0 Days, 1 Hours, 10 Minutes + 0 Days, 17 Hours, 54 Minutes + 0 Days, 0 Hours, 12 Minutes). Can you help me to sum these values?
Hello, Komal,
you need to create a custom time format that will allow summing the values when they're written as you need. You can read how to create such a format in our article.
Hope it helps.
Help! I do not know what is wrong with my formulas.
I am doing a time sheet,
all cells are in as time 00:00;
time in & time out= total (=sum(B1-A1))
That all works, but when I want to total the week, (=sum(C1:C7))
the total does not add up, if I enter one day at a time, the first 2 rows add correctly but the third row is subtracting the total from itself. the same happens for the next 3 rows. The end result is 8 hours which is what is entered on row 7.
Thank you for any help you can provide
Hi There,
in total cell change format Ctrl 1 go to custom and select (hh):mm.
what formula should i use to get time difference in quarters like 7:00AM - 8:15AM = 1.25 ? IF 15min equal to .25 and 30 mint to .50 ?
If 7:00am is in cell A1 & 8:15am in cell B1 formula should be =sum(b1-a1)*24 hope this helps (don't forget to format the results cells as "number")
I am trying to calculate the sum of two time(S).
I tried to add 15:05:30 and
14:02:00 and used the formula =sum(DE19:DE20)resulting into 5:07:30. After using =sum($DE$19:$DE$20), I got the same result. Please help.
Also please tell me if I want to add multiple timestamps each timestamp of the format(day hour:month:second),which formula I should apply?
Your expected results would be 29:07:30. Since one day is 24 hours, the balance you see is 5:07:30. It is a question of formatting the results (Control+1). Try using a "custom" format of [h]:mm:ss.
I need to add seconds with the final result of minutes and seconds i.e. 30+30+15+5 which sums to 1 minute and 20 seconds and should display as 1:20. Anyone have any idea of a formula that will work?
Enter your seconds in col A as numbers.
=SUM(A:A)/(86400) {formatted as "m:ss"}
I'm trying to create a time sheet to calculate the hours for the employee where I need to round up In time and out time by quarters and deduct lunch break. For example
In time Break In Out Out time
08:09 = 8:15 13:00 13:50 17:38 = 17:45 Total???
How can I formulate the last column into calculate the total hours worked?
Thank you!
Viktoria,
Per the rules as stated (starttime 8:00 = 8:00, starttime 8:01=8:15; similar for stoptime; breaktimes not adjusted to quarter-hours), this should work for the Total column (E):
=(HOUR(D1)+CEILING(MINUTE(D1)/15,1)/4)/24-(HOUR(A1)+CEILING(MINUTE(A1)/15,1)/4)/24+B1-C1
I answer phones virtually and I am trying to calculate the total # of mm:ss.
Each call I take has a total talk time. So like I took 6 calls.
4:23 4:23
1:45 6:08
2:24 8:32
2:00 10:32
5:36 16:08
10:52 3:00 (This should be 27:00). But anything over 24:00 won't calculate correctly. So how do I fix this?
You think you're entering minutes:seconds, but you're actually entering hours:minutes, which is why anything over 24 minutes goes haywire.
Format the columns as "mm:ss", but enter the data as h:m:ss - in other words enter 0:5:36 instead of 5:36. Then just subtract col A from col B.
02:00 10:32 08:32
i need over time formula
eg.
a2 09:00 b2 18:00 (c2 ans =a2-b2 we get 09:00)
but we need over time after this result we want -08:00 hour working day
if i do b2-a2-08:00=######
what is the perfect formula for over time result will you help me please
=B2-A2-8/24
(subtract 8/24 of a day rather than 8 hours)
I need to figure out the total time worked by an employee. we have a program that time stamps every action they make. I need to subtract any time gaps over 15 minutes and then total the time. Please help.
16:21:47 '06/25/2016
16:21:43 '06/25/2016
16:21:37 '06/25/2016
16:21:37 '06/25/2016
16:21:37 '06/25/2016
16:21:34 '06/25/2016
16:20:43 '06/25/2016
16:19:57 '06/25/2016
16:19:57 '06/25/2016
16:19:57 '06/25/2016
16:19:50 '06/25/2016
16:19:48 '06/25/2016
16:19:48 '06/25/2016
16:19:47 '06/25/2016
16:19:36 '06/25/2016
16:19:35 '06/25/2016
16:19:14 '06/25/2016
16:18:58 '06/25/2016
16:18:52 '06/25/2016
16:18:46 '06/25/2016
16:18:46 '06/25/2016
16:18:21 '06/25/2016
16:18:14 '06/25/2016
16:17:55 '06/25/2016
16:17:55 '06/25/2016
16:17:55 '06/25/2016
16:17:55 '06/25/2016
16:17:55 '06/25/2016
16:17:55 '06/25/2016
Add column B for gaps of 15+ minutes:
=IF(A1-A2>1/96,A1-A2,"")
Then hours worked =LARGE(A:A,1)-SMALL(A:A,1)-SUM(B:B)
{this presumes the same date for all timestamps}