Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Date Problem

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...


  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Karlstad, Sweden
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •