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 non-working 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 non-date 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 non-working 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 non-working 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 non-date 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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 440 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!