Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Excel formula to find same date as last year

    Hi,

    I cannot find a formula anywhere that will give me the excel serial number for the same date as last year (ie 7/1/2015 I want to return serial for 7/1/2014). I will be using this number for a lookup to pull a value from the previous year's spreadsheet. Any help on this would be appreciated as I have hit the research wall. Thanks.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    526
    Thanks
    1
    Thanked 45 Times in 43 Posts
    Let's assume that the date you are starting from is in cell A1.

    If you are happy to ignore the effects of leap years then you can use the simple formula =A1-365

    If you need to be exact, you can use =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

    Silly me ! Edited as per the posts below so as not to leave a misleading reply on this Forum.
    Last edited by MartinM; 2014-07-22 at 11:16.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    balla506 (2014-07-22)

  4. #3
    2 Star Lounger
    Join Date
    May 2010
    Location
    Philadelphia, PA, USA
    Posts
    133
    Thanks
    0
    Thanked 19 Times in 18 Posts
    OP wanted to go BACK one year. Martin M's formula goes AHEAD one year.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Same principle as forward, just use -1 instead of +1:
    =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

    Steve

  6. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Worked great. I actually had this at one time but was just looking at it wrong. Thanks again.

Tags for this Thread

Posting Permissions

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