Counting how many records within date range(s)

Posted by Sheila Vaughan on September 12, 2001 1:37 AM

I have copied an employee table from Access to Excel. One field is 'Date of Appointment'. I now want to count how many people have been appointed since a particular date or within a particular period. I don't want to use the Excel 'serial numbers' as HR managers wouldn't be able to cope with that. With previous versions of Excel I used the COUNTIF function without any problem but it doesn't seem to work now with "standard" dates. Can anyone help?

Sheila

Posted by Aladin Akyurek on September 12, 2001 4:22 AM

Sheila,

> I have copied an employee table from Access to Excel.

What do you mean by "copied"? You can just export an Access table to Excel. I expect the field "Date of Appointment" in Access to be of data type Date/Time. The values should appear then in date format in Excel.

> One field is 'Date of Appointment'. I now want to count how many people have been appointed since a particular date or within a particular period. I don't want to use the Excel 'serial numbers' as HR managers wouldn't be able to cope with that. With previous versions of Excel I used the COUNTIF function without any problem but it doesn't seem to work now with "standard" dates. Can anyone help?

I'd expect the following to work:

Lets say that the date values are in C2:C25 and the criterion dates are in G1 and G2 with G2>G1.

=COUNTIF(C2:C25,">="&G1)

will produce a count of those apppointed on G1 and later.

=SUMPRODUCT((C2:C25>G1)*(C2:C25 &LT; G2))

will give you a count of those appointed between G1 and G2 exclusive.

Aladin