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:00
04/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

Report •


#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.

MIKE

http://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
Sorry there is no way to upload a spreadsheet to the forum,
but you can post a small sample of your spreadsheet, please
use Row Numbers and Column Headings,
but first read this HOW-TO which explains the use of the < PRE > tags to align your data.

http://www.computing.net/howtos/sho...

Also see these threads and see if they help shed some light on the problem:

http://www.computing.net/answers/of...

http://www.computing.net/answers/of...

The formula don't seem to be returning the figures I need though.

What figures are they returning?
Are you getting an error message?

Just as a general warning, make sure your Date & Times are really
Date & Times and NOT Text that LOOKS like a Date & Time.
this is a very common problem, if you have imported the data.

MIKE

http://www.skeptic.com/


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.

MIKE

http://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 AM
In cell AB enter: The Time your work day Ends, IE: 5:00 PM
In 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 Formulas
Select Define Name
In the Name Box enter: DayStart
In the Refers to Box (At the bottom) enter: =Sheet1!$AA$1
Click OK

On the Ribbon, Select Formulas
Select Define Name
In the Name Box enter: DayEnd
In the Refers to Box (At the bottom) enter: =Sheet1!$AB$1
Click OK

On the Ribbon, Select Formulas
Select Define Name
In the Name Box enter: Holidays
In the Refers to Box (At the bottom) enter: =Sheet1!$AC$1:$AC$15
Click OK
Modify 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.

MIKE

http://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.5Hours
13/07/2016 10:00 13/07/2016 10:30 -1Days9Hours

The 1st one should return 0Days1Hours
The 2nd one should return 0Days0.5Hours

Thanks


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.

MIKE

http://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"))

MIKE

http://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)"))

MIKE

http://www.skeptic.com/

message 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 #9
11/07/2016 08:30 12/07/2016 09:30 2Days01:00(Hours&Min) 2 Days 1 hours
13/07/2016 10:00 13/07/2016 10:30 0Days00:30(Hours&Min) 0 Days0.5 hours
07/07/2016 14:30 11/07/2016 09:30 3Days01:00(Hours&Min) 3 Days 1 hours
28/05/2016 15:00 31/05/2016 08:31 1Days00:01(Hours&Min) 1 Days 0.02 hours


your help as always very much appreciated.
Darren

message 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.

MIKE

http://www.skeptic.com/

message 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)"))

MIKE

http://www.skeptic.com/


Report •


Ask Question