So I am working on a timesheet and I want to be able to enter the time as a number 0-24 and have it show up as h:mm AM/PM. (ex: enter something like 13.5 and the cell will show 1:30 PM) How in the heck do I make it do this insanely simple thing that excel seems to be fighting me on?
13.5 is seen as 13.5 days by Excel, not hours.
If you want to convert something like 13.5 to a time I think you'll need code.
If posting code please use code tags, see here.
Try this, assuming the 13.5 is placed in B3...
=TIME(INT(B3),(B3-INT(B3))*60,0)
Hope this helps.
-Z
I use the autocorrect option.
Replace ++ in :
type 13++30 and you get 1:30 pm
This works really well
Last edited by popipipo; 11-17-2014 at 04:50 PM.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
If you want 13.5 to show as 1:30 PM, with the 13.5 in A1 enter this in B1 and format as h:mm AM/PM
Formula:
Please Login or Register to view this content.
The result:
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks Zodeeak that definitely helps give me the number I need! Now I just have to figure out how to be able to type the number directly into the cell to be displayed. Anyone? Lol
Also thanks newdoverman, that works as well. Always nice to have options
Last edited by X Cel; 11-17-2014 at 04:54 PM.
A1/24 works ofcourse.
However, it has a disadvantage:
What is your input if you want an output of 1:40 PM?
That wasn't the problem but the answer for 1:40 PM is 13.68 ... serial number x 24
And you know all the minutes out of your head?
@ popipipo
I have many things in there but that isn't one of them....LOL
Hello Xcel
See if this helps
pre format all the cells where you are going to enter time
Use format cells --Custom & create new format 00\:00
after this you just type the time in 24 hr format (without
regds
kmahesh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks