I have received much help from this forum over the years. I wanted to post this as a way to hopefully provide some help to someone else. I had searched for an answer to this question on this forum, but didn't find what I was looking for. I then expanded my search to Google, but the only thing that really showed up was a bunch of custom functions. I finally solved it myself using what seems to me to be a fairly simple process.
Time12: text field with original time entered in 12-hour format (ie. "12:30 AM")
Time24: calculation field with text result in 24-hour format (ie. "00:30")
Time24 field calculation...
Right ( "0" & Hour ( GetAsTime ( Time12 ) ) ; 2 ) & ":" & Right ( "0" & Minute ( GetAsTime ( Time12 ) ) ; 2 )
// 'GetAsTime' returns 12-hour time with AM/PM.
// 'Hour' and 'Minute' both return single-digit 24-hour time, instead of double-digit (for some unknown reason?).
// To get around this...
// Add a "0" to the front of the 'Hour' and 'Minute' results.
// Use 'Right' to only keep the first two characters from the right.
// If 'Hour/Minute' returns double-digit, then only those two will be kept.
// If 'Hour/Minute' returns single-digit, the new "0" and the single-digit will be kept.
Hopefully someone finds this useful. :-)
1 of 1 people found this helpful
Why store time as text in the first place? Store it in a field of type time and the same field can easily be formatted to display the value in either format with no need for any calculations.
Thanks for the question Phil. The reason is because I primarily need the time for exporting to various services (ie. Google MyBiz, Facebook BizMgr, etc.). Those services require specific formats and with my limited FM knowledge, getting the time in text was what seemed best to get 100% consistent, predictable exports.
Maybe there's a better way? This is working well for our needs currently.
That's fine to have the calculated field for exporting, but I would still make Time12 a time field rather than text for use on your layouts in whatever format is required.
There are many custom functions you or others may research:
And this article in FMI kb (knowledge base):
Compare with what you have.