Solved: Convert time to 24 hour format

Daisy_Ramirez · 11-20-2019 04:07 PM

Does anyone have a dependable 24 hour format expression you’d be willing to share? Trying to convert this as an example:

A million thank you’s!

Daisy_Ramirez

Below is an expression I’ve had success with for the 24 hour Format:

TEXT(TIME([Date Time Entered]),“HH:MM”)

View solution in original post

Suvrutt_Gurjar

Hi @Daisy_Ramirez,

I believe you may wish to mention some more details. Is this 12 hour format date captured in a spreadshet? Do you wish to capture 24 hr format only in spreadsheet or are you looking for 24 hour format to be displayed in specific views of the app?

Daisy_Ramirez

Thanks Suvrutt,

I’m looking to have the 24 hr formatted date generate in a .csv file so my guess is it would have to display the 24 hour format in appsheet? The .csv seems to reflect other data adjustments I’ve made with the appsheet table.

Suvrutt_Gurjar

Thank you @Daisy_Ramirez.

Please explore if following workaround created based assuming Googlesheet as back end helps.

Please create a column with Google sheet formula of converting AM/PM time into 24 hrs format and then as a text. The column (Time24Text) and the correspnding Google sheet expression are highlighted in green. The column Time24Text is with spreadsheet formula and type as Text in the app. It converts the 12 hr time from adjecant column Time into 24 hr format.

The images below shows the table view in the app and then downloaded CSV.

Table :

!

CSV:
!

Daisy_Ramirez

Got it. I’ll report back shortly. Thanks so much Suvrutt!

Daisy_Ramirez

Thanks Suvrutt, this worked but I’ll post another expression within AppSheet I’ve had some success with.

Thanks for your help!

Kanha_PM_Office

Hi, @Suvrutt_Gurjar,
How to make the user enter time in 24 format?!

Suvrutt_Gurjar

Hi @Kanha_PM_Office,

Here is my understanding. I am sure there will be more insights from the community. I am unsure on how to compel the user to enter the time in 24 hour format especially a time after 12 PM. Once a field is user enterable ,I believe there may not be options to ensure the user enters only desired value, as long as it is a valid value. Indirect ways could be typically where we wish the user to follow certain pattern , we could use initial values from system or use actions to set the values so that user goes on the anticipated pattern.

Also in device( mobile ,tablet, desktop), if the device time setting is set to 24 hrs format , the user is most likely to follow that format as he gets presented with those specific formats when changing the field. However still if the user needs to enter 18:00:00 for 6PM and he enters 06:00:00 , even in 24 hour format, I am unaware how it can be prevented.

Kanha_PM_Office

Yes @Suvrutt_Gurjar, this is exactly the problem which we are facing.!!

Steve

You cannot if the column type is Time or DateTime.

Kanha_PM_Office

Any other option as Text?

Steve

You’d have to manually validate the user’s input, and the user wouldn’t have the convenience of the time picker.

Why do you want to force 24-hour format?

Kanha_PM_Office

Okay.
To avoid errors in AM / PM selection. They shall easily select the time if it’s 24 hour format.

Audit_CKGoC

Hi. Did you find a way to solve this problem? Thanks

Daisy_Ramirez

Below is an expression I’ve had success with for the 24 hour Format:

TEXT(TIME([Date Time Entered]),“HH:MM”)

Suvrutt_Gurjar

Hi @Daisy_Ramirez

Thank you for posting a compact expression.

Just wanted to mention, the reason I suggested Google sheet based approach. These very useful TEXT() expressions for managing date/time that you used were added by the AppSheet team about two weeks after we discussed Google sheet in this thread.

I agree with you that these are indeed very useful ,compact expressions for handling datetime.

Daisy_Ramirez

Awesome! I missed this post - I thought I was brilliant!