asked on

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

I have a report that kicks out 0-23 as time of day example: 17 = 5:00pm. I copy and past this into a different document and refresh my pivot table. How do i format my cell so that when I paste in displaces as 5:00pm rather that 17?

Montoya

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

Rob Henson

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

garyrobbins

ASKER

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.

Montoya

I have this in a second column

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

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garyrobbins

ASKER

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?

Montoya

Hey Gary, yes, good catch. Thanks!

Montoya

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

Rob Henson

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

Montoya

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)

Rob Henson

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