I am trying to build a formula that will look at a cell date and then mark "true" (in helper column)

Meredith Krueger · 02/13/23

I am trying to build a formula that will look at a cell date and then mark "true" (in helper column) if it is less than "TODAY". I need this formula to ignore blank cells in date column.

I have tried this one, with no luck.

=IFERROR(IF(ISBLANK([Smart Status Change Date]@row), "Blank", IF(AND(ISDATE([Smart Status Change Date]@row), TODAY() > [Smart Status Change Date]@row), "Late"))), "")

I get this one to work showing if a cell is blank but need to compare date now.

=IF(ISBLANK([Smart Status Change Date]@row), 1, 0)

IDEALLY, I would like the formula to tell me if my column cell date is less than a cell date in another column but I would settle to using "TODAY" if need be.

Jeff Reisman · 02/13/23

https://community.smartsheet.com/discussion/comment/362022#Comment_362022

OK, so you want it marked "Late" if the [Smart Status Change Date]@row is a date and if that date is after today - as in, the [Smart Status Change Date]@row is in the future?

And if the [Smart Status Change Date]@row is blank (i.e. not a date,) you just want the cell left blank, empty, yes?

Here's the thing: In Smartsheet formulas, simplicity is best, so try for the least complex formulas that gets your results. Sometimes with IFs, you need to switch them around to look for one condition before another, or look for the opposite of the condition you think you need to look for. In this case, you don't need to specify that the [Smart Status Change Date]@row must be blank, you just give it the requirement that it must be a date to even be considered. If it's not a date, the formula skips right to the "value if false."

=IF(AND(ISDATE([Smart Status Change Date]@row), [Smart Status Change Date]@row > TODAY()), "Late", "")

Smartsheet formulas work left to right, so the first thing this one is going to do is check to see if [Smart Status Change Date]@row is a date; if it is a date, then it checks if that date is after today's date, and if so, sets the value to "Late." If it's not a date, or if the date is before today's date, it leaves the cell blank.

I also recommend leaving IFERROR off your formulas until you are positive they are working correctly. That way, if you get a blank result, you know it's a result of the formula and not the IFERROR suppressing an error message that can help you troubleshoot. (See the links in my signature for formula help pages and error messages.)

Meredith Krueger · 02/13/23

That did it! Perfect, thank you! AND, thank you for a thorough explanation. ;)

Jeff Reisman · 02/13/23

@Meredith Krueger Try something like this:

=IFERROR(IF(AND(ISDATE([Smart Status Change Date]@row), [Smart Status Change Date]@row < [Other Date Column]@row), "Late", ""), "")

The logic here is just "if there's a date value in Smart Status Change Date, AND if it's less than this other date value, set the cell to "Late"; if one of these things is untrue, leave it blank."

If you want a different value if the Smart Status Change Date is greater than the other date value, you can just nest that as the false condition:

=IFERROR(IF(AND(ISDATE([Smart Status Change Date]@row), [Smart Status Change Date]@row < [Other Date Column]@row), "Late", IF(AND(ISDATE([Smart Status Change Date]@row), [Smart Status Change Date]@row >= [Other Date Column]@row), "On Time", ""), "")

If you need to find where a date field is blank or not a date, you can always use NOT(ISDATE([Date Column]@row)).

Meredith Krueger · 02/13/23

@Jeff Reisman That worked! But, now I am seeing a different issue and realizing that I just need to mark "late" if after "TODAY" and ignore blank cells.

So, not comparing to a date in a previous cell but looking at only the one cell and ignoring any blanks.

I feel like this one is close but something is missing...

=IFERROR(IF(ISBLANK([Smart Status Change Date]@row), "Blank", IF(AND(ISDATE([Smart Status Change Date]@row), TODAY() > [Smart Status Change Date]@row), "Late"))), "")

Jeff Reisman · 02/13/23

https://community.smartsheet.com/discussion/comment/362022#Comment_362022

OK, so you want it marked "Late" if the [Smart Status Change Date]@row is a date and if that date is after today - as in, the [Smart Status Change Date]@row is in the future?

And if the [Smart Status Change Date]@row is blank (i.e. not a date,) you just want the cell left blank, empty, yes?

Here's the thing: In Smartsheet formulas, simplicity is best, so try for the least complex formulas that gets your results. Sometimes with IFs, you need to switch them around to look for one condition before another, or look for the opposite of the condition you think you need to look for. In this case, you don't need to specify that the [Smart Status Change Date]@row must be blank, you just give it the requirement that it must be a date to even be considered. If it's not a date, the formula skips right to the "value if false."

=IF(AND(ISDATE([Smart Status Change Date]@row), [Smart Status Change Date]@row > TODAY()), "Late", "")

Smartsheet formulas work left to right, so the first thing this one is going to do is check to see if [Smart Status Change Date]@row is a date; if it is a date, then it checks if that date is after today's date, and if so, sets the value to "Late." If it's not a date, or if the date is before today's date, it leaves the cell blank.

I also recommend leaving IFERROR off your formulas until you are positive they are working correctly. That way, if you get a blank result, you know it's a result of the formula and not the IFERROR suppressing an error message that can help you troubleshoot. (See the links in my signature for formula help pages and error messages.)

Meredith Krueger · 02/13/23

That did it! Perfect, thank you! AND, thank you for a thorough explanation. ;)

I am trying to build a formula that will look at a cell date and then mark "true" (in helper column)

Best Answers

Answers

Help Article Resources

Categories