# Calculating working time between 2 date/time stamps in Excel

July 12, 2016 at 00:47:36
Specs: Windows 7
 Hi,I need to calculate the difference in working days / hours between 2 date / time stamps.Some simple example data which spans a weekend.01/07/2016 18:0004/07/2016 09:30(Working hours are 08:30-17:00 Monday - Friday)In this example the first date / time stamp is when an email was recieved (out of hours) and the second is when the email was replied to (1 hour into workiing hours) So the formula should return 00:01:00:00 (dd:hh:mm:ss)Thank you for your help!!

See More: Calculating working time between 2 date/time stamps in Excel

#1
July 12, 2016 at 08:08:48
 It's a rather lengthy formula and needs explanation, so see here:http://www.cpearson.com/excel/DateT...If you have further questions, please ask.MIKEhttp://www.skeptic.com/

Report •

#2
July 13, 2016 at 01:38:17
 Hi Mike,Thank you veyr much for your reply and the link. The formula don't seem to be returning the figures I need though. Is there any way I can please email you over my spreadsheet for you to take a look at? Thanks,Darren

Report •

#3
July 13, 2016 at 05:34:37

Report •

Related Solutions

#4
July 13, 2016 at 07:11:52
 I got a chance to plug your numbers into the formula, and I get 0's also. Let me work on it and I'll post back when I figure out the problem.MIKEhttp://www.skeptic.com/

Report •

#5
July 13, 2016 at 13:34:44
 OK, try this.First we need to Define Names for three cells:(Somewhere out of the way like cells AA AB & AC)In cell AA enter: The Time your work day Starts, IE 8:30 AMIn cell AB enter: The Time your work day Ends, IE: 5:00 PMIn Column AC enter: Any Holidays that need to be accounted for,IE 01/01/2016, 12/25/2016, etc. etc.Next we will give Names to each cell, that way if your start time changes,or your closeing time changes, this stuff is not hard coded into the formulas.How to Define Name in 2007:On the Ribbon, Select FormulasSelect Define NameIn the Name Box enter: DayStartIn the Refers to Box (At the bottom) enter: =Sheet1!\$AA\$1Click OKOn the Ribbon, Select FormulasSelect Define NameIn the Name Box enter: DayEndIn the Refers to Box (At the bottom) enter: =Sheet1!\$AB\$1Click OKOn the Ribbon, Select FormulasSelect Define NameIn the Name Box enter: HolidaysIn the Refers to Box (At the bottom) enter: =Sheet1!\$AC\$1:\$AC\$15Click OKModify the Holiday range to suit your needs.If you have no holidays ( I feel bad for you ) and you can just leave cell AC1 blank.Now with your data looking like:``` A B C 1) Start Date End Date Time Span 2) Friday July 01, 2016 18:00 Monday July 04, 2016 9:30 0 Days 1 Hours ```In cell C2 enter the formula:=NETWORKDAYS(A2,B2,Holidays)-2&" Days "&IF(MOD(A2,1)>DayEnd,ROUND((MOD(B2,1)-DayStart)*24,2)&" Hours",ROUND((DayEnd-MOD(A2,1)+MOD(B2,1)-DayStart)*24,2)&" Hours")You can drag the formula down as many rows as needed.It's a bit long, so you might want to just copy/paste from here.See how that works for you.MIKEhttp://www.skeptic.com/

Report •

#6
July 14, 2016 at 00:22:34
 That is amazing! Works like a dream! Thank you Mike.I have a spanner to throw in the works however as now having added it to my spreadsheet i have realised that the working hours for Friday are slightly different- 08:30 - 16:30. Is there a way of adding that in?Thank you very much!

Report •

#7
July 14, 2016 at 00:39:40
 Hi Mike, I have just added more data and unfortunately it doesnt appear to work for data on the same day;11/07/2016 08:40 11/07/2016 09:40 -1Days9.5Hours13/07/2016 10:00 13/07/2016 10:30 -1Days9HoursThe 1st one should return 0Days1HoursThe 2nd one should return 0Days0.5HoursThanks

Report •

#8
July 14, 2016 at 07:02:24
 OK, the -1 day is associated with the NETWORKDAYS() function, I've had this problem before. I'll work on it and get back to you. Sorry.MIKEhttp://www.skeptic.com/

Report •

#9
July 14, 2016 at 16:19:14
 Try this one:=IF(AND(INT(A2)=INT(B2),ISNA(MATCH(INT(A2),HolidayList,0))),"0 Dayss"&ROUND(24*(B2-A2),2)&" hours", IF(MOD(A2,1)>DayEnd,NETWORKDAYS(A2,B2,HolidayList)-1&" Days "&ROUND(24*((MOD(B2,1)-DayStart)),2)&" hours", NETWORKDAYS(A2,B2,HolidayList)&" Days "&ROUND(24*((MOD(B2,1)-DayStart)),2)&" hours"))MIKEhttp://www.skeptic.com/

Report •

#10
July 14, 2016 at 16:54:54
 The output on this one might be easier to understand:=IF(AND(INT(A2)=INT(B2),ISNA(MATCH(INT(A2),HolidayList,0))),"0 Days "&TEXT((B2-A2),"HH:MM")&" (Hours & Min)",IF(MOD(A2,1)>DayEnd,NETWORKDAYS(A2,B2,HolidayList)-1&" Days "&TEXT(((MOD(B2,1)-DayStart)),"HH:MM")&" (Hours & Min)",NETWORKDAYS(A2,B2,HolidayList)&" Days "&TEXT(((MOD(B2,1)-DayStart)),"HH:MM")&" (Hours & Min)"))MIKEmessage edited by mmcconaghy

Report •

#11
July 18, 2016 at 07:20:02
 Thank you Mike. These formula don't seem to be taking weekends / holidays into account anymore... The second line in this data is correct however the rest return false data;Start date End date Formula #10 Formula #911/07/2016 08:30 12/07/2016 09:30 2Days01:00(Hours&Min) 2 Days 1 hours13/07/2016 10:00 13/07/2016 10:30 0Days00:30(Hours&Min) 0 Days0.5 hours07/07/2016 14:30 11/07/2016 09:30 3Days01:00(Hours&Min) 3 Days 1 hours28/05/2016 15:00 31/05/2016 08:31 1Days00:01(Hours&Min) 1 Days 0.02 hoursyour help as always very much appreciated.Darrenmessage edited by Langer

Report •

#12
July 18, 2016 at 07:42:55
 OK, I see the problem, back to the drawing board and I thought I checked it.MIKEmessage edited by mmcconaghy

Report •

#13
July 18, 2016 at 07:49:40
 Hopefully this will fix all the problems:=IF(AND(INT(A2)=INT(B2),ISNA(MATCH(INT(A2),HolidayList,0))),"0 Days "&TEXT((B2-A2),"HH:MM")&" (Hours & Min)",IF(MOD(A2,1)>DayEnd,NETWORKDAYS(A2,B2,HolidayList)-1&" Days "&TEXT(((MOD(B2,1)-DayStart)),"HH:MM")&" (Hours & Min)",NETWORKDAYS(A2,B2,HolidayList)&" Days "&TEXT(((MOD(B2,1)-DayStart)+(DayEnd-MOD(A2,1))),"HH:MM")&" (Hours & Min)"))MIKEhttp://www.skeptic.com/

Report •