Month's accumulated Numbers

Salamullah

Board Regular
Joined
Mar 28, 2011
Messages
221
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
[TABLE="width: 261"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Month Number[/TD]
[TD]Cumulative[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD="align: right"]1 [/TD]
[TD="align: right"]31 [/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD="align: right"]2 [/TD]
[TD="align: right"]59 [/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD="align: right"]3 [/TD]
[TD="align: right"]90 [/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]4 [/TD]
[TD="align: right"]120 [/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]5 [/TD]
[TD="align: right"]151 [/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD="align: right"]6 [/TD]
[TD="align: right"]181 [/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD="align: right"]7 [/TD]
[TD="align: right"]212 [/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD="align: right"]8 [/TD]
[TD="align: right"]243 [/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD="align: right"]9 [/TD]
[TD="align: right"]273 [/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]10 [/TD]
[TD="align: right"]304 [/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]11 [/TD]
[TD="align: right"]334 [/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD="align: right"]12 [/TD]
[TD="align: right"]365 [/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi,

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


Book1
ABCDEF
1Jan90MonthMonth NumberCumulative
2Feb90Jan131
3Mar90Feb259
4AprMar390
5MayApr4120
6JunMay5151
7JulJun6181
8AugJul7212
9SepAug8243
10OctSep9273
11NovOct10304
12DecNov11334
13Dec12365
Sheet35
Cell Formulas
RangeFormula
B1=IF(MONTH(TODAY())>=ROW(A1),VLOOKUP(MONTH(TODAY()),E$2:F$13,2,0),"")
 
Upvote 0
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...
Code:
[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]
 
Last edited:
Upvote 0
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
ABCDEF
1Jan9090MonthMonth NumberCumulative
2Feb9090Jan131
3Mar9090Feb259
4Apr00Mar390
5May00Apr4120
6Jun00May5151
7Jul00Jun6181
8Aug00Jul7212
9Sep00Aug8243
10Oct00Sep9273
11Nov00Oct10304
12Dec00Nov11334
13No TableDec12365
Sheet35
Cell Formulas
RangeFormula
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)
 
Upvote 0
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 !
 
Upvote 0
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.
 
Upvote 0
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...
Code:
[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]
 
Last edited:
Upvote 0
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.
 
Upvote 0
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))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top