Workday Calculcations

fennerb ✭✭✭✭
edited 02/11/20 in Formulas and Functions

I've not used Smartsheet formulas for some time, and I've spent the last hour trying to tackle this formula on a deadline. I'm very rusty.

I'm literally just trying to calculate the amount of workdays between two columns, but on the same Smartsheet. It's between "Date Served" and "DOI of Litigation." Neither of them are date columns, though. So, I think that's the issue. All of these dates are for years prior, so I don't know how to calculate that.

I have this: =IF([Date Served]1) = "", "", IF([DOI of Litigation]1 = "", NETWORKDAYS([Date Served]1, TODAY()), NETWORKDAYS([Date Served]1, [DOI of Litigation]1)

Additional problem: some columns don't have dates, so that also has to be taken into account. I've tried a few different IFERROR statements.

Best Answer


  • fennerb
    fennerb ✭✭✭✭

    I actually was just able to figure it out! Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent! If you post your solution and then flag your post as "helpful", it will let others searching for a similar solution know that one may possibly be found here!

  • Gavin Burne
    Gavin Burne ✭✭✭

    Hi all,

    I am trying to set a target date (SLA) 2 working days from the "Allocated Date" column. If the column I am working from starts on the weekend it doesn't work? Any ideas because it should not be counting weekends.


    If the allocated data is Fri the 24th April my result is the 28th April - Correct.

    If the allocated data is Sat the 25th April my result is the 28th April - Incorrect.

    If the allocated data is Sun the 26th April my result is the 28th April - Incorrect.

    If the allocated data is Mon the 27th April my result is the 29th April - Correct


    =WORKDAY($[Allocated Date]1, 2)

    Note: I will also add holidays but need the basic formula working first.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Gavin Burne I don't see how the dates generated for Sat and Sun are incorrect. You want to add two working days. Monday. Tuesday. Two working days.

  • Gavin Burne
    Gavin Burne ✭✭✭

    Hi Paul,

    Thanks for the response. What I am struggling with is that if data is added to Sat and Sun the formula adds 2 working days including Monday. However the weekend doesn't count for what I am trying to do, so they should be treated as if logged on Monday + 2 days resulting in the Wed date. (29th in this example).

    In this case we have data logged Sat and Sun with an SLA set to Tue which is not correct for me. I can see why that makes sense, however for tracking an SLA, if you log a call on the weekend the clocks don't start until the next working day, in the formula it doesn't start on Monday it includes Monday as one of the 2 days.

    I hope that makes sense.

    In summary, the results I am looking for are:

    If logged Sat, Sun and Mon it add 2 days from Monday resulting in Wed as the target date (2 working days added)

    Any input you have would be great.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So we just want to add 1 day if $[Allocated Date]1 is Saturday or Sunday.

    IF(OR(WEEKDAY($[Allocated Date]1) = 7, WEEKDAY($[Allocated Date]1) = 1, 1)

    =WORKDAY($[Allocated Date]1, 2) + IF(OR(WEEKDAY($[Allocated Date]1) = 7, WEEKDAY($[Allocated Date]1) = 1, 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!