Removing non business days & hours

**Shakey** · 02-08-2009, 08:58 PM

How do I calculate the number of days, hours & minutes between 2 value's and exclude non business days and hours...? For example, I have 2 time/dates stamps; 1. the time/date an incident was logged (say 01/21/09 09:30). 2. the time/date the incident was resolved (say 01/29/09 19:30). In real time thats 7 days & 10 hours. But in business terms its 6 days 2.5 hours (working Monday - Friday, 9 - 5 (8 hr days)).

I can use 'networkdays' function to calculate working days but it ignores the time completely.

I'm using Excel 2007

HELP!!!??

Cheers, Jason

**daddylonglegs** · 02-08-2009, 09:49 PM

How do you make that 6 days 2.5 hours, isn't it 6 days and 7.5 hours?

Assuming you have start time and date in A2 and end time and date in B2 then this formula will give the result in total hours, e.g. 55:30

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"09:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","09:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","09:00")

format as [h]:mm

If you want to convert that to 6 days and 7.5 hours then, assuming that formula in C2 use this formula in D2 to convert

=INT(C2*3)&" days "&FIXED(MOD(C2*24,8),2)&" hours"

**Shakey** · 02-08-2009, 11:39 PM

Sorry, Mr Longlegs, you're right. 19:30 is outside business hours so it should count up to 5pm. That aside - brilliant!! Its works!

Thanks for your help!!

Cheers, J.

**Dahlia** · 06-18-2012, 07:52 AM

Originally Posted by daddylonglegs

How do you make that 6 days 2.5 hours, isn't it 6 days and 7.5 hours?

Assuming you have start time and date in A2 and end time and date in B2 then this formula will give the result in total hours, e.g. 55:30

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"09:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","09:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","09:00")

format as [h]:mm

If you want to convert that to 6 days and 7.5 hours then, assuming that formula in C2 use this formula in D2 to convert

=INT(C2*3)&" days "&FIXED(MOD(C2*24,8),2)&" hours"

Hi,
Am now also have a similar situation and i tested the above formulas and they do calculate and give results. However,to apply these formula to my data, I seem to be curious about the followings:-

In formula
=(NETWORKDAYS(A2,B2)-1)*("17:00"-"09:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","09:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","09:00")
can you help to clarify what is number "1" refers to, please?

In formula
=INT(C2*3)&" days "&FIXED(MOD(C2*24,8),2)&" hours"
is number "8" refers to how many working hours per day? If it is, I shall put 9 instead because our working hours is from 8.30am-5.30pm
I still cannot figure out where has number "3" and number "2" come from.Please clarify.

Thank you in advance.

**daddylonglegs** · 06-18-2012, 07:59 AM

Please start a new thread, with a link to this one if you believe it is relevant

**samir_ibrahim3** · 06-19-2013, 08:23 AM

Hi All,

I have a similar situation, and i used the above formula and it's already working, but i need to change the off day to be only "Fri" instead of "Sat & Sun"

your help is highly appreciated