Datetime_format & 'a'?

[Sharon_J]

I have a date column called Entry with a time field, and I want a column which just contains AM or PM based off the corresponding value in Entry. I seem to only get back AM with this formula: DATETIME_FORMAT(Entry,‘A’)

Thanks!

[Justin_Barrett]

Welcome to the community, Sharon! :grinning_face_with_big_eyes: Are you saying that you don’t get anything back with the 'a' option? I ran a quick test, and both 'a' and 'A' work for me.

[W_Vann_Hall · Accepted solution]

@Sharon_J — Any time a datetime function doesn’t return an expected function, I first suspect a GMT mismatch. If you’re entering a post meridiem time and still getting an output of ‘AM’ or ‘am’ (depending on whether you’re using 'A' or 'a' as your format specifier), I’d try entering hourly times from 1 pm to 11 pm and see if the response changes somewhere in that range. If so, that’s probably how far offset your local time is from GMT. :winking_face: If so, the easiest solution will be to enable the ‘Use GMT for all collaborators’ toggle on the formatting tab for {Entry}.

If you’re having some other problem, provide us with a little more info and we’ll take another whack at it.

[Sharon_J]

Thanks! Updating the Entry column’s field type to use GMT finally results to PM when appropriate. I happen to have been entering times at 8pm EST, and that’s why it kept showing AM.

[W_Vann_Hall]

It’s a common problem — I fall into it weekly, myself.

Several times in the past month I’ve started on an omnibus post detailing how datetime fields work — only to discover I still have no idea how they work. I have a handful of bases with multiple columns of date-related fields, each labeled something cryptic like ‘GMT no time’, ‘GMT time’, GMT no time (GMT)’, GMT no time (no GMT)’, and so on, evidently referring to the underlying formulas and formats — none of which mean a thing to me the next day. So far I’ve uncovered a recently introduced bug in the handling of datetime fields, which I reported to support — only to be informed Airtable had never worked the way I [mis]remembered it working.

So, evidently, this needs more research. :winking_face: