Using NETWORKDAYS function to calculate cycle time

**tciocchetti** · 06-27-2012, 05:42 PM

Hello!

I'm trying to figure out how to calculate a 1 day cycle time using the NETWORKDAYS function. Currently, I need to calculate the business days between two dates 'actual' and 'submitted' and if the date is 1 day or less it is 'on-time' over 1 day and it is late. The function works in most cases except for when the 'submitted date' falls on the weekend. Since NETWORDAYS doesn't count the weekends it shows that the cycle inaccurately for my needs. Here is an illustration and the current formula I am using.

Actual Date Submitted Date Cycle Time Late/On Time
6/21/2012 6/24/2012 1 on time

Where C2 is the actual date, D2 is the submitted date. Since the 21st is a Thursday, for it to be in cycle it needs to be entered by Friday the 22nd or it's late; however with the formula I am using it doesn't count the weekend so despite it being submitted on Sunday it shows it 'in cycle and on time' when it should actually be late. Is there something I can add to my current formula or a different formula I can use to count a 1 day cycle time excluding weekends? The formula I'm using is below:

=IF(ISBLANK(C2),"",NETWORKDAYS(C2,D2)-1)

Thanks!

**daddylonglegs** · 06-27-2012, 05:57 PM

You can use WORKDAY to get the target day and then designate "on time" or not with an IF function, e.g. use

=IF(C2="","",IF(D2>WORKDAY(C2,1),"late","on time"))