Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



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


Re: Counting how many records within date range(s)

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 < G2))

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

Aladin




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.