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

1. ## 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. =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?

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