CODEWITHSUNDEEP
excelexcel-formula
Sergiu
Sergiu

Reputation: 43

how can i exclude FRI and SAT days from the calculation of days passed

I need to exclude FRI and SAT days from the formula which counts the days from date in cell A1 until the date of Today(), and subtracts A1 from Today()

i have already the formula which checks first if A1 has a value, if yes subtracts the value from Today(), if not it will say "NO VALUE"

i also know there is code to exclude FRI and SAT but i'm having a hard time incorporating it into my formula:

This will count 8 days from Start_Date in A1, and excludes FRI and SAT:

=WORKDAY.INTL(A1,8,7)

This is my current formula which shows the number of days, but i want it to skip FRI and SAT:

=IF((A1<>""),((TODAY()-A1)),"NO VALUE")

Upvotes: 0

Views: 121

Answers (2)

Nels
Nels

Reputation: 11

It might not be the best way to do it but I found a work around.

You can calculate the total number of day and remove the number of Friday and remove the number of Saturday.

I found this that count how many Friday in a specific date range.

For example:

 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(Date1&":"&Date2)))=5))

It basically retrieve all the date in the range in their numerical value and test if if it is a Saturday and give you the amount of Saturday between 2 dates.

In your case you could use it to remove the Friday and Saturday like below:

=IF((A1<>""),((TODAY()-A1-SUMPRODUCT(-(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=5)) -SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=6)))),"NO VALUE")

Where B1 is today's date.

And example:

example using the formula

Hope this helps.

Upvotes: 1

Rory
Rory

Reputation: 34075

You can use NETWORKDAYS rather than WORKDAY:

=IF(A1<>"",NETWORKDAYS.INTL(A1,TODAY(),7),"NO VALUE")

Upvotes: 3

Related Questions