+8I 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
Best answer by AlliAlosa
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?} :slightly_smiling_face: 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!
+13Hi @Rachael_Castelaz! As you say, this looks like you are on the right track. What exactly is not looking like what you are expecting?
+29Is 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.
+13There 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?} :slightly_smiling_face: 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!
+21This 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.
+8I 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?} :slightly_smiling_face: 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!!
+8Is 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!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.