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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something along the lines of...

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!