I would like to convert 12-hour time that is entered and stored in a text field, into 24-hour time, still stored in a text field.
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. :-)
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:
FileMaker Custom Function:time.Convert( TmStamp ; Offset ; FromForm ; ToForm )
FileMaker Custom Function:TimeFormatAs ( theTime ; type12or24 )
FileMaker Custom Function:Military Time ( TimeField )
FileMaker Custom Function:formatTime ( numbersOnlyMilitaryTime )
And this article in FMI kb (knowledge base):
A Calculation for Converting Text to Time Values | FileMaker
Compare with what you have.
You could do that easily by setting your inspector "Data Formatting" options as "Time" and then in the export dialog, place a check mark for "Apply current layout's data formatting to exported data" as shown below:
Retrieving data ...