How to convert a time value from 22:30 to 1030p-CodePudding

I have multiple excel entries in 24 hour time format, that I need to convert to another very specific format.

e.g:

22:00 = 10p

6:00 = 6a

14:30 = 230p

You get the gist; I'm not sure as the length of the resulting time string is not always constant. I.e if the time ends in "00" it only has 2 or 3 characters. if it ends in anything else, it should have 4 or 5 strings

CodePudding user response：

This converts to a new string value in a different cell.

Value to convert in cell A1

=TEXT(A1,IF(MINUTE(A1)=0,"ha/p","hmma/p"))

Examples:

22:00   10p
06:00   6a
6:00    6a
14:30   230p

Seems to work whether values are actual Excel times (eg 14:30:00) or strings eg '14:30.

CodePudding user response：

This can be done by formatting the time as per your requirement. Steps:

Select the cell(s)
Press Ctrl 1(1 not from the number pad but above alphabets). This would open "format cells" dialog box.
Select first tab, Number, and "time" in the category.
Choose whatever type of the time you want to use.

Format cells dialog box is attached for your reference. Click here to see