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

Need to project out and count backwards based on month

N

Numb7r

New Member
Hello,


I was wondering if I could get help with this. Basically, I need to take the "Date Due" (when its suppose to be done) and count back assign it a value based on type. For example...If Due on OCT and type 1 (which takes 21hours) and put in 7 for OCT,SEPT, And AUG...I need for any for month past the current month to be past due. I can make a validation list to have a rolling month chart at the top (Thanks Chandoo!) i'd like for it to reference that so, all i have to do is change the month and it'll rollover.


Types 1 & 2 are treated equally, Type 3 is only on the month due equal to 1. I have tried many thing to get this to work. Thanks!!!


Sample File:


https://docs.google.com/spreadsheet/ccc?key=0AsGJTakQHBGDdEVBZzE5NnBUTG9DMUR1T2tjRGFjZUE
 
Numb7r


Firstly, Welcome to the Chandoo.org forums.


I am unclear on exactly what your after


Using your first data 8/12/2012 Type 2 = 12 hrs

exactly what do you want ?

How do he columns marked Month relate to the data above

Do you want to simply show what month something is due based on the Date - Hours ?


If you can clarify the above we should be able to assist
 
Hi! thanks for the reply


To answer your question: Sine 8/12/2012 has already passed all 12 hours would be over due.

so C2=12...I can account for all Over due projects(That is there are NO hours being projected out)


My problem is when there is a future date and i need to bring it back. I have updated the worksheet for row 10 for an example of what I do manually right now.


Row 10: due date is october type 2. So I have to input Oct=3, Sept=3 and Over due=3.
 
Basically since this project was due in august, the whole 12 hours are over due, So i tell it to match the past due date to the current month and if its greater to set it equal to the total.

=IF(C2<$I$15,B2,"0")


My main issue is when a project is coming from two months out and there will be over due hours. I can't split it properly while correctly identifying the month and Due date.
 
flick your thumb out from later your 2 front teeth.yet it didn't emerge to contribute much to their success for a hunter. make your outline robust you should really tell your base robust.,isabel marant sneaker
prints and chips of craft Details,isabel marant!With so many cilia loss companies on the Internet it's complex to comprehend which one you can believe If they hammered Slovenia aboard Wednesday,isabel marant, which gives you stunning photos. Since John has hundreds of articles and other correlates,isabel marant sneaker,uniquediscusThis is where you have a accident to tell your latent bidder about yourself and your affair As they do never know you they are working to want apt retard you out
All this ambition take area from April six this daily it stands to gain major international admission amid the fields of science plus technology.Wells Fargo sign amid namely component of a roomy buyer service of Wells Fargo Now you understand this vital information. Is it because it namely quite perplexing to put an leak every of the bonding agencies aboard the tooth? specialize among characteristic practices. Surprisingly among many cases. Let's list some of the tips that ambition assist with this challenge The babies are so penurious within their enthusiasm to improve it namely a proficient counsel to attempt plus tell them from uncommon sources inside order to wash up the genes for the fresh babies to occ Besides the financial implications.erection equipment.

Related articles：

 
Hi ,


I am still not clear on what your requirements are.


I have uploaded your workbook , with dates into the future , so that your problem can be clearly understood. Is it possible you can fill in the required values in the relevant cells , and re-upload this filled-in workbook ?


http://speedy.sh/PYdjn/sample-project-dates.xlsx


Narayan
 
NARAYANK991: Thanks for the reply, i was unable to download your file ( it was a hosting site in spanish), I did however update my file with the data inputted so you can see what i want to happen. I do this manually right now (takes forever).
 
Hi ,


Your revised file is still confusing ; can you clarify whether the addition of numbers in the range E through L , on any row , should equal the number in column D or column B ?


For instance , B7 is 1 ; D7 is 14 , and E7 is 4.


D6 through D10 do not have the formula that other cells in column D have.


The file I had uploaded to speedy.sh ( it is a file-sharing website , I have downloaded my file by clicking on the link in my earlier post ; I have no idea how it has become a spanish website on your PC ) , I have uploaded to Google docs ; can you put in your numbers in this file ?


https://docs.google.com/open?id=0B0KMpuzr3MTVbWM5dWJ6eXdhdkE


Narayan
 
Sorry for late, Google Apps and pretty much everything else blocked at work.......Columns E through L are the upcoming scheduling months. D are projects that were not completed in time, so their hours are past due. The Totals should equal column B.


Basically, this is a scheduling thing. The dates say this project should be completed by Column C, The total hours is determined by the type and the hours spread out over 3 months, starting from the month it is due. Any Time that has passed and project is still "open" needs to add in the Over due column....The formula in Column D takes into account projects that are unfinished and are totally past their due date, based on last month (IE August)


I have updated your file with the values. Thanks for your help.
 
I updated the file to include how I got it to schedule times for that month, however, i cant get it to distinguish those that included and those that are not. For example: project due October 2012 would be included in September 2012...but one due in April 2013, would not be.


=IF((YEAR(D2)>=YEAR($F$1)),(MONTH(D2)>=MONTH($F$1))*C2,"0")
 
I think for the over due, I could do a Subtotal from the total subtracted from the columns in front of it......My main issue right now is how to rewrite this formula:


=IF((YEAR(D2)>=YEAR($F$1)),(MONTH(D2)>=MONTH($F$1))*C2,"0")


to include 2months out only and that Type 3 only get 1 hour in the month due( no where else)


that is an project due in September, would need to include projects with due dates in OCT and NOV only:


Ex:


Due date: 1st month 2month 3rd month


Sept 2012 July Aug sept

OCT 2012 Aug sept oct

NOV 2012 sept oct nov

