Month's accumulated Numbers

[Salamullah]

I want formula to bring cumulative days number based on the month, need formula please.

Suppose month is Mar I want from Jan to Mar 90 and remaining months Zero
If month is Jun the figure from Jan to Jun should be 181 and remaining months Zero, so on so forth.

Jan 90
Feb 90
Mar 90
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0

Month	Month Number	Cumulative
Jan	1	31
Feb	2	59
Mar	3	90
Apr	4	120
May	5	151
Jun	6	181
Jul	7	212
Aug	8	243
Sep	9	273
Oct	10	304
Nov	11	334
Dec	12	365

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[jtakw]

Hi,

I'm assuming the Months in your Tables are Text and Not real Date values:

Book1
	A	B	C	D	E	F
1	Jan	90		Month	Month Number	Cumulative
2	Feb	90		Jan	1	31
3	Mar	90		Feb	2	59
4	Apr			Mar	3	90
5	May			Apr	4	120
6	Jun			May	5	151
7	Jul			Jun	6	181
8	Aug			Jul	7	212
9	Sep			Aug	8	243
10	Oct			Sep	9	273
11	Nov			Oct	10	304
12	Dec			Nov	11	334
13				Dec	12	365
Sheet35

Cell Formulas
Range	Formula
B1	=IF(MONTH(TODAY())>=ROW(A1),VLOOKUP(MONTH(TODAY()),E$2:F$13,2,0),"")

 

[Rick Rothstein]

I want formula to bring cumulative days number based on the month, need formula please.

Suppose month is Mar I want from Jan to Mar 90 and remaining months Zero
If month is Jun the figure from Jan to Jun should be 181 and remaining months Zero, so on so forth.

Jan 90
Feb 90
Mar 90
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0

Assuming the month names are Text values starting in cell A1, put this stand-alone formula (it does not require a lookup table) in cell B1 and copy it down...

[table="width: 500"]
[tr]
	[td]=IF(0+TEXT(1&A1,"m")<=MONTH(TODAY()),INDEX({31;59;90;120;151;181;212;243;273;304;334;365},MONTH(TODAY())),0)[/td]
[/tr]
[/table]

 

[jtakw]

Oops, forgot you wanted 0, not Blank, when for future months, here's the corrected version (just changed the "" to 0) in B1, C1 is another option without the Table:

Book1
	A	B	C	D	E	F
1	Jan	90	90	Month	Month Number	Cumulative
2	Feb	90	90	Jan	1	31
3	Mar	90	90	Feb	2	59
4	Apr	0	0	Mar	3	90
5	May	0	0	Apr	4	120
6	Jun	0	0	May	5	151
7	Jul	0	0	Jun	6	181
8	Aug	0	0	Jul	7	212
9	Sep	0	0	Aug	8	243
10	Oct	0	0	Sep	9	273
11	Nov	0	0	Oct	10	304
12	Dec	0	0	Nov	11	334
13			No Table	Dec	12	365
Sheet35

Cell Formulas
Range	Formula
B1	=IF(MONTH(TODAY())>=ROW(A1),VLOOKUP(MONTH(TODAY()),E$2:F$13,2,0),0)
C1	=IF(MONTH(TODAY())>=ROW(A1),CHOOSE(MONTH(TODAY()),31,59,90,120,151,181,212,243,273,304,334,365),0)

 

[Salamullah]

Many thanks its working, but I am getting 90 when I am putting last month like Feb, I want flexibility.

The data contained plan numbers from Jan to Dec and I want to compare Actuals with plan month, currently for example Feb month and later any month.

Hope its clear !

 

[Salamullah]

I think I have not explained my requirement correctly, let me again.

I want formula in column B based on cell A1


A1 May

Jan 151
Feb 151
Mar 151
Apr 151
May 151
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0

for example in A1 it Jan than in B2 its required 31 and from B3 to B13 ZERO.

 

[Rick Rothstein]

I want formula in column B based on cell A1

A1 May

Jan 151
Feb 151
Mar 151
Apr 151
May 151
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0

Put this formula in cell B2 and copy it down to cell B13...

[table="width: 500"]
[tr]
	[td]=IF(0+TEXT(1&A2,"m")<=0+TEXT(1&A$1,"m"),INDEX({31;59;90;120;151;181;212;243;273;304;334;365},TEXT(1&A$1,"m")),0)[/td]
[/tr]
[/table]

 

[Salamullah]

Hi Rick, perfect !!

Many thanks you saved my time a lot.

 

[Rick Rothstein]

Hi Rick, perfect !!

Many thanks you saved my time a lot.

You are quite welcome. By the way, I forgot to mention that the formula I posted in Message #8 is, like the first formula I attempted, completely stand-alone and does not require the table of cumulative days per month that you posted in your original message.

 

[Tetra201]

Late to the party, but here is a shorter formula that will work for leap years, too:

=(EDATE(1&A$1,1)-(YEAR(1&A$1)&"-1-1"))*(MONTH(1&A2)<=MONTH(1&A$1))