TimeValue formula

  • Thread starter Thread starter Phrank
  • Start date Start date
P

Phrank

Hello,

I have a TimeValue formula that works well for hh:mm, but I also need
to expand this to hh:mm:ss, and I've tried several times to get this
right, but can't seem to come up with the proper combination. Here's
the one that works:

=IF(F4="","",IF(F4<1000,TIMEVALUE(LEFT(F4,1)&":"&RIGHT(F4,2)),TIMEVALUE(LEFT(F4,2)&":"&RIGHT(F4,2))))

With the above formula in F5, if I enter 1234 in cell F4, then in cell
F5 I get 12:34. I need to be able to enter 123456 to get 12:34:56.
Thanks.

Frank
 
To support the entry of seconds, change your formula to:

=IF(F4="","",IF(F4<100000,TIMEVALUE(LEFT(F4,1)&":"&mid(f4,2,2)&":"&RIGHT(F4,2)),TIMEVALUE(LEFT(F4,2)&":"&mid(f4,3,2)&":"&RIGHT(F4,2))))

If you want to support all entries (ie, hmm, hhmm, hmmss, hhmmss), your formula
would be:

=IF(F4="","",IF(F4<1000,TIMEVALUE(LEFT(F4,1)&":"&RIGHT(F4,2)),if(f4<10000,TIMEVALUE(LEFT(F4,2)&":"&RIGHT(F4,2)),IF(F4<100000,TIMEVALUE(LEFT(F4,1)&":"&mid(f4,2,2)&":"&RIGHT(F4,2)),TIMEVALUE(LEFT(F4,2)&":"&mid(f4,3,2)&":"&RIGHT(F4,2))))))

It's ugly but it works.

Regards,
Fred
 
Thanks VERY much Fred!

Frank

To support the entry of seconds, change your formula to:

=IF(F4="","",IF(F4<100000,TIMEVALUE(LEFT(F4,1)&":"&mid(f4,2,2)&":"&RIGHT(F4,2)),TIMEVALUE(LEFT(F4,2)&":"&mid(f4,3,2)&":"&RIGHT(F4,2))))

If you want to support all entries (ie, hmm, hhmm, hmmss, hhmmss), your formula
would be:

=IF(F4="","",IF(F4<1000,TIMEVALUE(LEFT(F4,1)&":"&RIGHT(F4,2)),if(f4<10000,TIMEVALUE(LEFT(F4,2)&":"&RIGHT(F4,2)),IF(F4<100000,TIMEVALUE(LEFT(F4,1)&":"&mid(f4,2,2)&":"&RIGHT(F4,2)),TIMEVALUE(LEFT(F4,2)&":"&mid(f4,3,2)&":"&RIGHT(F4,2))))))

It's ugly but it works.

Regards,
Fred
Click to expand...
 
Back
Top