Time formatting 10:00:00 to 10:00

[Mark_Taylor]

Greetings,

I have a start time and end time columns that when I am entering data are fine and show
10:00 AM and 01:00 PM within the app.

In the google sheet backend they show as 10:00:00 and 13:00:00

I have created a virtual column to display theses values as headers using concatenate.
CONCATENATE([Start Time], “–”,[End Time])

This results in the header showing
10:00:00–13:00:00

How can I format this so that I get either
10:00–13:00 or 10:00AM–01:00PM

Thank you in advance.

[Mark_Taylor · Accepted solution]

I figured it out

CONCATENATE(text([Start Time],“H:MM Am/Pm”), “–”,text([End Time],“H:MM Am/Pm”))

Additionally using just 1 H instead of 2 HH makes 13:00 read like 1:00 PM instead of 01:00 PM