calculate a 24 hour cycle time which excludes weekends and public holidays

**rammergu** · 03-16-2009, 09:20 PM

Hi,

I would like your input in correcting my formula.

I would like to find out if a job took more than 24 hour cycle time (eg. 6:00 am to 5:59:59 AM next day).

data :

A1 = received date & time (format "m/d/yyyy h:mm")
B1 = completed date & time (format "m/d/yyyy h:mm")

my formula is '=if(B1-A1>"24:00"+0,"Yes","No")

The problem with the formula is that it doesnt exclude weekends nor public holidays.

I couldnt formulate a solution using networkdays function.

appreciate your help.
Kind regards,
Ram

Solution :

for 24 hours Monday to Friday

=if(NETWORKDAYS(C2,W2)-1+IF(NETWORKDAYS(W2,W2),MOD(W2,1),1)-IF(NETWORKDAYS(C2,C2),MOD(C2,1),0)>"24:00"+0,"Yes","No")

Thankyou Daddylonglegs !!

**Bob Phillips** · 03-17-2009, 07:04 AM

Why not post the solution to further enhance the archives?

**rammergu** · 03-17-2009, 06:07 PM

Hi Bob,

i included the solution in my first post. here it is again.

for 24 hours Monday to Friday

IF(NETWORKDAYS(A2,B2)-1+IF(NETWORKDAYS(B2,B2),MOD(B2,1),1)-IF(NETWORKDAYS(A2,A2),MOD(A2,1),0)>"24:00"+0,"NO","YES")

Regards,
Ram