# I need a formula for symbols based on IF function

Options

Hi,

I need 2 formulas for "due date calculator" column and "due date status" column

1. In the due date calculator column, i used this formula, which works good, but i need one change in it, if done column is checked then i need to return someother value in the due date calculator like "complete".

=IF([Due Date]@row <= TODAY(), NETWORKDAYS([Due Date]@row, TODAY(), {Pivot Sheet Range 1}) - 1, NETWORKDAYS([Due Date]@row, TODAY(), {Pivot Sheet Range 1}) + 1)

2. second formula is for due date status column, for which the reference can be taken from due date calculator column or due date and done columns, no issues on that.

If due date is 7/15, then I need the status as "Yellow" until it meets the due date, like it should turn to yellow 3 days before due date until the due date completes.

After due date- "Red"

But, if "Done" column is checked, then turns to"green"

I know its confusing and tricky, but anything helps, thanks.

• ✭✭✭✭✭
Options

First formula becomes:

=If(Done@row=1,"Complete",IF([Due Date]@row <= TODAY(), NETWORKDAYS([Due Date]@row, TODAY(), {Pivot Sheet Range 1}) - 1, NETWORKDAYS([Due Date]@row, TODAY(), {Pivot Sheet Range 1}) + 1))

Second formula could be:

=If(Done@row=1,"Green",if([Due date calculator]@row<=0,"Red",if([Due date calculator]@row<=3,"Yellow","Green")))

• ✭✭✭✭✭
Options

First formula becomes:

=If(Done@row=1,"Complete",IF([Due Date]@row <= TODAY(), NETWORKDAYS([Due Date]@row, TODAY(), {Pivot Sheet Range 1}) - 1, NETWORKDAYS([Due Date]@row, TODAY(), {Pivot Sheet Range 1}) + 1))

Second formula could be:

=If(Done@row=1,"Green",if([Due date calculator]@row<=0,"Red",if([Due date calculator]@row<=3,"Yellow","Green")))

• Options

Excellent! Thank you so much!

@Philip Robbins if i convert it into column formula, for blank rows, i shouldn't return "invalid" error. Anything to help with it?

• ✭✭✭✭✭
Options

Just wrap the entire formula in:

IFERROR( «FORMULA» ,"")

• Options

For some reason , it makes all the rows with formula with formula as blank. I just want the ones with no reference values to be blank, like below.

• ✭✭✭✭✭
Options

Can you paste your formula into a comment?

• Options

below formula works,

=IFERROR(IF(Done@row = 1, "Complete", IF(TODAY() <= [Due Date]@row, NETWORKDAYS(TODAY(), [Due Date]@row, {Pivot Sheet Range 1}) - 1, NETWORKDAYS(TODAY(), [Due Date]@row, {Pivot Sheet Range 1}) + 1)), "")

this is another one, where now the error has been removed, but it returns "red", even if the reference value is blank.

=IFERROR(IF(Done@row = 1, "Green", IF([Due date calculator]@row <= -1, "Red", IF([Due date calculator]@row <= 3, "Yellow", "Blue"))), "")

• ✭✭✭✭✭
Options

Hmmm… I can't see anything obviously wrong with that second formula. You could add an additional IF statement into it to handle blank values just to be sure, but I'm surprised that blanks are returning "Red" based on what I'm seeing.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!