• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date formula

3G

3G

Member
Hello-

I have 4 columns A/B/C/D- Start Month/End Month/Duration (Months)/Hours


I then have 36 columns representing each of the months for 2011, 2012, & 2013 to the right.


What I need is to break out the hours into the approrpiate 36 columns, depending on their start end date. So, for example, say a row of data looks like this:


January 2011 December 2013 36 360


what I'd like to do is build a formula that will take the duration, divide it by the number of hours, and, place that value (in this case, 10) in each of the 36 cells to the right. So, then, I've distributed the total hours into their respective months based on the duration and the months that those hours start & end. In the end it'd look like this:


Jan Feb Mar Apr ...Dec 13

10 10 10 10 10


Any ideas? Does that even make sense!? :)


3G
 
First of all, you can use the DATEDIF function to calculate the value for duration rather than hardcoding.


Then, for each month, use two conditions (>= startmonth and <=endmonth) and multiply by the monthly value. You can used two nested IF functions or just:

=(thismonth>=startmonth)*(thismonth<=endmonth)*hours/duration

Copy end-to-end!
 
In case you need help on DATEDIF (since help file was removed after Excel 2000):

http://www.cpearson.com/excel/datedif.htm
 
You must log in or register to reply here.
Back
Top