Convert 24hr time text field to AM/PM

[ucmavnerd]

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

[ucmavnerd]

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

[Justin_Barrett]

This formula will do the trick:

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

[ucmavnerd]

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

[ucmavnerd]

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.

[ucmavnerd]

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…

[Justin_Barrett]

@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.

Airtable

Airtable Field Reference - Airtable

Explore the "Airtable Field Reference" view on Airtable.

[ucmavnerd]

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.