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
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.
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.
This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.