Formula to show QTD & YTD

[Vasweetheart05]

I have a drop down list in the format of months like the following: 2016-05

The user can select any month in the current year. Then the QTD (current months in the quarter) and YTD should automatically populate.

For example:

QTD:::: if 2016-05 is selected then qtd should automatically populate 2016-04 in C1 and 2016-05 in c2. C3 will be blank Until 2016-06 is selected from the drop down.

YTD:::: if 2016-05 is selected, then all months January through may with be shown in b1 through b6. In b7 through b12, the columns will be blank until another month is selected.


what formula can I use to automatically populate the current months in a quarter and a year?

 

[CyrusTheVirus]

I'm not sure about the quarter formula, but below is the YTD formula. Going to bed now, i'll check back tomorrow to see if anyone posted a QTD formula.

B1:

=IF(ROWS($B$1:B1)<=MONTH($A$2),TEXT(DATE(YEAR($A$2),ROWS($B$1:B1),1),"mmmm"),"")

[TABLE="width: 137"]
<tbody>[TR]
[TD]YTD[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2016[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]May[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Assuming you drop-down list is in cell A1, try these formulas:

Cell B1 (and copy down)

=IF(ROWS($B$1:$B1)<=MONTH(DATEVALUE($A$1&"-01")),TEXT(ROWS($B$1:$B1)&"-01","mmmm"),"")

Cell C1 (and copy down)

=IF(ROWS($C$1:$C1)<=MONTH(DATEVALUE($A$1&"-01"))-(ROUNDUP(MONTH(DATEVALUE($A$1&"-01"))/3,0)-1)*3,LEFT($A$1,5)&TEXT(ROUNDUP(MONTH(DATEVALUE($A$1&"-01"))/3,0)*3-3+ROWS($C$1:$C1),"00"),"")

 

[Vasweetheart05]

The only issue here is that it isn't going to be a specific date entered. It would only be a drop down value of month in the format of 2016-07.

 

[Tetra201]

Have you tried my formulas?

 

[Vasweetheart05]

The output would also have to be in the same format of 2016-01 through 2016-12. I don't believe datevalue functiOn would work with this or am I incorrect?

 

[Tetra201]

The output would also have to be in the same format of 2016-01 through 2016-12.

That's not what you originally posted.

then all months January through may with be shown in b1 through b6.

 

[Tetra201]

Here is an updated formula for cell B1:

=IF(ROWS($B$1:$B1)<=MONTH(DATEVALUE($A$1&"-01")),LEFT($A$1,5)&TEXT(ROWS($B$1:$B1),"00"),"")

 

[Vasweetheart05]

This worked!!!!!! Thank you!

 

[Tetra201]

Glad to be of a help.