Time correction question

[Meebers]

Had some workers inserting times as 651 and 710. Obviously they have to be
changed to 6:51 and 7:10. I hate to think I have to manually edit them all
to correct. Multiple rows and pages to fix :0( Any suggestions?

 

[Tyro]

There is a simple correction but I have a question. Does 651 represent 6:51
AM or 6:51 PM or both?
Excel maintains time as a decimal fraction. 6:51 AM is 0.285417 and 6:51 PM
is 0.785417

Tyro

 

[Meebers]

In this particular example, it represents 6:51 am . there are entries such
as 1851 also that have to be converted to 18:51

 

[Tyro]

Assuming your times are 1 to 4 numeric digits in length and start in A1 and
go down column A, put this formula in B1 and copy down column B.:
A 1 would be 1 minute after midnight, 30 is 12:30 AM, 500 is 5AM 1800 is
6PM. You might want to check your values before using to see if there
is something outside the limits, like 9600, for example. After you use this
formula, format the results as times. Also you can check your times after
using the formula to see if any time is greater than 1. That means that the
time is greater than 24 hours. You can also copy column B and then paste
special as values to get rid of the formulas.

=VALUE(IF(LEN(A1)=1,"0:0"&RIGHT(A1,1),IF(LEN(A1)=2,"0:"&RIGHT(A1,2),IF(LEN(A1)=3,LEFT(A1,1)&":"&RIGHT(A1,2),IF(LEN(A1)=4,LEFT(A1,2)&":"&RIGHT(A1,2),0)))))

Tyro

 

[Meebers]

Tx Tyro....it works perfectly. I will be inserting an extra column next to
the times, insert the formula, do the conversion, paste special to original
column and erase formula column. I owe you a beer!

 

[Tyro]

I'm drinking a beer at the moment, but thanks anyway <g>

Tyro