Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract Month from Date (Office 2003)

    From a list of arbitrary dates, I need to extract only the Month for each date entry .......... the mission is to count the number of occurrences of data during each month and using the statment ........ =COUNTIF(Logged!$C4:$C35,Summary!F1) ............ where F1 is the month and year and in the range C4:C35 resides a series of aribirtary dates, the reult returned is the number of instances of the date < 01MMMYYY > since when the MMMYYYY is entered in F1, Excel automatically sets the day to 01 ....... in the example attached, the countif and sumif functions are intended to key off the month and to faciltitate this, an additonal column has been added in the "logged" tab. I would like to avioid this artifice if possible, relying on the power of excel functionality to recognize that a date falls within a certain month without monotomous strings of nested if statements ........
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Month from Date (Office 2003)

    Instead of entering the dates manually in C4:C35 on the Logged sheet, you could enter the formula

    =DATE(YEAR(B4),MONTH(B4),1)

    in C4 and fill down. Alternatively, you can omit this column altogether and use the following formula in F2 on Summary:

    =SUMPRODUCT((YEAR(Logged!$B$4:$B$35)=YEAR(F$1))*(M ONTH(Logged!$B$4:$B$35)=MONTH(F$1)))

    and this in F3:

    =SUMPRODUCT((YEAR(Logged!$B$4:$B$35)=YEAR(F$1))*(M ONTH(Logged!$B$4:$B$35)=MONTH(F$1))*Logged!$D$4:$D $35)

    You can select F2:F3 and fill right. See attached version.
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Month from Date (Office 2003)

    Hans, I am deeply grateful for your kind attention to my request, and hope that you are being paid handsomely for your ability and skill ........ and in this connection, would like to know how the resources for this site are funded ..... may one contribute and how is this done ........

    Kind Regards,

    Mark

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Month from Date (Office 2003)

    Hi Mark,

    I appreciate your kind words. All participants in Woody's Lounge are volunteers - we do this for fun, not for money.

Posting Permissions

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