Help revising IF statement for Business days (NETWORK DAYS) rather than Calendar Days

SherryFox · 01/27/25

Hi all! I have a functional formula (woohoo!). However I have found out I need to transition the formula from calendar days to business days. The screenshot is below the formula. I have hidden the non-relevant columns. Any help is appreciated.

=IF(Parent@row = "TRUE", "", IF(AND([Certification Complete]@row <> "Green", NOT(ISBLANK([Workflow Initiated]@row)), [Workflow Initiated]@row <= TODAY(-14)), TODAY(), ""))

SherryFox · 01/29/25

@Paul Newcome ,

I decided to use the following:

=IF(Parent@row <> 1, IF([Workflow Initiated]@row <> "", WORKDAY([Workflow Initiated]@row, 14)))

This formula gives me a deadline date on all records regardless of th status.

Kelly Moore · 01/27/25

Hey @SherryFox

Try this:

=IF(Parent@row = "TRUE", "", IF(AND([Certification Complete]@row <> "Green", NOT(ISBLANK([Workflow Initiated]@row)), [Workflow Initiated]@row <= WORKDAY(TODAY(),-14)), TODAY(), ""))

Does this work for you? You may need to change the -14 days to -10 days, depending on how your business counts days.

Kelly

If interested, you can find more information and related functions at the link below.

WORKDAY Function | Smartsheet Learning Center

SherryFox · 01/27/25

That gives me the same results. I think the "TODAY" is part of the problem. If you see the date in the column displayed, it is 11/6/24. Now the formulas is displayed in the next column. With my previous column AND your formula, both result in today's date. However 11/6/24 + 14 Network days is way before today. That is the date I am trying to capture/ And still excluding the Green Status like my formula lists. Does that clarify? The date that is currently listed in my formula is today's date. This is the formula we are working on.

Paul Newcome · 01/28/25

Can you just outline what it is you want your formula to do? The way I am reading what you currently have is…

If it is not a parent row, if [Certification Complete] is not "Green", and [Workflow Initiated] is not blank, and [Workflow Initiated] is more then 14 days in the past, output today's date. Otherwise leave blank.

But based on your latest comment, it sounds like at some point you want to output a date different than today's date.

SherryFox · 01/28/25

@Paul Newcome .

Okay here is my criteria

If it is is a Parent row, then the result is a blank cell. There is a checkbox on the far left for Parent.
If the Certification Complete column is Green then the result is a blank cell.
If However the Certification Complete column is NOT green (meaning is yellow or red)
For Red and Yellow list the deadline date of 14 network days. The value in this cell should disappear when the color changes to Green for Certification Complete.

We have additional deadlines that follow the same rules, 3 days, 5 days, 8 days, 10 days. I am hoping to just copy this revised formula, and change the appropriate number.

And I am just thinking about it, can you provide 2 formulas, one as I have outlined, and the second that will NOT change when the certificate complete has gone to Green. I think I may just want to handle that with conditional formatting instead.

=IF(Parent@row = "TRUE", "", IF(AND([Certification Complete]@row <> "Green", NOT(ISBLANK([Workflow Initiated]@row)), [Workflow Initiated]@row <= TODAY(-14)), TODAY(), ""))

Paul Newcome · 01/28/25

Try this:

=IF(Parent@row <> 1, IF([Certification Complete]@row <> "Green", IF([Workflow Initiated]@row <> "", WORKDAY([Workflow Initiated]@row, 14))))

There is no formula that is going to lock in a date when the Certification Complete] changes to "Green", but you can set up a Record a Date automation to do that.