Excel:Next year period

0
votes

How to get next year period based on current month and year, for example:

  • Jan 2014 - Dec 2014
  • Feb 2014 - Jan 2015
  • Mar 2014 - Feb 2015
  • Apr 2014 - Mar 2015
  • May 2014 - Apr 2015
  • Jun 2014 - May 2015
  • Jul 2014 - Jun 2015
  • Aug 2014 - Jul 2015
  • Sep 2014 - Aug 2015
  • Oct 2014 - Sep 2015
  • Nov 2014 - Oct 2015
  • Dec 2014 - Nov 2015

Next period

  • Jan 2015 - Dec 2015
  • Feb 2015 - Jan 2016

etc.

I have tried with the following formula:

=UPPER(TEXT(NOW();"MMM")) &" "& TEXT(NOW();"YY")-1

It works fine for Jan 2014 but can't figure out how to get Dec 2014; Feb 2014 - Jan 2015 and so on?

2
excelexcel-formula
Btw, I'm new bee within Excel :) – NB_Excel
Yes, this is the year-loop. The year period should start from previous month and one year back. – NB_Excel
Yes, this is the year-loop. The year period should start from previous month and one year back. Because the current month is January 2015 the year period is Jan 2014 - Dec 2014 and when February arrives then the period will start with January 2015 and one year back which is February 2014. As listed above :) – NB_Excel
Jan 14 - Dec 14; Feb 14 - Jan 15 = January 2014 - December 20014; February 2014 - January 2015. Sorry my bad, I'm new here. Is't possible to edit the question? :) – NB_Excel
The 14 refer to year 2014 and 15 refer to year 2015. Not days :) – NB_Excel

2 Answers

0
votes

I think you need the EOMonth formula.

=EOMONTH(NOW(),-13) +1 and =EOMONTH(NOW(),-2) +1 should give give you JAN 2014 to DEC 2014

from the MS Excel documentation

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

To get the text formatting you are after, I would suggest that you stick with formatting the cell/column as @Makyen has suggested. Having said that this is the formula that you can use to format the text.

=UPPER(TEXT(EOMONTH(NOW(),-13) +1, "MMM YY"))

0
votes

Assuming that the date (as a date serial number) for which you desire to find the year period is in cell A1, the following should provide the next year period starting from that day:

=EOMONTH(A1,11) +DAY(A1) -1

Examples:

   Input      Output
 1/18/2014   1/17/2015
 2/18/2014   2/17/2015
 3/18/2014   3/17/2015
 4/18/2014   4/17/2015
 5/18/2014   5/17/2015
 6/18/2014   6/17/2015
 7/18/2014   7/17/2015
 8/18/2014   8/17/2015
 9/18/2014   9/17/2015
10/18/2014  10/17/2015
11/18/2014  11/17/2015
12/18/2014  12/17/2015
 1/18/2015   1/17/2016
 2/18/2015   2/17/2016
 3/18/2015   3/17/2016
 4/18/2015   4/17/2016
 5/18/2015   5/17/2016
 6/18/2015   6/17/2016
 7/18/2015   7/17/2016
 8/18/2015   8/17/2016
 9/18/2015   9/17/2016
10/18/2015  10/17/2016
11/18/2015  11/17/2016
12/18/2015  12/17/2016
 1/18/2016   1/17/2017

If you want the year period to start from the current day:

=EOMONTH(NOW(),11) + DAY(NOW()) -1

If you want the year period to start from the first day of the current month:

=EOMONTH(EOMONTH(NOW(),-1) + 1,11)

or

=EOMONTH(NOW() - DAY(NOW()) + 1,11)

The EOMONTH() function:

EOMONTH(start_date,months)

Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.