SUBSTITUTE

[Guest]

Hello from Steved

From decimal point to Time.

from 4376.10 to 4376:10 using the below formula.

=SUBSTITUTE(TEXT(VLOOKUP(A1:A1,0,0),"#.00"),".",":")*1.

It gives me #N/A

What have I forgotten to do please to have the formula working.

 

[Pete_UK]

Hello from Steved

From decimal point to Time.

from 4376.10 to 4376:10 using the below formula.

=SUBSTITUTE(TEXT(VLOOKUP(A1:A1,0,0),"#.00"),".",":")*1.

It gives me #N/A

What have I forgotten to do please to have the formula working.

I don't understand your VLOOKUP in the middle. I do this quite often,
and use the formula:

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

assuming your decimal value is in A1 and your time is in
minutes:seconds.

Hope this helps.

Pete

 

[Guest]

Hello Pete from Steved

Vlookup part was my attempt at writing the formula and as you discovered it
was wrong, I thankyou for your Formula.

Cheers.

Thankyou.

 

[Pete_UK]

Glad to be of help, Steve, thanks for feeding back.

Pete

 

[Sandy Mann]

Hello Steved,

Just out of intererest your own formula works in a cell formatted as [h]:mm
without the VLOOKUP():

=SUBSTITUTE(TEXT(A1,"#.00"),".",":")*1

--
Regards,


Sandy
In Perth, the ancient capital of Scotland

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