Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Single select choice creates date in a second column

Topic Labels: Formulas
2520 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Patrick813
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,
How do I create a column or what is the formula that will automatically add the date based on a single selection in a previous column? For the date, I just need Month / Day / Year, not time.
Example:
Column A single select choices are: New / Pending / Closed.
Column B is the date when I select Column A- NEW,
Column C is the date when I change Column A from NEW to PENDING,
Column D is the date when I changed Column A from NEW or PENDING to CLOSED.

(I do want to be able to see all three dates which is why I’m using Columns B, C and D vs. have the date change in Column B.)

Thanks, Pat Barron

4 Replies 4
AlliAlosa
10 - Mercury
10 - Mercury

Try something like this… (modify for each column)

IF({Column A} = "NEW", DATETIME_FORMAT(LAST_MODIFIED_TIME({Column A}), 'M/D/YYYY')

The only problem I found with this is if you have fields that were changed prior to the LAST_MODIFIED_TIME() function becoming available, the above will return #ERROR! as it doesn’t have a time to pass to DATETIME_FORMAT(). You can eliminate the DATETIME_FORMAT() section of the formula to get rid of the #ERROR!s, but you’ll see the time in the field as well as the date, and you said you didn’t want that.

Also… after re-reading your post, I don’t think you’ll be able to track when you change a field from one specific value to another. The formula above will only display a date if the value in the field is currently “NEW”; it doesn’t know what it was prior to the change.

Hope this helps!

EDIT: I was able to get rid of the #ERROR!s with the following formula:

IF({Column A} = "NEW", IF(ISERROR(DATETIME_FORMAT(LAST_MODIFIED_TIME({Column A}), 'M/D/YYYY')), BLANK(), DATETIME_FORMAT(LAST_MODIFIED_TIME({Column A}), 'M/D/YYYY')))

Thank you very much for the help!

Is there a way for column B(NEW) to keep the date when I later change column A to PENDING or CLOSED?

example:

New client enrolls I select NEW:

Column A is now single select NEW, Column B is today’s date (7/25/19)

next week I select PENDING:

Column A is now single select PENDING Column B stays 7/25/19, Column C becomes 8/1/19 etc.

Thanks for your help. What you’ve done is already helpful.

That’s where you’re going to hit a wall, I’m afraid. Formula fields can’t have their values “frozen” when a certain state is reached. One option is to tie into either Zapier or Integromat. Trigger a zap/scenario when that state changes, and have the zap/scenario record the current date into the relevant date field.

Thanks for the feedback. It’s a good suggestion to try Thank you. Thanks, Pat.