Ask Experts Questions for FREE Help !
Ask
    mike1724's Avatar
    mike1724 Posts: 3, Reputation: 1
    New Member
    		  
    #1

    Nov 18, 2009, 12:22 PM
    Formula involving a date, specifically a month
    I'm looking for assistance with what I consider a challenging formula. I want to create a
    SUMIF (if that is the right function) formula that uses a month from a date to keep track of how much time I worked on a specific project. I have a "date" cell formated 1/23/2009 and an "update time" cell that I enter a number representing an amount of time (1.5 for one and a half hours). Then I have a 12 month cells (October Hours, November Hours, etc.. ) that I want to tally the update time entries with the date as the criteria. Is this possible?

    Thanks in advance for any assistance,

    Mike
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Nov 19, 2009, 12:20 AM

    The function you want is probably SUMPRODUCT().

    If you click on GO ADVANCED and use the paperclip icon to upload your workbook (or smaller sample of it), I can help you construct the actual formulas to go in your summary table.
    mike1724's Avatar
    mike1724 Posts: 3, Reputation: 1
    New Member
    		  
    #3

    Nov 19, 2009, 07:52 AM
    I have posted an example of the spreadsheet.

    Thanks J.B.

    Mike
    Attached Files
  1. File Type: xls date_formula_example.xls (18.5 KB, 109 views)
    2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #4

    Nov 19, 2009, 02:25 PM

    In G1 use this:

    =SUMPRODUCT(--(MONTH($D$3:$D$13)=9), $E$3:$E$13)

    Just change the "9" to "10" for October... etc.

    Expand the ranges to match your data, you can allow some expansion room, but don't try to use the entire column and don't go to 50,000 rows needlessly. SUMPRODUCT() is an array formula and shouldn't be applied to unreasonably large ranges if it can be avoided.
    mike1724's Avatar
    mike1724 Posts: 3, Reputation: 1
    New Member
    		  
    #5

    Nov 20, 2009, 07:50 AM

    Thank you JB, I will give this a try and I bow to your expertise!

    Mike
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

One formula that convert any given date to week no.of the year. [ 2 Answers ]

What are the formula in excel to convert any given date to the week of that date in that year. Example: 29 October 2009 is Week 44. TQ. Khalil.

Get max of date field in a table for a particular month [ 3 Answers ]

Hi, I have data in a table as follows: Key date 101 4/25/2006 101 4/26/2006 101 4/27/2006 101 4/28/2006 101 5/25/2006

Excel: date formula [ 5 Answers ]

Say you have a date (21/7/08) typed in a cell and want the cell directly below it to be automatically calculated 7 days from the date in the cell above (27/7/08). How do you do this? Then once you've done this, you want a column of dates to automatically appear as follows: 21/7/08 22/7/08...

Why is my 1 month old spitting up ALL of his formula all of a sudden? [ 6 Answers ]

My 1 month old seems very hungry, but he is spitting up all of his formula all of a sudden. We burp him, which isn't always effective but even if he does burp he still spits up all that he drank which isn't very much maybe an ounce. We don't know what to do. PLEASE HELP!

Cows milk or formula for a 3 month old.pros and cons [ 6 Answers ]

Need to know what is good for a 3 month old... formula or cows milk

View more questions Search