how to check the missing log

**only_lonely** · 08-20-2007, 05:28 AM

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

**Greg M** · 08-20-2007, 02:10 PM

Hi Lonely,

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

Thanks,

Greg M

**Greg M** · 08-20-2007, 02:21 PM

Hi again,

Sorry - try this:

="Missing Logs = "  &  COUNTIF(B1:B18,"Log Missing")

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

**only_lonely** · 08-20-2007, 11:25 PM

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

**only_lonely** · 08-21-2007, 12:05 AM

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!

**Greg M** · 08-21-2007, 07:52 AM

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:

="Missing Logs = "  &  COUNTIF(B1:B9999,"Log Missing")

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