PDA

View Full Version : 2 Date Time Difference



paddysheeran
06-03-2011, 04:12 AM
Hi All,
I need to be able to calculate the time difference between two dates. For example:
17/05/2011 12:04 and 01/06/2011 00:00
I only need to calculate the time elapsed between 8am-6pm Monday to Friday between these two dates. I think the forumla may need to use Network Days or the Weekday function but not sure which. Does anyone have a solution?
thanks,
Padraig.

Bob Phillips
06-03-2011, 06:14 AM
Try this

=N((NETWORKDAYS(A2,A3)-(MOD(A2,1)>=--"08:00:00")-(MOD(A3,1)<=--"18:00:00"))*10+MAX(0,--"18:00:00"-MOD(A2,1))*24+(MAX(0,MOD(A3,1)-"08:00:00")*24))

paddysheeran
06-03-2011, 07:26 AM
I've calculated the times using the formula in the attached. All looks ok apart from rows 5 and 20 in column I which are giving negative values. I cant get my head around why this would be. Is it somethign obvious?