CONCATENATE with 12 hour time

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])

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

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

Thanks. That was the solution.

1 Like

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?

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.


1 Like

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 :slight_smile:

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.

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.

4 Likes

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

1 Like

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.

3 Likes

Thanks @Kirk_Masden :slight_smile:

1 Like

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