Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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. #1
    New Member kripper's Avatar
    Join Date
    Dec 2013
    Location
    EVERYWHERE
    Posts
    29

    Unhappy 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. #2
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,555

    Default 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)))
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  3. #3
    New Member kripper's Avatar
    Join Date
    Dec 2013
    Location
    EVERYWHERE
    Posts
    29

    Default 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. #4
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,555

    Default 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

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


    Excel tables to the web >> Excel Jeanie HTML 4
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,482

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

    Quote Originally Posted by kripper View Post
    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,354

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

    Is it just

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

    what you are after?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member kripper's Avatar
    Join Date
    Dec 2013
    Location
    EVERYWHERE
    Posts
    29

    Default 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. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,482

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

    Quote Originally Posted by kripper View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  9. #9
    New Member kripper's Avatar
    Join Date
    Dec 2013
    Location
    EVERYWHERE
    Posts
    29

    Default 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. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,482

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

    Quote Originally Posted by kripper View Post
    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com