Formula to break out days of month in week.

[mseferog]

Hi,
I have weekly data that is based on Monday as the beginning of the week then counting 7 days to Sunday. My problem is that some weeks cross two months as in the example below week 1/29/2018. How do I write a formula to pick the amounts in each of the two months (Jan, Feb) and then show me the values in each of the two months that were in that particular week?
See example below.

Start Date End Date Month Sales $s Sales $ Jan Sales $ Feb
1/1/2018 1/7/2018 01JAN18 $1,523
1/8/2018 1/14/2018 01JAN18 $1,452
1/15/2018 1/21/2018 01JAN18 $1,892
1/22/2018 1/28/2018 01JAN18 $1,502

1/29/2018 2/4/2018 02FEB18 $1,587 ?? ??

2/5/2018 2/11/2018 02FEB18 $1,458
2/12/2018 2/18/2018 02FEB18 $1,478
2/19/2018 2/25/2018 02FEB18 $1,399

 

[Johnny Thunder]

You could do something like this : =IF(MONTH(D6)=MONTH(C6),"Find Partial Amount", "Find Normal Amount")

D6= Your End Date
C6= Start date
"Find Partial" = How you would tell the partial month sales
"Find Normal Amount" = However your formula was getting the sales regularly.

Hi,
I have weekly data that is based on Monday as the beginning of the week then counting 7 days to Sunday. My problem is that some weeks cross two months as in the example below week 1/29/2018. How do I write a formula to pick the amounts in each of the two months (Jan, Feb) and then show me the values in each of the two months that were in that particular week?
See example below.

Start Date End Date Month Sales $s Sales $ Jan Sales $ Feb
1/1/2018 1/7/2018 01JAN18 $1,523
1/8/2018 1/14/2018 01JAN18 $1,452
1/15/2018 1/21/2018 01JAN18 $1,892
1/22/2018 1/28/2018 01JAN18 $1,502

1/29/2018 2/4/2018 02FEB18 $1,587 ?? ??

2/5/2018 2/11/2018 02FEB18 $1,458
2/12/2018 2/18/2018 02FEB18 $1,478
2/19/2018 2/25/2018 02FEB18 $1,399

 

[jtakw]

Hi,

Formula started getting a little tricky when accounting for growing Months into the year...
In my sample, Columns A and B are Real Date values, D1:G1 are Real Date values formatted mmm

Book1
	A	B	C	D	E	F	G
1	Start Date	End Date	Month Sales	Jan	Feb	Mar	Apr
2	1/1/2018	1/7/2018	$1,523	1,523.00
3	1/8/2018	1/14/2018	$1,452	1,452.00
4	1/15/2018	1/21/2018	$1,892	1,892.00
5	1/22/2018	1/28/2018	$1,502	1,502.00
6	1/29/2018	2/4/2018	$1,587	529.00	1,058.00
7	2/5/2018	2/11/2018	$1,458		1,458.00
8	2/12/2018	2/18/2018	$1,478		1,478.00
9	2/19/2018	2/25/2018	$1,399		1,399.00
10	2/26/2018	3/4/2018	$1,234		411.33	822.67
11	3/26/2018	4/1/2018	$5,678			4,731.67	946.3333
Sheet9

Cell Formulas
Range	Formula
D2	=IFERROR(IF(AND(MONTH($A2)=MONTH(D$1),MONTH($B2)=MONTH(D$1)),$C2,IF(AND(MONTH($A2)=MONTH(D$1),MONTH($B2)=MONTH(E$1)),$C2/($B2-$A2)*(EOMONTH($A2,0)-$A2),IF(AND(MONTH($A2)=MONTH(C$1),MONTH($B2)=MONTH(D$1)),$C2/($B2-$A2)*DAY($B2),""))),"")

Formula copied down and across for all months.

 

[Tetra201]

@ mseferog:

What is your expected split for

1/29/2018 2/4/2018 02FEB18 $1,587 ?? ??