Text to Time conversion
 

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

Text to Time conversion
 

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


"bpc" wrote:

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

Text to Time conversion
 

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"))

--
Rick (MVP - Excel)


"bpc" wrote in message
...
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

Text to Time conversion
 

bpc wrote:
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)