Workday Calculcations

fennerb

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.

Paul Newcome

You would need to use date type columns or use a formula to convert the data into a DATE statement for use in your formula.

Are you able to provide a screenshot of what you are working with and have sensitive/confidential data removed, blocked, or replaced with "dummy data"?

Paul Newcome

You would need to use date type columns or use a formula to convert the data into a DATE statement for use in your formula.

Are you able to provide a screenshot of what you are working with and have sensitive/confidential data removed, blocked, or replaced with "dummy data"?

fennerb

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

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

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.

e.g.

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

Formula

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

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

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

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.

Thanks

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)