Quick look at a function

Christopher Warren

I'm trying to have the Status column drop down pick "Processing" if date is after today's date & trying to have the Status column drop down pick "LATE" if date is before today's date.

But if the date is blank, what would need to be added to the function to have the drop down not choose anything?

All the help is greatly appreicated

Mike TV

@Christopher Warren

You need to add the IF blank first, like so:

=IF(ISBLANK([Estimated Completion Date]@row), "", IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row >= TODAY(), "Processing", "")))

I also changed your "Processing" slightly because without the "or equal to today" part it was going to want to leave an Estimated Completion Date of today blank as well. If you want a date of today marked as "LATE" instead of "Processing" then put the = in the Late formula instead and remove it from the Processing one.

Also, this formula wipes out your ability to have anything set to Not Started. Maybe you should have it choose Not Started if the Estimated Completion Date is blank? If so, in the empty quotes near the start of the formula put Not Started in there instead of ""

Jeff Reisman

@Christopher Warren You only need that ISDATE at the very beginning:

=IF(ISDATE([Estimated Completion Date]@row), IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row > TODAY(), "Processing")), "")

If it's a date, evaluate if it's after or before today and set the status value, if it's not a date, leave the status value blank.

Mike TV

@Christopher Warren

You need to add the IF blank first, like so:

=IF(ISBLANK([Estimated Completion Date]@row), "", IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row >= TODAY(), "Processing", "")))

I also changed your "Processing" slightly because without the "or equal to today" part it was going to want to leave an Estimated Completion Date of today blank as well. If you want a date of today marked as "LATE" instead of "Processing" then put the = in the Late formula instead and remove it from the Processing one.

Also, this formula wipes out your ability to have anything set to Not Started. Maybe you should have it choose Not Started if the Estimated Completion Date is blank? If so, in the empty quotes near the start of the formula put Not Started in there instead of ""

Jeff Reisman

@Christopher Warren You only need that ISDATE at the very beginning:

=IF(ISDATE([Estimated Completion Date]@row), IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row > TODAY(), "Processing")), "")

If it's a date, evaluate if it's after or before today and set the status value, if it's not a date, leave the status value blank.