TimeValue formula
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)&":"&R IGHT(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 |
TimeValue formula
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)&":"&R IGHT(F4,2)),if(f4<10000,TIMEVALUE(LEFT(F4,2)&":"&R IGHT(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 "Phrank" wrote in message ... 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)&":"&R IGHT(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 |
TimeValue formula
Thanks VERY much Fred!
Frank On Tue, 19 Sep 2006 22:31:18 -0600, "Fred Smith" wrote: 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 "Phrank" wrote in message .. . 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)&":"&R IGHT(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 |
All times are GMT +1. The time now is 05:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com