Help

Re: If complete- Timestamp, CST

Solved
Jump to Solution
964 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Rachael_Castela
6 - Interface Innovator
6 - Interface Innovator

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.

image

Thanks in advance!

Rachael

1 Solution

Accepted Solutions
AlliAlosa
10 - Mercury
10 - Mercury

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!

See Solution in Thread

7 Replies 7
Matthew_Thomas
7 - App Architect
7 - App Architect

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.

Matthew_Thomas
7 - App Architect
7 - App Architect

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.

Screen Shot 2020-05-27 at 1.52.13 PM

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

AlliAlosa
10 - Mercury
10 - Mercury

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!

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.

Yes, that works. Thank you so much!!

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!