Convert text into time

[SherriKilgore]

How do you convert text stored as 300 (which is 3 pm) into a time field?

 

[pr2-eugin]

How are you certain that 300 is 3 PM and not AM? If it is 1500 makes sense to be 3 PM. Could you explain this?

 

[SherriKilgore]

You are correct, 300 is 3am and 1500 is 3pm.

 

[pr2-eugin]

So give a few examples of the data in the table.

 

[jdraw]

Why/How did you decide to use a text box for time?

 

[SherriKilgore]

I am trying to track how much time an employee is taking for lunch.

Here is some more data. The clock out is what time the employee clocked out for lunch and the Clock On is what time the employee clocked back in from lunch.
I need to convert the Clock Off and Clock On from text to time so I can calculate how much time the employee took for lunch.

Thanks for your help.

EMPNODCLOCK OUTCLOCK ONTDATED1623013192/10/2014163120012202/10/2014192193019502/10/2014194110011202/10/2014195190019192/10/2014200190219212/10/20142103003192/10/2014216113011492/10/2014223193019492/10/20142263153352/10/2014227193119482/10/2014228190019192/10/20142312312502/10/20142333303502/10/20142343303552/10/2014237114011582/10/2014242194020002/10/2014243113011492/10/2014244113011492/10/2014246112011392/10/20142563003202/10/2014259190919292/10/20142603313492/10/2014269190019202/10/2014

 

[pr2-eugin]

EMPNODCLOCK OUTCLOCK ONTDATED1623013192/10/2014163120012202/10/2014192193019502/10/2014194110011202/10/2014195190019192/10/2014200190219212/10/20142103003192/10/2014216113011492/10/2014223193019492/10/20142263153352/10/2014227193119482/10/2014228190019192/10/20142312312502/10/20142333303502/10/20142343303552/10/2014237114011582/10/2014242194020002/10/2014243113011492/10/2014244113011492/10/2014246112011392/10/20142563003202/10/2014259190919292/10/20142603313492/10/2014269190019202/10/2014

Not sure where it begins or ends. So I don't think I can be of any help here ! Good Luck mate !

 

[SherriKilgore]

Sorry about the table not posting correctly.

Emp Nbr Clock Out Clock On
123 301 319
162 1200 1220
194 1100 1120
246 1120 1139

 

[pr2-eugin]

Try something like..

Public Function CTime(inputStr As String) As Date
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    inputStr = Format(inputStr, "0000")
    CTime = TimeSerial(CLng(Left(inputStr, 2)), CLng(Right(inputStr, 2)), 0)
End Function

Use the function in a Standard module. Give the module a name different than the function name.