how to check the missing log

i got a list which contain the date and time for my windows logging.
each interval time is approx 3mins.
after i manually calculate,found that in 24hrs ,certain time, the windows failed to log.
how to find the missing log?
it got thounsands over logs,which i can't check it one by one.
can i just with a formula and find the log?

example of my log:
17/08/2007 05:13:55
17/08/2007 05:16:34
17/08/2007 05:19:14
17/08/2007 05:21:54
17/08/2007 05:24:34
17/08/2007 05:27:14
17/08/2007 05:29:55
17/08/2007 05:32:34
17/08/2007 05:35:14
17/08/2007 05:37:54
17/08/2007 05:40:34
17/08/2007 05:43:14
17/08/2007 05:45:54
17/08/2007 05:48:34
17/08/2007 05:51:14
17/08/2007 05:53:54
17/08/2007 05:56:34
17/08/2007 05:59:14

Hi there,

If your list of times begins in cell A1 then enter the following formula in cell B1 & copy it all the way down beside the list:

Please Login or Register  to view this content.

This produces a "Log Missing" message immediately above any cell whose log time is more than four minutes behind the cell above it. To use a different time interval just change TIMEVALUE("0:04") to suit.

You can also enter the following formula (anywhere) to show you the number of the logs that are missing:

Please Login or Register  to view this content.

Hope this helps,

Greg M

the second formula is incorrect.
only one log is missing,but it display 2 logs.

Hi Lonely,

Would you post me a copy of your workbook so I can take a look at it?

Thanks,

Greg M

Hi again,

Sorry - try this:

Please Login or Register  to view this content.

I tried it on an example which HAD two missing logs, so the formula appeared to work correctly. Hope this one's OK for you.

Regards,

Greg M

the 1st formula always display 2 logs, but actually only one is missing.
the second formula always return 0, actual one missing log.

here is another problem,i upload the file here.
is in DOC format, after i copy them into excel, the formula doesn't work anymore.
HELP!

Hi Lonely,

I'm really sorry about this - I just didn't check my code thoroughly enough. The following should work & may be inserted anywhere in your worksheet:

Please Login or Register  to view this content.

Regarding the second problem, I think the difficulty may be caused when you import the data from a document. When I imported the data from the file you uploaded, the times had trailing ")" characters at the end - these need to be trimmed etc.

Can you upload a sample of the EXCEL version of the times so I can see what they look like? If the file is too large to upload then just upload a typical extract from it.

Sorry about the first problem & hope I can help with the second one.

Regards,

Greg M

ok..i zip the exce sheet...
i just copy the doc (CTRL+A)then paste it to excel.

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:

Please Login or Register  to view this content.

This puts the description of the test in cell B1.

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

Please Login or Register  to view this content.

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