Convert 24hr time text field to AM/PM

I am getting an export that expresses a start and stop time as text in 24 hour format. I am trying to make this into AM/PM. Thoughts?

Input:
730
1130
1330
1700

Desired Output:
7:30 AM
11:30 AM
1:30 PM
5:00 PM

And yes, I am hoping the report can be modified, but I am trying to prepare for when it can’t. :sweat_smile:

This formula will do the trick:

IF(Input, DATETIME_FORMAT(DATETIME_PARSE(Input, "hmm"), "h:mm a"))

Screen Shot 2022-02-01 at 6.40.50 PM

1 Like

I did not realize your could put parameters in DATETIME_PARSE… damn. Thanks for answering a silly question.

I forgot to mention (sorry) the time data is coming from a lookup. ARRAYUNIQUE and ARRAYFLATTEN don’t seem to be doing the trick, so it’s returning an #ERROR. The lookup is already formatted as an Integer and it works for doing basic math, so super odd.

Ah ha!

Had to use the ole &'' trick on it cause even though the lookup formatting is making it an integer the formula is still treating it as an array cause it is a lookup field type. That might be a bug…

2 Likes

@ucmavnerd Not a bug. That’s just how lookup fields behave. More often than not, they return arrays of values, even when there’s only a single value being looked up. I broke down all field types in this base, which shows how they’re treated on their own in formulas, as well as how they’re treated when fed through a lookup field.

No. Right. I knew lookups always return as arrays. What tripped me up is the lookup formatting was set to integer. I can even do something like {Lookup Field}+50 and it will add 50 to the value.

So, at the very least, it is an inconsistency where some operators can figure out your intent and/or leverage the formatting parameter and others cannot.

Going forward, I’ll just know to always append &'' to any formula variable from a lookup to be safe.

Thanks for the table though, that is super handy to have bookmarked now.

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.