convert digits to time

[oravec]

Hello,

Is there an easy way to enter time as 1200 for 12:00 or 30 for 00:30. And use macro to convert all digits to hh:mm format.

Basically I want to be able to enter values without a colon and use macro to convert to time, regardless to what 1, 2, 3 or 4 digits I enter.

I made huge search and replace macro, but know there must be an easier way.

Cliff

 

[IML]

You could use a formula approach such as
=(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"00"),2))+0

and format as hh:mm

or incorporate the same into a macro.

Also
=SUM(MID(TEXT(A1,"0000"),{1,3},2)/{24,1440}) formatted as hh:mm

Okay last edit. Stolen from someone else, can't remember who:

Use
=TEXT(A3,"00\:00")+0

and format as time.

 

[oravec]

Thank you,

That is exactly what I was looking for. It will allow me to get rid of my HUGE search and replace macro's.

Cliff