Calculating values by date range

[jamie1976]

Hi all

I have a spreadsheet where I have a sales forecast by both annual and quarterly breakdowns. Where I have a start date quoted, unless it is at the start of the quarter, I don't get an accurate figure in the Q1/2/3/4 columns.

Example - Correct result (manually obtained)
Part A - Agreed expected start date Jan-19 - Quarterly sales £3750 - Q1 19 = £3750 (3 months of sales)

Part B - Agreed expected start date Feb-19 - Quarterly sales £18000 - Q1 19 = £12000 (2 months)
Part C - Agreed expected start date June-19 - Quarterly sales £12000 - Q2 19 = £4000 (1 month)​

Example - Incorrect formula currently used =IF((AG$4>=$S7)*AND(AG$4<$T7),$Y7,0)

Part A - Agreed expected start date Jan-19 - Quarterly sales £3750 - Q1 19 = £3750
Part B - Agreed expected start date Feb-19 - Quarterly sales £18000 - Q1 19 = I either get - or £18000 (depending on date method used in cell AG4, AH4, AI4 etc.)

I have shared the sheet below, any help you could offer on this would be really appreciated. I have the following formula in the date cell (AG4) =IF((AG$4>=$S7)*AND(AG$4<$T7),$Y7,0) and then just a date in the other cells adjacent to it, this does effect whether a value appears below in the Q1-Q4 cells
​

https://drive.google.com/open?id=1R7oBmnQE372gf6UBdz_Hd_2dWHRvtIqz

 

[RasGhul]

Hi Jamie

If you post some sample data in the forum, your google drive link is broken.

 

[jamie1976]

Hi there

Thanks for coming back to me. I have tried another link, hopefully this works as I can't upload an image within this forum

https://drive.google.com/open?id=1R7oBmnQE372gf6UBdz_Hd_2dWHRvtIqz

 

[RasGhul]

Hi Jamie,


I'm not completely sure what you need - your formula is testing is IF(Agreed expected date & expected finish date is before or after 1 date in AG4 for Quarter 1),Quarterly sales,0).

If you need to test any Agreed start date during each Quarter you could do your setup like this? This will allocate the Est. sales to the correct Quarter;

https://www.dropbox.com/s/vui4ruvn8mx14nk/Excel - Sales query_JP.xlsx?dl=0

**Make sure to check that the start and finish dates in AG4 & AG5 columns are correct quarter date periods.