Date Problem

[jips]

I have a list of people and start dates with the company. I need to extract the number of people that started in any selectable month eg June-97

sample table

K FARLEY October-99
C L SUSSEX September-01
J L BROOKES October-95
M J E O'HAGAN July-87
R W GILBERT April-97
F T LARKIN June-97
K A STEPHENS June-97
J L WILLIAMS February-97
C GOSLING October-97
C R PAYNE April-98
M C KIRBY June-98
C SLATER March-99
P BRYANT April-99
K FPSTER August-99
J DILLON December-99
B RAGGATT September-00
E SMILES October-00
R GRINSTEAD November-00
S MIDDLETON April-97
C JENKINS February-01

Please help

[Dave Hawley]

Hi

Most efficient way would be to create a simple Pivot table, group the date field by Month, then place it in the PageField position.

The second option I would go for is the use of Excels database functions combined with a Validation list of the dates.

I have some examples of how this can be done hre: http://www.ozgrid.com/download/default.htm
Under:
DFunctionsWithValidation.zip

There is also the Advanced Filter option or even the Auto filter option.

[Tom Schreiner]

Hi,

I can do this but someone else might know an easier way. If not, I have to ask you a couple of questions

Do all of the dates end with -##
A line and two digit year???

And also. Are all of the months spelled out in long form as you have listed?

Is this list in one column or many?

Would it be a problem to list all of your data in one column and break up the lists into 12 tables on a seperate sheet?

Let me know...

[ClaesB]

If using VBA is an option, this code might do the work. (Not very fancy, though)
--------------------------------
public Sub CountEmp()
Dim lngLast As Long, lngRow As Long
Dim lngCount As Long
Dim strMonth As String

strMonth = InputBox("Month ?")
lngLast = Range("A65536").End(xlUp).Row

For lngRow = 1 To lngLast
If InStr(1, Cells(lngRow, 1), strMonth) Then
lngCount = lngCount + 1
End If
Next

MsgBox lngCount

End Sub
----------------------------

Rgds
/Claes