If, And, Star Help
Hi SS Community!
I wrote a formula that is not intending as I wanted it to.
Looking for some help.
Here is my formula:
=IF(AND(ISBLANK([Entered in WD]@row), TODAY() > [Assigned To OSU]@row + 4), 1, 0
Here are my sheet columns:
I want to STAR if a PEP was assigned to the OSU but not entered in WD after five days of it being assigned.
Where did I go wrong? Right now it is throwing the star on all blanks...
Best Answers

Use NETDAYS(), NETWORKDAY(), or NETWORKDAYS() to find the number of days between two dates.
One way to evaluate for 5 days passed would be,
NETDAYS(
[Assigned To OSU]@row , TODAY() )>5Your formula would be,
=IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)
To exclude nonworking days, use NETWORKDAY() or NETWORKDAYS().
You'll find documentation for these and other Smartsheet functions here: https://help.smartsheet.com/functions
Hope this helps.
Cheers!

Hi, Tabitha.
The formula,
=IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)
assumes that [Assigned To OSU] will never be empty and that it will always be a date. Otherwise, NETDAYS() will return an error because it'll be unable perform the calculation.
If there is a chance that [Assigned To OSU] might be empty or contain a nondate value, then we need to include IFERROR() to handle those exceptions. Assuming that you want to return "not true" when [Assigned to OSU] is empty, then the IFERROR() statement will be...
IFERROR(NETDAYS([Assigned To OSU]@row,TODAY())>5,FALSE)
and the formula, with exception handling, will be...
=IF(AND(ISBLANK([Entered in WD]@row), IFERROR(NETDAYS([Assigned To OSU]@row, TODAY()) > 5, FALSE)), 1, 0)
Answers

Use NETDAYS(), NETWORKDAY(), or NETWORKDAYS() to find the number of days between two dates.
Since you're evaluating for 5 or more days passed, the criterion would be
NETDAYS(
[Assigned To OSU]@row, TODAY() )>5Your formula would be
= IF(AND(ISBLANK([Entered in WD]@row), NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)
Use NETWORKDAY() or NETWORKDAYS() if you want to exclude nonworking days.
You'll find additional references for Smartsheet functions here: https://help.smartsheet.com/functions

Use NETDAYS(), NETWORKDAY(), or NETWORKDAYS() to find the number of days between two dates.
One way to evaluate for 5 days passed would be,
NETDAYS(
[Assigned To OSU]@row , TODAY() )>5Your formula would be,
=IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)
To exclude nonworking days, use NETWORKDAY() or NETWORKDAYS().
You'll find documentation for these and other Smartsheet functions here: https://help.smartsheet.com/functions
Hope this helps.
Cheers!

Oh, I see thank you so very much!

I just tried the formula and it returns #invalid:

Hi, Tabitha.
The formula,
=IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)
assumes that [Assigned To OSU] will never be empty and that it will always be a date. Otherwise, NETDAYS() will return an error because it'll be unable perform the calculation.
If there is a chance that [Assigned To OSU] might be empty or contain a nondate value, then we need to include IFERROR() to handle those exceptions. Assuming that you want to return "not true" when [Assigned to OSU] is empty, then the IFERROR() statement will be...
IFERROR(NETDAYS([Assigned To OSU]@row,TODAY())>5,FALSE)
and the formula, with exception handling, will be...
=IF(AND(ISBLANK([Entered in WD]@row), IFERROR(NETDAYS([Assigned To OSU]@row, TODAY()) > 5, FALSE)), 1, 0)

I often forget the "not true" aspect of the formulas.
This makes total sense and I appreciate the thorough explanation!
Help Article Resources
Categories
Check out the Formula Handbook template!