Networkdays: start date is a holiday

[sangeetha]

I have a similar problem.
Data: Start date and time in col F, end date and time in col L, lead time (hh:mm:ss) in col M. Lead time calculation formula:=IF(ISBLANK(L2),(NETWORKDAYS(F2,NOW(),Holidays)-1+MOD(NOW(),1)-MOD(F2,1)),(NETWORKDAYS(F2,L2,Holidays)-1+MOD(L2,1)-MOD(F2,1)))
Lead time is calculated as soon as the start date is entered, considering current time (NOW) as end date until actual end date and time are entered.
2nd October is in holiday list.

Issue: For rows with start date as 2nd october, lead time does not give the correct result.
E.g. start date and time = 02-10-2014 13:56:00 and End date and time = 07-10-2014 12:03.
Result should be 60:03:00 but I get 46:07:00

I guess this is because, though 2nd october is considered holiday, start time is calculated from 13:56 and not as 3rd october 00:00

Hope I have clearly laid the case. Any help appreciated.

 

[srinidhi]

Please upload the file

 

[NARAYANK991]

Hi Sangeetha ,

This is bound to be a problem if the start date is itself a holiday ; if only this one day is a holiday , then the solution is straightforward , since you can use an IF statement such as :

=IF(ISBLANK(L2),(NETWORKDAYS(IF(ISNA(MATCH(INT(F2),Holidays,0)),F2,INT(F2)+1),NOW(),Holidays)-1+MOD(NOW(),1)-MOD(IF(ISNA(MATCH(INT(F2),Holidays,0)),F2,INT(F2)+1),1)),(NETWORKDAYS(IF(ISNA(MATCH(INT(F2),Holidays,0)),F2,INT(F2)+1),L2,Holidays)-1+MOD(L2,1)-MOD(IF(ISNA(MATCH(INT(F2),Holidays,0)),F2,INT(F2)+1),1)))

where the highlighted portions replace the F2 in your posted formula.

However , if the start date is the start of a series of dates which are holidays , then the formula will need to be developed differently to find out the first date after the start date that is a non-holiday. Will there be such dates ?

Narayan

 

[sangeetha]

Thanks.　No, I dont think there is any continuous holiday, atleast until this year end. So I will check this formula and let you know the result.

 

[sangeetha]

Thank you so much, this worked.