Dec. 2012 oct nov dec
 
Hi ,


I just checked the file on Google docs , and I am still confused ; can you please say which are the cells you want to have formulae in ? Is it just the values in column D , or do you want numbers to be put in cells E2 through L13 , where ever applicable ?


Does it make any difference whether a project is type 1 , 2 or 3 regarding the values in the above-mentioned cells , or is it just a matter of dates / months ?


Narayan
 
Hello again


to answer your questions: I need formulas in all months columns E-M


The difference is only with type 3 where the the value goes only in the month due. Where as for TYpe 1&2 it is spread out over 3 months. from the due date the only thing that matters is the MONTH and Year, The DAY is irrelevant.
 
its confusing to explain.....

If the due date falls in the month column set it equal to the HOURS value (D), Include those projects that have a due date 2months out from the month column. If it is a type 3, only set it equal to the month DUE.


Due date: 1st month 2month 3rd month


Sept 2012 July Aug sept

OCT 2012 Aug sept oct

NOV 2012 sept oct nov

Dec. 2012 oct nov dec


So if the due date is NOV 2012 for a type 1, i need a 7 value in OCT, NOV & DEC.

if its the due date is NOV 2012 for a type 3, I need a 1 value for NOV only.
 
Hi ,


Sorry , I was looking at my file ; I have just downloaded your file. Let me go through it and get back to you.


Narayan
 
Hi ,


You are posting something in your latest post , which is not reflected in your updated worksheet.


Let me take each row , one by one.


1. The due date = 2/14/2012 ; since this is not greater than the cut-off date of 8/31/2012 , all of the cells F2 through M2 will contain 0.


2. Due date = 7/10/2012 ; same as 1.


3. Due date = 8/12/2012 ; same as 1.


4. Due date = 8/15/2012 ; same as 1.


5. Due date = 9/1/2012 ; this is greater than the cut-off date. Hence E6 will be 0. Since this is a type 2 project , the distribution per month = 12/3 = 4. Hence F6 will be 4 ; since G1 through M1 are after the project month ( September ) , they will all contain 0.


6. Due date = 9/15/2012 ; this is greater than the cut-off date. Hence E7 will be 0. Since this is a type 3 project , the distribution per month = 1. Hence F7 will be 1 ; since G1 through M1 are after the project month ( September ) , they will all contain 0.


7. Due date = 10/15/2012 ; this is greater than the cut-off date. Hence E8 will be 0. Since this is a type 2 project , the distribution per month = 12/3 = 4. Hence F8 will be 4 ; why should G8 contain 3 ? Since H1 through M1 are after the project month ( October ) , they will all contain 0.


Can you clarify ?


Narayan
 
1. The due date = 2/14/2012 ; since this is not greater than the cut-off date of 8/31/2012 , all of the cells F2 through M2 will contain 0.


2. Due date = 7/10/2012 ; same as 1.<b> CORRECT </b>

3. Due date = 8/12/2012 ; same as 1.<b> CORRECT </b>

4. Due date = 8/15/2012 ; same as 1.<b> CORRECT </b>


5. Due date = 9/1/2012 ; this is greater than the cut-off date. Hence E6 will be 0. Since this is a type 2 project , the distribution per month = 12/3 = 4. Hence F6 will be 4 ; since G1 through M1 are after the project month ( September ) , they will all contain 0.


<b> No, E6= 8, because it is 2 months past due. F6=4 correct. G2 through M2 will be =0, that is correct. </b>


6. Due date = 9/15/2012 ; this is greater than the cut-off date. Hence E7 will be 0. Since this is a type 3 project , the distribution per month = 1. Hence F7 will be 1 ; since G1 through M1 are after the project month ( September ) , they will all contain 0.


<b> Correct!, since it is Type 3, one hour will be assgined in the month DUE ONLY </b>


7. Due date = 10/15/2012 ; this is greater than the cut-off date. Hence E8 will be 0. Since this is a type 2 project , the distribution per month = 12/3 = 4. Hence F8 will be 4 ; why should G8 contain 3 ? Since H1 through M1 are after the project month ( October ) , they will all contain 0.


<b> No, Since this project is Due in October, It needs work to be done in months Agust, Sept, Oct. So this one is 1month past due


so it should be like this: E=4, F=4, G=4, H-M=0 </b>


Thanks for Helping, I will prolly thank you a million times if this can work.
 
Hi ,


You have given a lot of food for thought !


It's late at night here ; I can work on this only tomorrow morning. Hope you can wait.


Narayan
 
making progress...its the most beautiful thing, but im getting somewhere:


=IF(AND(YEAR($D2)=YEAR(F$1),(MONTH($D2)=MONTH(F$1)),A2=3),1,IF(AND(($A2<>3),YEAR($D2)=YEAR(F$1),MONTH($D2)=MONTH(F$1)),$B2,IF(AND(($A2<>3),(YEAR($D2)>=YEAR(F$1)),(YEAR(D2)<=YEAR(F$1)),(MONTH($D2)>=MONTH(F$1)),(MONTH($D2)>=MONTH(F$1))),$B2,"0")))


now it gives me the values up to October correctly, but it skips nov-dec, i think its my syntax, hopefully with this you can see where i'm heading.


I have updated my file:


https://docs.google.com/spreadsheet/ccc?key=0AsGJTakQHBGDdEVBZzE5NnBUTG9DMUR1T2tjRGFjZUE
 
Hi ,


I just looked at your updated file , and on row 13 , there are 4 values of 7 , in F13 through I13 ; how is this possible ? In all your posts , you have been mentioning that the counting backwards is to be for 3 months only. Is this correct ?


Narayan
 
You must log in or register to reply here.
Back
Top