+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Calulation of how many days in column.

  1. #1
    Registered User
    Join Date
    03-21-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Calulation of how many days in column.

    Need help trying to work out how to get my spreadsheet to work out how many jobs were opened in a month.

    I have column A as the date the job was initiated using date format of 14-FEB-10 (DD-MMM-YY). On Worksheet 2 I would like to have the number of jobs to be displayed automatically for each month.

    Can someone provide me with a formula?

    Greatly appreciated.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Calulation of how many days in column.

    Hello losthero,

    Your best option would probably be a pivot table.

    A slower alternative can be achieved with SUMPRODUCT formulae. On sheet2 you can have a list of dates, 1-Jan-2010, 1-Feb-2010, etc. If you want format them to show only month and year. Then in B2 put this formula

    =sumproduct(--(month(Sheet1!$A$1:$A$1000)=month(A1)),--(year(Sheet1!$A$1:$A$1000)=year(A1)))

    cheers

    copy down

  3. #3
    Registered User
    Join Date
    03-21-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Calulation of how many days in column.

    Thank you. I am not quite sure how to get the SUMPRODUCT to work. Can you help break it down for me?

    Sheet 1 Column A I have the dates listed Eg: I have 8 jobs in Jun 2008, 3 jobs in Jul 2008. On Sheet 2 Column C I have the Months listed to display the number of jobs for that month.

    When I copied that formula you provided it came up with an error. Not quite sure where to look to fix it.

    Thank you.
    Last edited by losthero; 03-21-2010 at 09:36 PM.

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Calulation of how many days in column.

    On Sheet 2 enter a date like 1-Jun-2008. Format it to look any way you like, but it must be a date, not a text or a number. Then the formula will work.

    See attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-21-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Calulation of how many days in column.

    Thanks, I can see that yours works. I needed to fiddle around for a little bit to get it to work. Thanks again for your help.
    Last edited by losthero; 03-22-2010 at 12:17 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1