Text to Time conversion

[bpc]

I have time values as text (134 or 1614 for example) entered into a column
(J4) that I need to convert to a time value (1:34 or 16:14). Helpers cells
are ok but no VBA please. I have tried =IF(J4="","",TEXT(J4,"00\:00")) and
=IF(j4="","",MOD(j4,1)) to no avail as I need to peform another calculation
(thank you T Valko) on this data. Thoughts? If it helps I have the date for
the corresponding times in an adjacent cell(H4). BPC

 

[Teethless mama]

=IF(J4="","",TEXT(J4,"00\:00")+0)

 

[Rick Rothstein]

The TEXT function returns... this should be no surprise... a text string.
You want a number, so you have to coax Excel into making the string into a
number. You do this be involving the text string in a calculation. Any one
of these should work for you...

=IF(J4="","",--TEXT(J4,"00\:00"))

=IF(J4="","",0+TEXT(J4,"00\:00"))

=IF(J4="","",1*TEXT(J4,"00\:00"))

 

[Glenn]

I have time values as text (134 or 1614 for example) entered into a column
(J4) that I need to convert to a time value (1:34 or 16:14). Helpers cells
are ok but no VBA please. I have tried =IF(J4="","",TEXT(J4,"00\:00")) and
=IF(j4="","",MOD(j4,1)) to no avail as I need to peform another calculation
(thank you T Valko) on this data. Thoughts? If it helps I have the date for
the corresponding times in an adjacent cell(H4). BPC

Another option:

=IF(J4="","",LEFT(J4,LEN(J4)-2)/24+RIGHT(J4,2)/24/60)