• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Time Format

Hi Sambit,

Try:
=TEXT(SUBSTITUTE(D5,".",":"),"hh:mm")


and this if you want AM/PM
=TEXT(SUBSTITUTE(D5,".",":"),"hh:mm AM/PM")

Regards,
 
Hi Sambit ,

Two points :

1. The posted formula will convert a numeric value of 4.2 to a text value of :

04:02

which is not what you want.

2. The posted formula will not give you a time value ; instead it will give you a text value.

Narayan
 
Thanks Sir Narayan for the deep eye,
I think this will be better ?

=TIMEVALUE(SUBSTITUTE(D4,".",":"))
Change format to time
 
Hi Khalid ,

Sorry , but the problem still remains ; when ever the minutes are 10 , 20 , 30 , 40 or 50 , which means the decimal number is .1 , .2 , .3 , .4 or .5 , the conversion will go wrong.

The only way is to do the mathematics ; convert the integer portion separately and the decimal portion separately , as in :

=(INT(decimalnumber) + MOD(decimalnumber,1)*100/60)/24

Format the cell which has the above formula the way you want to.

Narayan
 
NARAYANK991 said:
Hi Khalid ,

Sorry , but the problem still remains ; when ever the minutes are 10 , 20 , 30 , 40 or 50 , which means the decimal number is .1 , .2 , .3 , .4 or .5 , the conversion will go wrong.

The only way is to do the mathematics ; convert the integer portion separately and the decimal portion separately , as in :

=(INT(decimalnumber) + MOD(decimalnumber,1)*100/60)/24

Format the cell which has the above formula the way you want to.

Narayan
Click to expand...

Great.... little late, but Sir i got it :)

Entering 4.60
my formula is giving 05:00 AM (correct)
and your's showing 5:00:00 AM (correct)


But when entering 4.6
my formula is giving 04:06 AM (which is definitely wrong)
and your's showing 5:00:00 AM (absolutely correct)

Thank you so much Sir.
 
Hi Khalid ,

You can also do this :

=TIMEVALUE(SUBSTITUTE(TEXT(G12,"00.00"),".",":"))

but I think using 3 functions where the earlier posted formula uses only 2 is not so good. Of course , you can choose which ever appeals to you.

Narayan
 
You must log in or register to reply here.
Back
Top