Convert to time

[Floyd Forbes]

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

 

[oldchippy]

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

Hi Floyd

If A1 contains 8.00

format A2 to Time, then input

=(A1/24)+A1

oldchippy :

 

[Dave Peterson]

Divide by 24 and format as time.
=a1/24
(8.50 will convert to 8:30 AM)

 

[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

 

[Pete_UK]

Just to be different <g>

Pete

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

 

[Floyd Forbes]

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

 

[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

 

[Sandy Mann]

Hi old chippy,

=(A1/24)+A1

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Floyd Forbes]

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

 

[Dave Peterson]

One more:

=TIME(INT(A1),MOD(A1,1)*100,0)

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

 

[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.

 

[Floyd Forbes]

Thank you for your help. You are the best.

Floyd Floyd
Turks & caicos Islands
B.W.I

 

[Sandy Mann]

Just another way:

=INT(A1)/24+(MOD(A1,1))/14.4

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk