?
Solved

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

Posted on 2014-08-06
11
Medium Priority
?
93 Views
Last Modified: 2014-08-06
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?
0
Comment
Question by:garyrobbins
  • 5
  • 4
  • 2
11 Comments
 
LVL 19

Expert Comment

by:Montoya
ID: 40243690
do you mean that you want to reformat the source cel so that it displays the actual time in 12/24 format?
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40243727
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
0
 

Author Comment

by:garyrobbins
ID: 40243844
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Montoya
ID: 40243843
I have this in a second column

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

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40243886
Iammontoya - your formula will only show "h:mm" but I believe Gary wants "h am/pm" formatting.

The following will do this:

=TEXT(P4/24,"h am/pm")

Thanks
Rob H
0
 

Author Comment

by:garyrobbins
ID: 40243893
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?
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40243910
Hey Gary, yes, good catch. Thanks!
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40243916
I did notice that in my haste I was adding redundant code.
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40243912
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
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40243920
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)
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40243935
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question