Checkbox Formula: If Due Date has passed but completion date is blank
I have two columns:
1: A "Date Calc" field (contains a formula) that automatically displays a due date for a task based on manually entered dates from a prior field. This is a date column.
2: A Date Complete field that is manually entered. This is a date column.
I have added a third column that will automatically check a box if the Date Calc field is:
- In the past
- The Date Complete is blank
I do not want the box to be checked if the Date Complete field is NOT blank.
I have used the following formula which is almost there, but it is triggering a checkbox for dates in the Date Calc field that are NOT in the past.
This is what I am using: =IF([Date Calc]@row < TODAY(), ISBLANK([Date Complete]@row), 1)
Best Answers
-
Hi @Paula Meunier,
Consider updating your formula to an IF(AND and also adding in the operation to tell the formula what to do if the conditions are not met.
In my testing the formula in the checkbox column looked like this:
=IF(AND([Date Calc]@row < TODAY(), ISBLANK([Date Complete]@row)), 1, 0)
IF(AND(
Logical Expression 1: Date Calc row less than today
Logical Expression 2: Date Complete cell is blank
)) to close out the IF And
, to switch to value if true
True = 1 (Check the box)
, to switch to value if false
false = 0 (don't check the box)
-
It works! Thank you!
Answers
-
Hi @Paula Meunier,
Consider updating your formula to an IF(AND and also adding in the operation to tell the formula what to do if the conditions are not met.
In my testing the formula in the checkbox column looked like this:
=IF(AND([Date Calc]@row < TODAY(), ISBLANK([Date Complete]@row)), 1, 0)
IF(AND(
Logical Expression 1: Date Calc row less than today
Logical Expression 2: Date Complete cell is blank
)) to close out the IF And
, to switch to value if true
True = 1 (Check the box)
, to switch to value if false
false = 0 (don't check the box)
-
It works! Thank you!
-
Yay! I'm very happy to hear that. Have a lovely day.
-
Summer, I have a similar issue - maybe you can help. I am trying to use an "expires on" column to populate a checkbox column - however, many times the expires on column is blank - in those instances, I do not want the box checked in the other column - this is my original formula, and the one I tried that caused the error - any ideas to tweak it to solve the problem?
=IF([Expires on]@row < TODAY(90), 1, 0)
=IF(AND([Expires on]@row < TODAY(90), ISBLANK([Expires on]@row)), 1, 0))
-
Hi @Decall95
I'd suggest adding an IF statement at the front, like so:
=IF([Expires on]@row = "", 0, IF([Expires on]@row < TODAY(90), 1, 0))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
This worked, thank you so much!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!