Date / Time calculations ... with a twist..

[slinky]

Hi folks,

Hoping someone might have an idea on this one..

I'm attempting to calculate the time (days/hours/minutes) between two date/time entries.. That's very simple datetime2 - datetime1 = answer..

However, I need to stop the clock outside of working hours (i.e between 1730 and 0900) and not count weekends!

Yes, I could use the in built system calculators (within the system that the data is coming from) but unfortunately due to multiple levels of user error (which will take some time to educate out) our fix time and response time data calcs aren't exactly accurate..

Thanks in advance..

 

[slinky]

Google + MrExcel + a bit of time = Hurrah!

=IF(NETWORKDAYS(A3,B3)=1,(MIN($B$1,MOD(B3,1))-MAX($A$1,MOD(A3,1))),(NETWORKDAYS(A3,B3)-2)*(510/1440)+($B$1-MAX(MOD(A3,1),$A$1))+MIN(MOD(B3,1),$B$1)-$A$1)

Handles an 8.5 hour working day with Start and Stop times in A1 & B1 respectively..

 

[barry houdini]

Hello slinky,

when can start and end time be? If they are always within your working hours then this formula should be sufficient

=(NETWORKDAYS(A3,B3)-1)*(B$1-A$1)+MOD(B3,1)-MOD(A3,1)

 

[slinky]

Hello slinky,

when can start and end time be? If they are always within your working hours then this formula should be sufficient

=(NETWORKDAYS(A3,B3)-1)*(B$1-A$1)+MOD(B3,1)-MOD(A3,1)

Thanks Barry, that certainly looks a lot more simple than the formula I ended up with..

I'm discovering that some jobs are being completed / started outside of working hours (amazingly!).. I've added some handling to allow longer "opening" hours for priority 1 jobs (I'm handling output from a helpdesk system), but for jobs that are not priority 1 the clock should not start ticking until office hours begin.. At the other end of the day, if a job is completed after the end of the day, but prior to the start of the next day, I seem to run into an issue..

Any thoughts on those teasers?

(btw, have moved A1 to B1 and B1 to C1 and added the early start time in A1, then used an if command around the whole formula to decide which start time to use, based on the priority)

 

[barry houdini]

This formula will only count the hours between B1 and C1 even if the start and end times are outside those

=(NETWORKDAYS(A3,B3)-1)*(C$1-B$1)+IF(NETWORKDAYS(B3,B3),MEDIAN(MOD(B3,1),B$1,C$1),C$1)-NETWORKDAYS(A3,A3)*MEDIAN(MOD(A3,1),B$1,C$1)

If you have Priority in C3 then you could modify that to use A1 instead of B1 when Priority is 1, i.e.

=(NETWORKDAYS(A3,B3)-1)*(C$1-IF(C3=1,A$1,B$1))+IF(NETWORKDAYS(B3,B3),MEDIAN(MOD(B3,1),IF(C3=1,A$1,B$1),C$1),C$1)-NETWORKDAYS(A3,A3)*MEDIAN(MOD(A3,1),IF(C3=1,A$1,B$1),C$1)

 

[slinky]

Lovely work sir! Thanks very much for that..

I shall give it a shot in the morning.. It's been one of those days..

Thanks again, I'll report back..

 

[slinky]

Morning..

With a little tweak to add it into the table I'm using, it works perfectly.. Here's the amended code if you're interested..

=(NETWORKDAYS([@[Received Date/Time]],[@[Completion Date/Time]])-1)*(C$1-IF([@Priority]=1,A$1,B$1))+IF(NETWORKDAYS([@[Completion Date/Time]],[@[Completion Date/Time]]),MEDIAN(MOD([@[Completion Date/Time]],1),IF([@Priority]=1,A$1,B$1),C$1),C$1)-NETWORKDAYS([@[Received Date/Time]],[@[Received Date/Time]])*MEDIAN(MOD([@[Received Date/Time]],1),IF([@Priority]=1,A$1,B$1),C$1)

Thanks very much for your help Barry... All I need to do now is resolve the import process (some data cleansing to do) and some small anomalies in the query that provides the data and I'll be there..

Cheers again..