Solved: CONCATENATE with 12 hour time

Michael_Serafin · 11-04-2019 04:00 PM

Hi,

I’m using the following expression in a virtual column. The StartTime/EndTIme columns display in 24 hour format. How can I change this to 12 hours?

Thanks

CONCATENATE([Service 1], " ", [StartTime], " “,[EndTime],” ", [First_Last Name])

Steve

Try wrapping [StartTime] and [EndTime] in TEXT():

CONCATENATE(
  [Service 1],
  " ", 
  TEXT([StartTime]),
  " ",
  TEXT([EndTime]),
  " ",
  [First_Last Name]
)

View solution in original post

Steve

Try wrapping [StartTime] and [EndTime] in TEXT():

CONCATENATE(
  [Service 1],
  " ", 
  TEXT([StartTime]),
  " ",
  TEXT([EndTime]),
  " ",
  [First_Last Name]
)

Michael_Serafin

Thanks. That was the solution.

rrhirani

Hello @Steve, I have wrapped Time column with TEXT(Time) Column Type and name both are Time.
Still I am getting 24hrs format instead of AM and PM. If I do the same with DATETIME column then it is working. Any suggestion?

Steve

This is likely because of your device and/or spreadsheet locale settings: TEXT() applies your locale settings and the column’s display settings to generate the text.

rrhirani

Hello @Steve, Thanks for your reply. Yes, I’ve read your article regarding locale settings and I already did that. Initially locale was Australia and I changed it to United Kingdom.

Still I am getting time in 24hr format I want it in 12 hour format e.g. 09:22 AM

I have written

TEXT([Time])

Would you please suggest which setting is wrong here. Thanks

Steve

This appears to be a bug with the Show column type. I’ve reported it internally. In the meantime, consider displaying the Time column but setting its Editable? property expression to FALSE to prevent the user from changing it.

Former Community Member

We did recently introduce a date and time formatting parameter for the TEXT function. It didn’t change the behavior for the one-parameter version.

You should be able to explicitly ask for the 12-hour format with TEXT([Time], "HH:MM AM/PM").

It looks like there’s still a bug here, but this might help as a workaround in the mean time.

rrhirani

@Steve_Howard: Thanks a lot Steve It’s working like a charm!! Thanks @Steve for your suggestion I make the edit false

Kirk_Masden

Hi @rrhirani! I know that your question has been answered but I was thinking about how to get a result in 12-hour time that doesn’t have AM or PM attached to it. Here’s what I came up with:

if(hour(text(now(),“HH:MM:SS”))>12,TEXT(now()-“012:00:00”, “H:MM”),TEXT(now(),“H:MM”))

Again, I suspect you don’t need this but I though I’d share it just in case.

rrhirani

Thanks @Kirk_Masden

Mark_Holanda

Thank you for this Steve… this help me as well!