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!!

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

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

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

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

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 OKOn 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 OKOn 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 HoursIn 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

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!

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 -1Days9HoursThe 1st one should return 0Days1Hours

The 2nd one should return 0Days0.5HoursThanks

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

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

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

message edited by mmcconaghy

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

OK, I see the problem, back to the drawing board and I thought I checked it. MIKE

message edited by mmcconaghy

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

Ask Your Question

Weekly Poll

Do you think Amazon has bigger plans for Whole Foods?

Discuss in The Lounge

Poll History