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
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Budapest
    Posts
    749
    Thanks
    30
    Thanked 77 Times in 68 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
    169
    Thanks
    1
    Thanked 20 Times in 19 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,225
    Thanks
    14
    Thanked 342 Times in 335 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
  •