Decimal to Time

[Steved]

Hello from Steved

1384.06 to excel time ie 1384:06
1384.56 to excel time ie 1384:56
1384.59 to excel time ie 1384:59
The above is correct in hours and minutes but has a "."
instead of ":"
how do I write a formula to change to excel time please.

Thankyou

 

[Bob Phillips]

Hi Steve,

Try this formula

=INT(A1)/24+(A1-INT(A1))*100/1440

and format the cells as [hh]:mm

 

[Peo Sjoblom]

Assume the times start in A1

=INT(A1)/24+TIME(,MOD(A1,1)*100,)

copy down
and format as [hh]:mm

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Anders Silven]

Hi Steved

Quick and dirty is to Edit>Replace "." with ":" (without the quotes). You may
record a macro if you do this often.

HTH
Anders Silven
Excel 2002

 

[Guest]

Hello from Steved

Thankyou Gentlemen.

 

[Ron Rosenfeld]

Hello from Steved

1384.06 to excel time ie 1384:06
1384.56 to excel time ie 1384:56
1384.59 to excel time ie 1384:59
The above is correct in hours and minutes but has a "."
instead of ":"
how do I write a formula to change to excel time please.

Thankyou

Three methods.

Format the result cell as [h]:mm

Use one of these formulas:

=(INT(A1)+MOD(A1,1)*100/60)/24
=--SUBSTITUTE(A1,".",":")
=DOLLARDE(A1,60)/24

With regard to the last formula, If the DOLLARDE function is not available, and
returns the #NAME? error, install and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron

 

[Soo Cheon Jheong]

Steved,

=--REPLACE(A1,5,1,":")


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--