.txt Clock file to XLS - Difficult

HI There

I need some advise in the import of data to xls.

Attached is the .txt file I get from out Clocking System, now we have to by hand subtract the new time and the old time to see how long someone took a break. Now I have tried VARIOUS ways, but seems to come out a mess every-time to rework it in xls.

for example:

0708 : 0737 means that the person clocked in at 07:08 and clocked out at 0737

Then when there is a "x" after a time, it means it was booked beck into the system
The * means the time was manually added in

Is there a way to extract the times (4 numbers) only and then convert them to time format...I will then do the time subtractions afterwards

I imported your data into Excel and then put this formula in cell N4:

=IF($D4="","",IFERROR((MID(SUBSTITUTE(SUBSTITUTE(":"&$D4&":","x",""),"*",""),FIND(":",SUBSTITUTE(SUBSTITUTE(":"&$D4&":","x",""),"*",""),(COLUMNS($N:N)-1)*5+1)+1,2)&":"&MID(SUBSTITUTE(SUBSTITUTE(":"&$D4&":","x",""),"*",""),FIND(":",SUBSTITUTE(SUBSTITUTE(":"&$D4&":","x",""),"*",""),(COLUMNS($N:N)-1)*5+1)+3,2)&":00")*1,""))

This can be copied across and down as required, and turns each of the numbers into the appropriate time in Excel format (blue cells).

Hope this helps.

Pete

Ahh WOW - Yes Surly I would NOT have solved this on my OWN!! THANKS A LOT!!

When you imported the data, you just had it on Fixed width and "next-ed" it and finish -No other settings?

Once again THANK YOU SO MUCH!!YOU MADE MY DAY

I clicked Finish on the first panel, so I didn't change any of the default settings.

Glad it worked for you.

Pete