Formula to deduct calender days from a date, holding weekends in account

Nicolasdb

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.

Paul Newcome

Try something along the lines of...

=WORKDAY([Date Column]@row, -10)

Nicolasdb

Already tried that,

the result is then :

=WORKDAY([SAILING DATE]1; -STORAGE1)= 26/05

(sailing date being 9th of June & storage is-10)

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

Paul Newcome

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.

Nicolasdb

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..

Paul Newcome

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?

Nicolasdb

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

Paul Newcome

Give this a try...

=[Sailing Dates]@row - 10 + IF(WEEKDAY([Sailing Dates]@row - 10) = 7, 2, IF(WEEKDAY([Sailing Dates]@row - 10) = 1, 1))

Nicolasdb

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?

Paul Newcome

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))