How do i change the format of a cell to display the hour of day when a #0-23 is displayed

do you mean that you want to reformat the source cel so that it displays the actual time in 12/24 format?

If you have literally the number 17 for 5:00pm you will need to divide by 24 to convert it to hours. You can then use the standard time formats to display as a time.

Thanks
Rob H

So the report gives me the left column i would like it to display the right column.  The Format of the right column

00  =  12am
01  =  1am
02  =  2am
Etc.
12  =  12pm
13  =  01pm
14  =  02pm
Etc.

I have this in a second column

=IF(D4<=12,TEXT(D4/24,"h:mm"),TEXT((D4-12)/24,"h:mm"))

Rob, I made a slight alteration.  =TEXT(B4/24,"hh:mm am/pm")  This works perfectly, but is there a way to do it without creating a second column?

Hey Gary, yes, good catch. Thanks!

I did notice that in my haste I was adding redundant code.

I had originally put "hh:mm am/pm" but then read your comment showing "2 PM" so changed it to what I submitted.

I don't believe there is a way of doing it without using a separate column. Can you adjust the way that the number is produced in the first place, dividing it by 24 at source.

If you didn't already know, Excel recognises date and time as serial numbers. Dates are based on a serial number since 01/01/1900 using a whole number for each day. Times are then a portion of a day, 6 am being 0.25, 12 noon being 0.5, etc hence the need to divide the whole number that you have by 24, conning excel into thinking it is a decimal portion of a day.

Thanks
Rob H

there is no way of doing that without doing a second column, that I know of, unless you were programmatically changing your source column (VBA)

You could achieve it with a second copy & paste in your process.

After pasting your data into the report, select a cell containing the number 24 and copy it to clipboard, Ctrl +C.

Then select your time data column and select Paste Special. Choose Values in the top part and select Divide in the mid section. Click OK. Then format the column as "hh:mm am/pm".

Thanks
Rob H