Difference between two dates/times, excluding weekends...

[dean_woods]

I would like to work out the difference (in hh:mm:ss) between two periods of time (Start time and End time) (dd/mm/yyyy hh:mm:ss), the formula needs to exclude weekends. Also...

Here's the bit where I'm not too sure how to factor into the formula (thats if it is at all possible!):

My team provide support between 08:00 & 18:00. If a jobs Start time is after 18:00 the Service Level Agreement for my team does not start until 08:00 the following morning. Does that make sense

Any help/ideas would be appreciated. Thanks

 

[inarbeth]

Hi welcome to the board.

Chris Pearson has a comprehensive section on working with dates and times: http://www.cpearson.com/excel/datetime.htm#WorkHours

and http://www.cpearson.com/excel/datearith.htm

as does Dave McRitchie: http://www.mvps.org/dmcritchie/excel/datetime.htm

 

[Dutchy]

Do you only want to calculate the WORK hours between the two time/date values?

e.g. 14:30 on Friday to 15:30 tomorrow will be 21 hours

If so

=(NETWORKDAYS(A1,B1)-1)*5/12+MOD(B1,1)-MOD(A1,1)

format as [h]:mm:ss

where A1 is start time/date and B1 the end time/date

 

[dean_woods]

Thanks for the replies.
Yep, would like to calculate the difference between Start and End times (but only between 08:00 - 18:00)

 

[Dutchy]

I looked at this again and realised that the formula I suggested wouldn't give the correct results if the start or end dates/times were at the weekend or on holidays.

Here's a better version, which seems to work OK for all the circumstances I tried.

It only calculates the work hours between the dates/times given, also excludes holidays - you list those and the formula references the range

It sounds a pretty simple proposition but it's a tricky little critter. My formula's now pretty long but still not nearly as long as Pearson's posted above.

Please let me know if this is any good for you - I'm sure I can adjust if necessary, if you don't need the holidays included, that will shorten the formula somewhat

overtime.xls
	A	B	C	D	E	F	G
1
2
3	Start	End	Elapsed work hours		hours	holidays
4	29/04/2005 18:30	03/05/2005 07:30	0:00:00		08:00	27/12/2004
5	22/04/2005 17:30	26/04/2005 03:30	10:30:00		18:00	28/12/2004
6	26/04/2005 22:00	28/04/2005 04:00	10:00:00			02/01/2005
7	27/04/2005 22:00	04/05/2005 22:00	40:00:00			25/03/2005
8	01/04/2005 17:20	28/04/2005 08:10	180:50:00			28/03/2005
9	01/04/2004 17:20	28/04/2005 22:10	2760:40:00			02/05/2005
10						30/05/2005
11						29/08/2005
12
Time difference

Formula in C4 copied down column

=IF(OR(A4="",B4=""),"",(NETWORKDAYS(A4,B4,$F$4:$F$12)-1)*($E$5-$E$4)+IF(OR(ISNUMBER(MATCH(INT(B4),$F$4:$F$12,0)),WEEKDAY(B4,2)>5),$E$5,MEDIAN(MOD(B4,1),$E$5,$E$4))-IF(OR(ISNUMBER(MATCH(INT(A4),$F$4:$F$12,0)),WEEKDAY(A4,2)>5),$E$4,MEDIAN(MOD(A4,1),$E$5,$E$4)))

 

[dean_woods]

Dutchy, thanks! Its all looking good so far... I suppose to be 100% I should factor in the holidays...

 

[Dutchy]

Yes,

Given the formula I suggested these can be listed in F4:F12 - obviously you can change the range or extend it