how to check the missing log

Hi again Lonely,

Thanks for sending me your data. Here's what to do with the Excel worksheet:

For data contained in cell A1, enter the following formula in cell B1:

=TRIM(LEFT(A1,  SEARCH("(",  A1) - 1))

This puts the description of the test in cell B1.

Here comes the fun one - enter the following formula in cell C1:

=DATEVALUE(TRIM(LEFT(MID(A1,  SEARCH("(",  A1) + 1,  999),  LEN(MID(A1,  SEARCH("(",  A1)  +  1,  999)) - 1)))  +  TIMEVALUE(TRIM(LEFT(MID(A1,  SEARCH("(",  A1) + 1,  999),  LEN(MID(A1,  SEARCH("(",  A1)  +  1,  999)) - 1)))

This puts the Date & Time of the test in cell C1, although it'll initially look something like 39313.8896412037. To show this value as a date & time use Format > Cells > Number > Custom, & enter "dd/mm/yy hh:mm" in the Type box. Enter mm/dd/yy hh:mm if you want to display the date in US format.

Now copy the formulas in cells B1 & C1 all the way down your column of data. Columns B & C now contain the test descriptions & test dates/times as FORMULAS - if you want to insert them into a worksheet as VALUES, just select all of the B & C column cells & use Edit > Copy. Then select the cells into which you want to insert the values & use Edit > Paste Special > Values.

Hope this helps - please let me know how you get on.

Regards,

Greg M

thaks...
it works like magic...

Hi Lonely,

Many thanks for the feedback - delighted to have helped.

Best regards,

Greg M