Count working days.

**bangor_boy** · 06-06-2012, 04:25 PM

Spreadsheet is as follows.

Cell A1 contains a date entered Manually say 01/06/2012.
Cell A2 has a formula in it which counts the difference between todays date and the value in cell A1, using today(). The idea is that if something goes over 10 working days Cell A2 will change to "over 10 days", if it goes over 15 days the value in the cell will change to "over 15 days" and if it is under 10 days it will say "under 10 days". It has been set up that it counts everyday as a working day so for it to go over 10 days the formula will count to the 15th day.
I understand there is Workingday() helper which counts monday - friday and holidays as well (i have only been with the company a few weeks and noticed this all today when i came back from the long weekend). I had a play about with workingday() but i dont know if i am writing it correctly and i dont understand what to write in the number of days part, i want it to run unlimited but not sure if this is possible.

I am at home at the moment so i cant describe it any better than that. Please help and tell me if i am going in the completely wrong direction.

**daddylonglegs** · 06-06-2012, 04:31 PM

You can get the working days elapsed with NETWORKDAYS function, i.e.

=NETWORKDAYS(A2,TODAY(),H$2:H$10)

assuming holidays are listed in the range H2:H10

You can put that into a LOOKUP function to return the text you need, i.e.

=IF(A2="","",LOOKUP(NETWORKDAYS(A2,TODAY(),H$2:H$10),{0,10,15;"under 10 days","over 10 days","over 15 days"}))

Assumes A2 in the past, note that NETWORKDAYS will include both A2 and TODAY in the count (e.g. Yesterday to today will count as 2), so you may need to adjust to accommodate that

**bangor_boy** · 06-06-2012, 04:49 PM

Sounds good to me.
Thanks!

**bangor_boy** · 06-06-2012, 04:52 PM

Sorry how would i set it to check how many days it is on like a sense check.

**daddylonglegs** · 06-06-2012, 05:25 PM

I'm not sure what you mean. The basic NETWORKDAYS formula will give you a count of elapsed working days, i.e.

=NETWORKDAYS(A2,TODAY())

**Xx7** · 06-06-2012, 05:55 PM

=IF((NETWORKDAYS(A1,TODAY(),H$2:H$10))<10,"under 10 days",IF((NETWORKDAYS(A1,TODAY(),H$2:H$10))>10,IF((NETWORKDAYS(A1,TODAY(),H$2:H$10))>15,"over 15 days","over 10 days")))