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. ☺️

Find more posts tagged with

Sheets

Accepted answers

Jason Tarpinian

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

JHuber77

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

All comments

Jason Tarpinian

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

JHuber77

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!

JHuber77

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.