Calculating working days AND hours

[Pearl_Jam]

Hi everyone,

I'm looking for a formula to calculate working days AND hours between a start and end date/time of a given task - IE how long did it take, excluding weekends.

For example, something starting this Friday and completed on the following Monday:

A1 = 08/02/2013 14:01
B1 = 11/02/2013 15:01

So the result for this should be 01:01:01 (using dd:hh:mm as the cell format in C1), with a working day starting @ 08:00 and finishing @ 17:00

How do I calculate this? I can see the NETWORKDAY function, but I need to calc the hours too and to be able to sum up the total days/hours across x amount of tasks.

Thanks

[dilipandey]

So the result for this should be 01:01:01 (using dd:hh:mm as the cell format in C1), with a working day starting @ 08:00 and finishing @ 17:00

what about days as you said you need days and and hours ?

Regards,
DILIPandey
<click on below * if this helps>

[Pearl_Jam]

Yes, including the days too. Thanks!

[daddylonglegs]

If the start and end times/dates will always be within working hours then try this formula

=NETWORKDAYS(A1,B1)-1-(MOD(A1,1)>MOD(B1,1))+MOD(MOD(B1,1)-MOD(A1,1),"9:00")

Note that dd:hh:mm format won't show days above 31

[Pearl_Jam]

Brilliant DLL!

Can you explain what the "9:00" bit of that formula means?

Many thanks

[daddylonglegs]

9:00 is the length of your working day - it's used in the MOD function here to give the correct "remainder" after you have taken the whole days with NETWORKDAYS

[Pearl_Jam]

Thanks again

[Pearl_Jam]

Note that dd:hh:mm format won't show days above 31

Ahhh, this is causing a problem when I'm summing up the total across all tasks. When I highlight this column and drag down, I can see the count of days/hours/minutes going up and when it hits 31, it resets back to zero.

EG, 29 days, 0 hours and 8 minutes plus the next completed task time (6 days, 5 hours and 9 minutes) totals 8 days, 7 hours and 57 minutes!

Is there a way around this?

Thanks

[daddylonglegs]

I can see another problem with summing those results - the "days" actually represent a working day of 9 hours so if you add 1 day 6 hours and 2 days 7 hours the result should really be 4 days 4 hours (but summing those in excel will understandably give you 3 days 13 hours)

Perhaps it's simpler to work in working hours, i.e. using this formula

=(NETWORKDAYS(A1,B1)-1)*("17:00"-"8:00")+MOD(B1,1)-MOD(A1,1)

format result cell as [h]:mm

That would give 10:00 for your example and the results can be summed with no problem

If you want days ultimately then you can convert the total from hours to working days by dividing by "9:00"

[Pearl_Jam]

Hmmm, still not working properly. For example, I get the following result with these dates/times:

A1 07/01/2013 10:38
B1 08/01/2013 11:01
Result = 15:23

But it should be 9hrs 23 mins!

And for a start/end that spans a weekend:
A2 10/01/2013 16:27
B2 15/01/2013 16:58
Result = 15:31

But this should be 27hrs, 31mins

Any ideas?

[daddylonglegs]

I get 9:23 and 27:31 using the suggested formula - are you using exactly the formula I suggested?