On that first example, I think you can be sure the baby spent some days in the SCN in 2012 because the admit date in col C is 2012-12-24. (Christmas Eve, he muses. I entered the hospital Christmas Eve of 2010 and didn't get out 'til Mother's Day; swine flu.) But it doesn't matter; the relevant factor is that the admit date isn't 2013, so the patient isn't to be included in this count.

Now, about the 3rd example: I get it, the policy is to assign a new patient number in such a case. (I thought you might say that, in fact.) But like all old and grizzled programmers with decades of experience, I am untrusting. However impossible it may seem to you that a patient be admitted a second time with the old patient number, I nevertheless must ask: If the impossible should happen, how should it be counted? Three possible answers: If the patient is readmitted with the same patient number...

1. Count it as 2 admissions.
2. Count it as 1 admission.
3. Call it in some way to the attention of the user—you or whoever—by displaying a message, highlighting it in a new color, whatever.

The way we do this will depend on your answer above. Hint: If you're really determined to believe that it cannot happen, and therefore don't waste any time thinking about it, then answer #2 is probably the easiest.

I haven't asked this yet, but what's your final output? Are you looking for just a number (244 admissions)? Do you need a list of 244 rows, one for each patient showing the admit date and maybe the discharge date? What?

One last consideration: How shall we do this? If you have to do this only once a year, then I'm guessing it isn't worth the trouble to write a VBA program for it; I can just show you how to massage the date with a sort and some functions to come up with the result. If I've misunderstood and you need to do this a lot more often—maybe you want to do the same thing to 43 other worksheets, once this one is done—then a VBA program is probably better.