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?
garyrobbinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MontoyaProcess Improvement MgrCommented:
do you mean that you want to reformat the source cel so that it displays the actual time in 12/24 format?
0
Rob HensonFinance AnalystCommented:
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
garyrobbinsAuthor Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

MontoyaProcess Improvement MgrCommented:
I have this in a second column

=IF(D4<=12,TEXT(D4/24,"h:mm"),TEXT((D4-12)/24,"h:mm"))
0
Rob HensonFinance AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garyrobbinsAuthor Commented:
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
MontoyaProcess Improvement MgrCommented:
Hey Gary, yes, good catch. Thanks!
0
MontoyaProcess Improvement MgrCommented:
I did notice that in my haste I was adding redundant code.
0
Rob HensonFinance AnalystCommented:
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
MontoyaProcess Improvement MgrCommented:
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
Rob HensonFinance AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.