Formula to deduct calender days from a date, holding weekends in account
for example:
date is 09/06  need to deduct 10 calenderdays, the date returned may never be saterday or sunday.
the outcome must be 01/06  since 10 calenderday returns Sunday 31/05  which is not allowed.
Answers

Try something along the lines of...
=WORKDAY([Date Column]@row, 10)

Already tried that,
the result is then :
=WORKDAY([SAILING DATE]1; STORAGE1)= 26/05
(sailing date being 9th of June & storage is10)
For some more background, the setup is to have a formula in which a date is shown when we can deliver free of charge on a terminal. problem being is that various carriers have different allowed days (ranges from 2 to 10) since weekend are not operational working days, the outcome can never be in a weekend. so the date shown must be (if in a weekend) revert back to the closest date within the allowed days, in this example the first of June

The problem is that you are subtracting the data in the cell and the data happens to be a negative number (minus a negative is the same as plus). Try using just a cell reference to the negative number which basically adds a negative which is the same as subtracting.

Hi Paul,
so gratefull for your help, but the problem remains the same,
formula applied: =WORKDAY([SAILING DATES]1; STORAGE1)
since free delivery on 29/05 means a total of 15 calenderdays, not 10
It's a brainbreaker this one..

Ok. Let's try a different approach...
You want to subtract 10 calendar days, but if it falls on a weekend, you need a weekday.
If it falls on a Saturday, do you want to move to the previous Friday or the following Monday? Same for if it falls on a Sunday?

Hi Paul,
exactly,
if it falls on a saterday or sunday, it must move to monday
since that falls within the granted 'freetime' of 10 calenderdays

Give this a try...
=[Sailing Dates]@row  10 + IF(WEEKDAY([Sailing Dates]@row  10) = 7, 2, IF(WEEKDAY([Sailing Dates]@row  10) = 1, 1))

Hi Paul,
we're getting closing!
but when you change the sailing date to 12/06, it goes wrong again.
the outcome changes to 02/06 which are 11 calenderdays instead of 10
or if you change the sailing date to 08/06  the outcome changes to 29/05  which are 11 calender days and should go to 01/06
Can i maybe invite you in my sheet?

That seems odd... Try this...
=([Sailing Dates]@row  10) + IF(WEEKDAY([Sailing Dates]@row  10) = 7, 2, IF(WEEKDAY([Sailing Dates]@row  10) = 1, 1))
Help Article Resources
Categories
Check out the Formula Handbook template!