Find Count of Days within another (seperate) Date Range

[jackrich]

Hi,

I am assessing the impact of product price increases on volume (qty) and sales (£/$).

The prices are changed sporadically and at no set intervals. For example pries were changed on the 15th Jan and 13th Feb this year.

We have set reporting dates (calender months) so i am hoping to be able to divide the report which shows sales & qty's during the 15th Jan - 13th Feb segment and apportion them to the correct months.

Hope that makes sense.

I have the start & end dates of the calender months in cells A4:B18 and each time a new report is run (ie 15th Jan or 13th Feb, these dates go across the top i.e. cell C3 = 15th jan & D3 = 13th Feb.

I would like to return a % in cell C4 which relates to the amount of total days in the segment that relate to Jan (ie. 29 days in the segment, 16 relate to Jan so 55.2%).

Cell C5 would then return a % which relates to the amount of total days in the segment that relate to Feb (29 days in the segment, 13 relate to Feb so 44.8%).

Cells D4&D5 would return the next reporting segment and how many days in Jan/Feb respectively (the answer will be 0 but a constant formula acrosss all would be perfect.

Apologies for the huge post but tried to give as much info as poss.

Thanks anyone who could help.

Jack

 

[JLGWhiz]

Hi,

I am assessing the impact of product price increases on volume (qty) and sales (£/$).

The prices are changed sporadically and at no set intervals. For example pries were changed on the 15th Jan and 13th Feb this year.

We have set reporting dates (calender months) so i am hoping to be able to divide the report which shows sales & qty's during the 15th Jan - 13th Feb segment and apportion them to the correct months.

Hope that makes sense.

I have the start & end dates of the calender months in cells A4:B18 and each time a new report is run (ie 15th Jan or 13th Feb, these dates go across the top i.e. cell C3 = 15th jan & D3 = 13th Feb.

I would like to return a % in cell C4 which relates to the amount of total days in the segment that relate to Jan (ie. 29 days in the segment, 16 relate to Jan so 55.2%).

Cell C5 would then return a % which relates to the amount of total days in the segment that relate to Feb (29 days in the segment, 13 relate to Feb so 44.8%).

Cells D4&D5 would return the next reporting segment and how many days in Jan/Feb respectively (the answer will be 0 but a constant formula acrosss all would be perfect.

Apologies for the huge post but tried to give as much info as poss.

Thanks anyone who could help.

Jack

In cell C4

=((D3-C3)-DAY(D3))/(D3-C3)

In cell D4

=((D3-C3)-DAY(C3))/(D3-C3)

Make sure cells C4 and D4 are formatted as Percentage

 

[jackrich]

Thanks JLGWhiz,

apologies for the late reply. I realized when i went away i hadn't explained it very well. I have since solved the problem which allows me to explain what i was looking for perhaps in a clearer way.

The overarching goal was to return the number of days between two dates and then apportion that count into the appropriate month.

The reports have been run on the following days so far this year;

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]15/1/14[/TD]
[TD="class: xl65, width: 64, align: right"]13/2/14[/TD]
[TD="class: xl65, width: 64, align: right"]21/3/14[/TD]
[TD="class: xl65, width: 64, align: right"]28/4/14[/TD]
[/TR]
</tbody>[/TABLE]

the formula i wanted would calculate that there were 28 days between the 15th Jan and 13th Feb and would also return how many of those 28 days were in Jan (15) for the January cell (E3) and and how many were in Feb (13) for the Feb cell (E4).

Cell C3 - C14 are the first days of each month, cells D3 - D14 are the last days of each month.

in cells in cell G2 - U2 are the actual days the report is run ((i.e. 15th Jan, 13th feb etc etc...).

This is the formula i ended up using...

for cell H7

=IF(IF(AND($C7<=G$2,$D7>=G$2),$D7-G$2,IF(AND(H$2>=$C7,H$2<=$D7),(H$2-$C7)+1,IF(G$2>=$C7,$D7-G$2,0)))<=0,0,(IF(AND($C7<=G$2,$D7>=G$2),$D7-G$2,IF(AND(H$2>=$C7,H$2<=$D7),(H$2-$C7)+1,IF(G$2>=$C7,$D7-G$2,0))))).

Still may not make sense but felt you warranted a reply as you took the time to answer my query.

thanks,