Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula for adding time to a date based on a number entered into a cell

    Wow, what a cryptic title!

    Here is what I have. In cell A1 a number is entered 1, 2 or 3 for the number of years that a document is to be reviewed. In cell B1 a date of last review is entered. in cell C1 I need to display a future date based on A1+B1, but wait there is more. I need this to give me a date which is really 2 months earlier than would be if I simply added 1, 2, or 3 years to the date in B1.

    Example, Frequency is 2 (A1), Last Review date is 07/19/2012 (B1), Next Review date should return 05/19/2014 (C1). So, instead of 12 months or 24 months or 36 months it will add 10, 22 or 34.

    Thanks,

    Bret

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =DATE(YEAR(B1)+A1,MONTH(B1)-2,DAY(B1))
    should do it, though it depends what you want to happen if say the date were 30 April 2012 - which day would you want to go back to in Feb?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, this is probably more complicated than it should be but I would think the end of the month. I am actually going to suggest that the person that uses this spreadsheet format the review date to month/year. So, I would think just subtracting 2 months off of the number of years should suffice.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    As an alternative you could use the formula =B1+A1*365-60
    The answer could be off by a day or two, but would also be close enough for HR.

Posting Permissions

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