Subtract 1 year from date based on month "January"

[kripper]

I am trying to figure out how I can subtract a year when I formulate based on if the current month is January.

Such as Current month = January 2014, I would require the cell to populate December 2013, any other month would return the current year value.

I have currently tried { =DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)) } whereas cell A3 uses NOW() function formatted to MMMM, but it changes for every month.

Hoping for some help.

Thanks
K.

 

[tweedle]

maybe this works for you

=IF(MONTH(A3)=1,DATE(YEAR(A3)-1,MONTH(A3)-1,DAY(A3)),DATE(YEAR(A3),MONTH(A3),DAY(A3)))

 

[kripper]

It works, thanks. Now on a side note, how do I have it display the current year if it is not January? Such as in February it would display 2014?

 

[tweedle]

It already does the year for the other months if the month of A3 <> 1
Also had a type-err subtracting month when shouldn't have.
=IF(MONTH(A3)=1,DATE(YEAR(A3)-1,12,DAY(A3)),DATE(YEAR(A3),MONTH(A3),DAY(A3)))

	A	B
3	1/4/2014 1:42	12/4/2013
4	2/4/2014 0:00	2/4/2014
5	3/4/2014 0:00	3/4/2014
6	4/4/2014 0:00	4/4/2014
7	5/4/2014 0:00	5/4/2014
8	6/4/2014 0:00	6/4/2014
9	7/4/2014 0:00	7/4/2014
10	8/4/2014 0:00	8/4/2014
11	9/4/2014 0:00	9/4/2014
12	10/4/2014 0:00	10/4/2014
13	11/4/2014 0:00	11/4/2014
14	12/4/2014 0:00	12/4/2014

<colgroup><col style="width: 30px;"><col style="width: 97px;"><col style="width: 76px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A3 =NOW() B3 =IF(MONTH(A3)=1,DATE(YEAR(A3)-1,12,DAY(A3)),DATE(YEAR(A3),MONTH(A3),DAY(A3))) B4 =IF(MONTH(A4)=1,DATE(YEAR(A4)-1,12,DAY(A4)),DATE(YEAR(A4),MONTH(A4),DAY(A4))) B5 =IF(MONTH(A5)=1,DATE(YEAR(A5)-1,12,DAY(A5)),DATE(YEAR(A5),MONTH(A5),DAY(A5))) B6 =IF(MONTH(A6)=1,DATE(YEAR(A6)-1,12,DAY(A6)),DATE(YEAR(A6),MONTH(A6),DAY(A6))) B7 =IF(MONTH(A7)=1,DATE(YEAR(A7)-1,12,DAY(A7)),DATE(YEAR(A7),MONTH(A7),DAY(A7))) B8 =IF(MONTH(A8)=1,DATE(YEAR(A8)-1,12,DAY(A8)),DATE(YEAR(A8),MONTH(A8),DAY(A8))) B9 =IF(MONTH(A9)=1,DATE(YEAR(A9)-1,12,DAY(A9)),DATE(YEAR(A9),MONTH(A9),DAY(A9))) B10 =IF(MONTH(A10)=1,DATE(YEAR(A10)-1,12,DAY(A10)),DATE(YEAR(A10),MONTH(A10),DAY(A10))) B11 =IF(MONTH(A11)=1,DATE(YEAR(A11)-1,12,DAY(A11)),DATE(YEAR(A11),MONTH(A11),DAY(A11))) B12 =IF(MONTH(A12)=1,DATE(YEAR(A12)-1,12,DAY(A12)),DATE(YEAR(A12),MONTH(A12),DAY(A12))) B13 =IF(MONTH(A13)=1,DATE(YEAR(A13)-1,12,DAY(A13)),DATE(YEAR(A13),MONTH(A13),DAY(A13))) B14 =IF(MONTH(A14)=1,DATE(YEAR(A14)-1,12,DAY(A14)),DATE(YEAR(A14),MONTH(A14),DAY(A14))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Rick Rothstein]

It works, thanks.

Does it really? If the date in A3 is 1/1/2014, that formula returns 12/1/2012 for me (notice the year is 2012, not 2013). Also, I thought if the date were March 1, 2014 that you wanted February 1, 2014 returned to you (that formula just returns the date in A3... no change)? I think the formula kripper might have been after is this one...

=DATE(YEAR(A3),MONTH(A3)-1,DAY(A3))

Does it return the dates you want? If so, then I have a question for you... what values can the day number be for your dates? If it can be any day number in the month, then I do not think the above formula would do what you want. Try it out for March 31, 2013 and see what it returns. If that is not what you want, then what date should March 31, 2013 return?

 

[Aladin Akyurek]

Is it just

=EOMONTH(TODAY(),-2)+1

what you are after?

 

[kripper]

I am trying the make current sheet show the previous month and current year, so I am always one month behind, but when I get to the beginning of the new year in January, I need it to represent December of the following year, such as:

Current Date: Displayed Date:
January 2014 December 2013
February 2014 January 2014
March 2014 February 2014
April 2014 March 2014
May 2014 April 2014
June 2014 May 2014
July 2014 June 2014
August 2014 July 2014
September 2014 August 2014
October 2014 September 2014
November 2014 October 2014
December 2014 November 2014
January 2015 December 2014

And so on......

 

[Rick Rothstein]

I am trying the make current sheet show the previous month and current year, so I am always one month behind, but when I get to the beginning of the new year in January, I need it to represent December of the following year, such as:

Current Date: Displayed Date:
January 2014 December 2013
February 2014 January 2014
March 2014 February 2014
April 2014 March 2014
May 2014 April 2014
June 2014 May 2014
July 2014 June 2014
August 2014 July 2014
September 2014 August 2014
October 2014 September 2014
November 2014 October 2014
December 2014 November 2014
January 2015 December 2014

And so on......

Okay, fine, but you did not answer any of my questions in the last paragraph of Message #5. All of those question were important (which is why I asked them) in order to decide how to solve your problem.

 

[kripper]

Sorry.....

The day number can be any, I am not using that string, however for March 31, 2013 and 2014, I return a month of March, it does not return the required February month.

I have tried many formulas, and I can not seem to find one that will work for my purpose. Will it make much difference if I use the day variable, I just simply don't have to display that in my sheet!

Hope I answered the questions properly in order for you to assist.

Thanks

 

[Rick Rothstein]

The day number can be any, I am not using that string, however for March 31, 2013 and 2014, I return a month of March, it does not return the required February month.

You missed the point of my question about March 31, 2014... you cannot simply say you want the month before as there is no February 31 in 2014. So what date in February did you want March 31st to go to... the last day in February? If you say "yes" then I guess you will also want March 29th and March 30th to go to the last day in February as well, since those day numbers do not exist in February either, correct?