You are reading a single comment by @walm and its replies. Click here to read the full conversation.
  • I am obviously late to this but Mr Prince - I would probably do some remedial work on the spreadsheet NOW in order to save pain later.

    Putting two different forms of info in one cell is usually a recipe for disaster! (No offence!)

    Right now, for example, you can't manipulate the dates even though you have gone through all the effort of typing them in.

    If you just called column F the "date received" column and left it blank until you had a date to fill in - you would have a much friendlier set of data!

    In particular, if you have mis-typed "received" in any of the cells your COUNTIF will be wrong. (Although excel ignores capitalisation IIRC.)

    I am sure know this but putting more than one piece of info into a cell is recipe for a disaster.

    But if you have stuck with the exact format of your entry "received 20120913" then you can quickly create that new more friendly column.

    Just create a new column and copy this cell down the whole thing...

    =IF(F2="","",DATE(mid(F2,10,4),mid(F2,14,2),mid(F2,16,2)))

    That should do it.

    You can just use =COUNT(F2:F3000) to find your number received.

    PLUS you can do more "fun" things like count the number received by month or year.
    Or create a new column that compares date received with the date the document was created or sent out or whatever (assuming you have that data) and then track the time taken to receive it and tell if things are getting faster or slower...

    The exciting possibilities are limitless!! With such a friendly column you are empowered beyond the realms of your excel dreams - but with your existing data you are limited to panicking as to whether or not you remembered "i before e except after c" correctly...

About

Avatar for walm @walm started