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

Options

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.

• ✭✭✭✭✭✭
Options

Try something along the lines of...

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

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

Give this a try...

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

• Options

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?

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!