Change military time to local time if part of a formula?

[Weatheramsey]

I have api data getting drawn into excel regularly with time formatted as the following "2022-04-08T14:00:00-05:00".

I would like to make a chart with these times on it but I want it to be shorter and easier to understand for everyone. "5PM, 6PM, 7PM".

I was able to chop of the unnecessary characters in the beginning and end that was formatted from the original api data source to show just "14:00".

However, now I would like to convert "14:00" to display as "2PM".

I tried to format it with Excel's time options but noticed that only works if the number is directly entered into the cell. It does not work if the time you wish to convert is a part of a formula. In my case, I had to use the REPLACE formula to get my time.

How can I convert this to show the local time?

[#2]

@Weatheramsey 

You may transform text to time with TIMEVALUE(), e.g.

=TIMEVALUE("14:00")

and apply to the result any desired time format

[#3]

Thank you! That worked. It now displays "2:00 PM". But is there a way to display the time value showing only the hour without the minutes? "2 PM"

[#4]

@Weatheramsey 

In addition, you may consider Power Query option where such transformation are doing practically natively, like

The latest column is transforming datetime from UTC-5 into my local time zone.

[#5]

@Weatheramsey 

Select cell(s), Ctrl+1, custom number format and use h AM/PM