Time Format Needed

[BrianExcel]

What format would I use if I wanted a cell to display as hours:minutes? For example, if someone typed in 1.65 it would automatically translate that to 2:05?

 

[AlisaA]

Just to make sure I understand, would it always be that the number before the decimal would be hours and the number after the decimal would be minutes? So that 1.65 is 1 hour + 65 minutes = 2 hours 5 minutes, and NOT 1.65 hours which is actually 1 hour 39 minutes?

 

[BrianExcel]

Correct. It would always be 2 hours 5 minutes, not the other way.

 

[Jared_8878]

=text(yourcell,"h:mm")

 

[AlisaA]

=text(yourcell,"h:mm")

Actually, that doesn't give the result for which he is looking.

It would be simple enough to create a formula that would link to the input cell and make it work, but, Brian, you definitely need this as a format for the input cell, correct? An interesting challenge this is!

 

[BrianExcel]

Thats correct Alissa.

 

[Jared_8878]

I am wondering how this number was obtained?

if you want 1.65 to show as 2:05.

based on the other comments the 1 represents an hour - if that is the case then how did the digits to the right of the decimal ever get above 60? I am suspicious of that.

Anyways, just to parse the text you could use(value is in A1):


=IF(RIGHT(A1,LEN(A1)-FIND(".",A1))>60,LEFT(A1,FIND(".",A1)-1)+1,LEFT(A1,FIND(".",A1)-1))&":"&IF(IF(RIGHT(A1,LEN(A1)-FIND(".",A1))>60,RIGHT(A1,LEN(A1)-FIND(".",A1))-60,RIGHT(A1,LEN(A1)-FIND(".",A1)))<10,"0"&IF(RIGHT(A1,LEN(A1)-FIND(".",A1))>60,RIGHT(A1,LEN(A1)-FIND(".",A1))-60,RIGHT(A1,LEN(A1)-FIND(".",A1))),IF(RIGHT(A1,LEN(A1)-FIND(".",A1))>60,RIGHT(A1,LEN(A1)-FIND(".",A1))-60,RIGHT(A1,LEN(A1)-FIND(".",A1))))