How to Check a Series of Dates Between Two Dates

[dineshtendulkar]

Description Quantity Start Date Finish Date Jan-10 Feb-10
1 2 3 4 1 2 3 4
Excavation 100 05-01-10 14-01-10 30 70

Hi friends,

I am having an excel spreadsheet as shown above. I have splitted every month into 4 weeks..
1 - 7 = First Week
8-14 = Second Week
15 - 21 = Third Week
22 - 28, 30 or 31 = Fourth Week

What I need now is to Split-up the Quantity as per the Start Date and Finish Date as shown above.

I badly need this formula.. Searched in google I cant able get it. Any Experts Please Help Me

Additional Information

For Example, If i am doing Excavation Activity and if its quantity is 100, the start date is 05-Jan-2010 and Finish Date is 14-Jan-2010

Here we are considering 1-7th of Every month as First Week, but the activity starts on 5th January 2010, So in the first week we have the duration of 3 days and it ends on 14th January 2010, so the second week has the duration of 7 days, so the total duration is 10 days, it should divide the quantity as Total Quantity/total duration and should split the total quantity into weekwise distribution. In this example, for first week 3 days duration so in the first week it should fill (100/10)*3 and in the second week the duration is 7 days and so it should fill as (100/10)*7.

Also you should consider that we are everymonth into 4 weeks. For February month, the last
week will have a duration of 7 days, whereas January, March, May, July, August, October, December will have a duration of 10 days in the last week and April, June, September, November will have a duration of 9 days.

Consider other examples such as,

For example, the total quantity of concrete is 500 and the start date is 22-January-2010 and its finish date is 4-April-2010.

Then it should split the quantity as follows,

January 4th Week = 68.49315
February 1st Week = 47.94521
February 2nd Week = 47.94521
February 3rd Week = 47.94521
February 4th Week = 47.94521
March 1st Week = 47.94521
March 2nd Week = 47.94521
March 3rd Week = 47.94521
March 4th Week = 68.49315
April 1st Week = 27.39726

I think now you better understood my question, I am waiting for your reply boss....

 

[Haseeb Avarakkan]

Hello Dinesh,

Firstly define a name EndDay. On Refers to enter this

={0,0;1,3;2,0;3,3;4,2;5,3;6,2;7,3;8,3;9,2;10,3;11,2;12,3}

Then, F3 copy across & down.

=($C3/($E3-$D3+1))*MAX(0,MIN($E3,DATE(YEAR($F$1),INT((COLUMNS($F3:F3)-1)/4)+1,F$2*7)+((F$2=4)*LOOKUP(INT((COLUMNS($F3:F3)-1)/4)+1,EndDay)))-MAX(0,$D3,DATE(YEAR($F$1),INT((COLUMNS($F3:F3)-1)/4)+1,F$2*7-6))+1)

If you don't want to see zero's use the custom format

0.00;;;

Of course will have a more elegant ways.

 

[dineshtendulkar]

Dear Mr. Haseeb

Could you please send me the excel file to dineshtendulkar@gmail.com

How can i define a name Endday and in which cell i should define.

Where i should copy the formula..Please give a detail explanation plz..

Thanks for your reply..

Regards,
Sathish Justin

 

[dineshtendulkar]

Dear Mr. Haseeb,

Could you please explain a little about the formula, and why u used that Endday name and what does it mean

Sathish Justin

 

[dineshtendulkar]

Dear Mr. Haseeb,

This Formula Works Great, except for the leap years. Could you please adopt the same formula considering the leap year. I will be grateful to you if you give me this.

You have reduced my work with one formula. Thanks man. You are really a genius.

Regards,
Sathish Justin

 

[Haseeb Avarakkan]

Glad to hear. It works.

OK, change the EndDay array to;

={0,0;28,0;29,1;30,2;31,3}

Then,

=($C3/($E3-$D3+1))*MAX(0,MIN($E3,DATE(YEAR($F$1),INT((COLUMNS($F3:F3)-1)/4)+1,F$2*7)+((F$2=4)*LOOKUP(DAY(DATE(YEAR($F$1),INT((COLUMNS($F3:F3)-1)/4)+2,0)),EndDay)))-MAX(0,$D3,DATE(YEAR($F$1),INT((COLUMNS($F3:F3)-1)/4)+1,F$2*7-6))+1)

 

[dineshtendulkar]

Dear Mr. Haseeb,

Your sheet is having lot of loop holes. For example, For all Year column you are referring to Cell F11, if i am changing its month then your sheet is not working.

Check it and explain me Mr. Haseeb.

Regards,
Sathish Justin

 

[Haseeb Avarakkan]

Assume your First row F1:AX1 is the 1st day of each month. 1/1/2010, 2/1/2010, 3/1/2010 & formatted as mmm-yy.

F3, copy down & across.

=($C3/($E3-$D3+1))*MAX(0,MIN($E3,LOOKUP(1E+100,1/($F$1:F$1),$F$1:F$1)+(F$2*7-1)+((F$2=4)*LOOKUP(DAY(EOMONTH(LOOKUP(1E+100,1/($F$1:F$1),$F$1:F$1),0)),{0,0;28,0;29,1;30,2;31,3})))-MAX(0,$D3,LOOKUP(1E+100,1/($F$1:F$1),$F$1:F$1)+(F$2*7-7))+1)