Convert to time

F

Floyd Forbes

How would I go about converting 8.00 to 8:00:00 AM in excel.
Can this be done?
 
O

oldchippy

Floyd said:
How would I go about converting 8.00 to 8:00:00 AM in excel.
Can this be done?
Click to expand...
Hi Floyd

If A1 contains 8.00

format A2 to Time, then input

=(A1/24)+A1

oldchippy :
 
P

Pete_UK

Assuming your value is in A1, try this:

=VALUE(INT(A1)&":"&MOD(A1,1)*60&":00")

and use a custom format on the cell to display it with AM/PM.

Hope this helps.

Pete
 
P

Pete_UK

Just to be different <g>

Pete

Pete_UK said:
Assuming your value is in A1, try this:

=VALUE(INT(A1)&":"&MOD(A1,1)*60&":00")

and use a custom format on the cell to display it with AM/PM.

Hope this helps.

Pete
Click to expand...
 
F

Floyd Forbes

Thank you, But when I key 8.15 it gives me 8:09. How do I convert 8.15 to
8:15?
 
P

Pete_UK

If you are entering hours and minutes as hh.mm (using a full-stop to
separate them rather than the colon), you can convert this to normal
time format as follows:

=VALUE(INT(A1)&":"&MOD(A1,1)*100&":0")

I assumed earlier (as did the other responders) that you had hours and
decimal minutes, hence the factor of 60.

Hope this helps.

Pete
 
S

Sandy Mann

Hi old chippy,
=(A1/24)+A1
Click to expand...

does show as 8:00 when formatted as time but it is 8 AM on January 8 1900 -
reformat as General and you will see that the cell contains 8.33333333333.
As you probably know, the *8* represents day number 8

--
Regards,

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk
 
F

Floyd Forbes

Pete_UK, When I key 8.20 I get
#############################################.
How can I get 8:20 to show?
 
D

Dave Peterson

I think you're getting hit by too many digits:

=VALUE(INT(A1)&":"&ROUND(MOD(A1,1)*100,0)&":0")

Seems to work ok.
 
F

Floyd Forbes

Thank you for your help. You are the best.

Floyd Floyd
Turks & caicos Islands
B.W.I
 
Top