Subtract 1 year from date based on month "January"

This is a discussion on Subtract 1 year from date based on month "January" within the Excel Questions forums, part of the Question Forums category; I am trying to figure out how I can subtract a year when I formulate based on if the current ...

1. Subtract 1 year from date based on month "January"

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.

2. Re: Subtract 1 year from date based on month "January"

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)))

3. Re: Subtract 1 year from date based on month "January"

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?

4. Re: Subtract 1 year from date based on month "January"

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

 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)))

Excel tables to the web >> Excel Jeanie HTML 4

5. Re: Subtract 1 year from date based on month "January"

Originally Posted by kripper
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?

6. Re: Subtract 1 year from date based on month "January"

Is it just

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

what you are after?

7. Re: Subtract 1 year from date based on month "January"

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......

8. Re: Subtract 1 year from date based on month "January"

Originally Posted by 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......
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.

9. Re: Subtract 1 year from date based on month "January"

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

10. Re: Subtract 1 year from date based on month "January"

Originally Posted by kripper
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?

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•