SUBSTITUTE

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Steved said:
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.
Click to expand...

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
 
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.
 
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
 
Back
Top