I need a formula for symbols based on IF function

Joyjenz

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.

Philip Robbins

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")))

Philip Robbins

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")))

Joyjenz

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?

Philip Robbins

Just wrap the entire formula in:

IFERROR( «FORMULA» ,"")

Joyjenz

Hi @Philip Robbins ,

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.

Philip Robbins

Can you paste your formula into a comment?

Joyjenz

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"))), "")

Philip Robbins

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.