If / isBlank statement
I am having a total brain cramp and cannot figure out the formula I need for this.
two columns:
Task Status
Closed Date
There is automation setup that when someone marks a row as Task Status = Completed, it should capture the date and put it in the Closed Date column. Well, that automation works 90% of the time, but we currently have 219 rows where it did NOT work and that messes all of our productivity reports up. So, I am trying to come up with a coalesce type function that works like this:
=if([Task Status]@row = 'Completed',if(isblank([Closed Date]@row 1,[Last Modified Date]@row,[Closed Date])
If the task status is Completed, then show the Closed Date if it's not blank. If it is blank, then show the last modified date. I am sure it's probably some simple typo that I have, but my brain is about ready to burst. I appreciate any help. ☺️
Best Answers
-
I would first try to figure out why the automation isn't working, I love using the datestamp feature. Make sure that the Task Status is CHANGING to "Completed", it won't retroactively run the automation if you set it up after the status had been completed.
To answer your question though, I think this formula would get you what you need in a date formatted field;
=IF(AND([Task Status]@row = "Completed", ISDATE([Closed Date]@row)), [Closed Date]@row, DATEONLY([Last Modified]@row))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Got it.
=IF([Task Status]@row <> "Completed","",(if(AND([Task Status]@row = "Completed", ISDATE([Closed Date]@row)), [Closed Date]@row, DATEONLY([Last Modified Date]@row))))
Answers
-
I would first try to figure out why the automation isn't working, I love using the datestamp feature. Make sure that the Task Status is CHANGING to "Completed", it won't retroactively run the automation if you set it up after the status had been completed.
To answer your question though, I think this formula would get you what you need in a date formatted field;
=IF(AND([Task Status]@row = "Completed", ISDATE([Closed Date]@row)), [Closed Date]@row, DATEONLY([Last Modified]@row))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
I have no clue how to troubleshoot the automation. I have over 2500 records, 500+ that the automation worked correctly on. Today, the automation both worked and didn't work when I was testing and I did nothing different. I am curious if it doesn't work when server traffic is high. I have 46 people using this sheet all day long.
Had to change the formula to:
=IF(AND([Task Status]@row = "Completed", ISDATE([Closed Date]@row)), [Closed Date]@row, DATEONLY([Last Modified Date]@row))
I got an Invalid Column Value, but that was because I had the wrong column type for my target, so fixed that and it worked, mostly. I am getting the date regardless of the Task Status.
By the way, thank you for your assistance!
-
Got it.
=IF([Task Status]@row <> "Completed","",(if(AND([Task Status]@row = "Completed", ISDATE([Closed Date]@row)), [Closed Date]@row, DATEONLY([Last Modified Date]@row))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!