Text to Time conversion

B

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
 
R

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

Glenn

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top