We help IT Professionals succeed at work.

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

garyrobbins
garyrobbins asked
on
107 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?
Comment
Watch Question

MontoyaProcess Improvement Mgr
CERTIFIED EXPERT

Commented:
do you mean that you want to reformat the source cel so that it displays the actual time in 12/24 format?
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Author

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.
MontoyaProcess Improvement Mgr
CERTIFIED EXPERT

Commented:
I have this in a second column

=IF(D4<=12,TEXT(D4/24,"h:mm"),TEXT((D4-12)/24,"h:mm"))
Finance Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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?
MontoyaProcess Improvement Mgr
CERTIFIED EXPERT

Commented:
Hey Gary, yes, good catch. Thanks!
MontoyaProcess Improvement Mgr
CERTIFIED EXPERT

Commented:
I did notice that in my haste I was adding redundant code.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
MontoyaProcess Improvement Mgr
CERTIFIED EXPERT

Commented:
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 HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.