If, And, Star Help

Tabitha W.

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...

Toufong Vang

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() )>5

Your 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!

Toufong Vang

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)

Toufong Vang

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() )>5

Your 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

Toufong Vang

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() )>5

Your 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!

Tabitha W.

Oh, I see- thank you so very much!

Tabitha W.

I just tried the formula and it returns #invalid:

Toufong Vang

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)

Tabitha W.

I often forget the "not true" aspect of the formulas.

This makes total sense and I appreciate the thorough explanation!