Skip to main content

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

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

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.



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.



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.


Reply