Skip to main content

I have a column for “Completed?” with a checkmark option. When this is checked, I’d like for that column (“When complete?”) to populate the time it was checked.


I think I need to combine something like this:


IF({Completed?} = 1, LAST_MODIFIED_TIME({Completed?}))


with


DATETIME_FORMAT(SET_TIMEZONE({Completed?}, ‘America/Chicago’)‘M/DD/YYYY h:mm’)


but I’m not getting it to work the way I was hoping.



Thanks in advance!


Rachael

Hi @Rachael_Castelaz! As you say, this looks like you are on the right track. What exactly is not looking like what you are expecting?

Is your only problem the timezone?


Your second formula is missing a comma.


DATETIME_FORMAT(SET_TIMEZONE({Completed?}, 'America/Chicago'),'M/DD/YYYY h:mm')


If this answers your problem, could you please mark this post a a solution. If not, could you please give an example of what you want to be different.

There could also be some overriding formats in the settings for the field, an example screenshot below. In other words, the formula could be setting the value as Central time, but the field is configured to show GMT.



As @kuovonne said though, if your problem is something else, please post an example of what you want to be different.

I think you may be running into issues because you’re referencing your checkbox field, {Completed?} in your DATETIME_FORMAT() formula instead of the LAST_MODIFIED_TIME() for {Completed?} 🙂 Written that way, the formula is trying to format a date/time value from what the checkbox returns (either a “0” or “1”), instead of a true date/time.


Try this:


IF({Completed?}, DATETIME_FORMAT(SET_TIMEZONE(LAST_MODIFIED_TIME({Completed?}), 'America/Chicago'), 'M/DD/YYYY h:mm'))

Hope this helps!

This could be simplified even further:


LAST_MODIFIED_TIME({Completed?})

As long as the box isn’t checked for a record, there will be no date. Once it’s checked, it will receive the proper date, and it can be formatted as desired via the formula’s Formatting tab.

I think you may be running into issues because you’re referencing your checkbox field, {Completed?} in your DATETIME_FORMAT() formula instead of the LAST_MODIFIED_TIME() for {Completed?} 🙂 Written that way, the formula is trying to format a date/time value from what the checkbox returns (either a “0” or “1”), instead of a true date/time.


Try this:


IF({Completed?}, DATETIME_FORMAT(SET_TIMEZONE(LAST_MODIFIED_TIME({Completed?}), 'America/Chicago'), 'M/DD/YYYY h:mm'))

Hope this helps!


Yes, that works. Thank you so much!!

Is your only problem the timezone?


Your second formula is missing a comma.


DATETIME_FORMAT(SET_TIMEZONE({Completed?}, 'America/Chicago'),'M/DD/YYYY h:mm')


If this answers your problem, could you please mark this post a a solution. If not, could you please give an example of what you want to be different.


Yes, the issue was that I needed to show both 1) when the checkmark was checked and 2) the correct time zone. I have the solution now. Thank you!

Reply


Terms & Conditions