Predict Sales between two dates based on history sales per Quarter

[hatahetahmad]

Hi,
If I had two dates from 1/15/2018 to 7/10/2018
and I Had sales per day from historical data :
Qtr1 - 20 - per day
Qtr2 - 25 - per day
Qtr3 - 18 - per day
Qtr4 - 32 - per day

I wanna know how much I'll sold based on this data per quarter like this :
from 1/15 to 3/31 how many working days multiply Qtr 1 (65 days * 20 = 1300)
and from 4/1 to 6/30 (78 days * 25 =1950)
and from 7/1 to 7/10 (9 days * 18 =162)
I excluded Fridays only
total expected sales is (1300 + 1950 + 162 = 3412)

My solution is checking start date quarter and end date quarter then make a huge if to achieve that
Can we have more fast, dynamic and stable solution

Thank you

 

[FormR]

Hi, here are a couple of options that you could try to adapt to your set up - personally I think it would be better to break it up a bit (formulas in C9:G9 copied down), but I've included a one pass option I9 copied down.

Note the changes to your historical sales table to include the Quarter from and to dates.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Q	Q From	Q To	Per Day
2	Q1	01/01/2018	03/31/2018	20
3	Q2	04/01/2018	06/30/2018	25
4	Q3	07/01/2018	09/30/2018	18
5	Q4	10/01/2018	12/31/2018	32
6
7
8	Start	End	Q1	Q2	Q3	Q4	Total		One Pass
9	01/15/2018	07/10/2018	1300	1950	162	0	3412		3412
10	01/01/2018	01/31/2018	540	0	0	0	540		540
11	01/01/2018	12/31/2018	1540	1950	1422	2528	7440		7440
12	04/01/2018	07/01/2018	0	1950	18	0	1968		1968
13	01/01/2018	01/10/2018	180	0	0	0	180		180
14	12/31/2018	01/01/2019	0	0	0	32	32		32
Data

Cell Formulas
Range	Formula
C9	=MAX(0,NETWORKDAYS.INTL(MAX($B$2,$A9),MIN($C$2,$B9),16))*$D$2
D9	=MAX(0,NETWORKDAYS.INTL(MAX($B$3,$A9),MIN($C$3,$B9),16))*$D$3
E9	=MAX(0,NETWORKDAYS.INTL(MAX($B$4,$A9),MIN($C$4,$B9),16))*$D$4
F9	=MAX(0,NETWORKDAYS.INTL(MAX($B$5,$A9),MIN($C$5,$B9),16))*$D$5
G9	=SUM(C9:F9)
I9	{=SUMPRODUCT(IF(A9<=$C$2:$C$5,IF(B9>=$B$2:$B$5,NETWORKDAYS.INTL(IF($B$2:$B$5>$A9,$B$2:$B$5,A9),IF($C$2:$C$5<$B9,$C$2:$C$5,B9),16))),$D$2:$D$5)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hatahetahmad]

Thank you FormR