Solved

Display last 36 months based on current month/ year

Posted on 2013-06-12
7
313 Views
Last Modified: 2013-06-26
Hi,

I have string field where I get current month as:

06.2013

I want to make matrix based on this current month for last 36 months as:

02.2011     03.2011   04.2011  ... ... .. .. ... . . . . 02.2013   03.2013  04.2013  05.2013  06.2013

Please provide a formula to achieve it.

I don't want to use off-set function as not supported by Dashboards.

Thanks.
0
Comment
Question by:NickHoward
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39241961
Enter this formula into A1...

=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-36+COLUMN(),1),"mm.yyyy")

Now, copy that across through AJ1
0
 

Author Comment

by:NickHoward
ID: 39242045
Hi.

Thanks but I don't want to use system time to caculate current month. This month/ year has to be calculated from a cell where month/ year is displatyed as string 06.2013

Hope I get revised formula soon.

Nick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39242064
>>from a cell where month/ year is displatyed [sic] as string 06.2013

Is that the actual value entered in the cell, or just the displayed number format?

If the value is itself entered as a date, then just change my formula for A1 to:

=TEXT(DATE(YEAR($A$5),MONTH($A$5)-36+COLUMN(),1),"mm.yyyy")

Changed $A$5 to the real cell holding the value.

If it is actually entered as text...

=TEXT(DATE(YEAR(SUBSTITUTE($A$5,".","/1/")),MONTH(SUBSTITUTE($A$5,".","/1/"))-36+COLUMN(),1),"mm.yyyy")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NickHoward
ID: 39242134
I have cell A5 holding text value 06.2013

When I used formula:

=TEXT(DATE(YEAR(SUBSTITUTE($A$5,".","/1/")),MONTH(SUBSTITUTE($A$5,".","/1/"))-36+COLUMN(),1),"mm.yyyy")

Excel said error in formula and it highlights SUBSTITUTE. No further error information.

Please advise
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39242170
The attached spreadsheet has working examples of both of my approaches described in http:#a39242064

Q-28155292.xls
0
 

Author Closing Comment

by:NickHoward
ID: 39242235
Worked like wonder.

Thanks a lot.
0
 

Author Comment

by:NickHoward
ID: 39279347
Hi  matthewspatrick.

Just a quick fix otherwise I open new case.

What about if I only have "2013" (current year text without month like above). How I can off-set to 2012, 2011, 2010??

Thanks.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question