Convert 24hr time text field to AM/PM

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:

This formula will do the trick:

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

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…

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