Date Problem

jips

New Member
Joined
Mar 19, 2002
Messages
1
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top