Calculating Dates

[aidanlukecole]

If I have a date in column A, and I want column B to show a date 2 years and 9 months after the date in column A, what formula do I use?

ie: Column A has the date 6/15/08. I want Column B to automatically calculate the date 2 years and 9 months after 6/15/08 (which would be 3/15/2011.

 

[HalfAce]

Hi aidanlukecole, welcome to the board.
A couple ways to do this. (Both assume your date is in A1...)
1) Quick & dirty:
Since we know that 2 years & 9 months equals 1003 days (most years) you might get away with just using:

=A1+1003

2) More robust, and will take leap years into account...

=DATE(YEAR(A1)+2,MONTH(A1)+9,DAY(A1))

Hope it helps.

 

[aidanlukecole]

Thank you so much! Perfect. One additional question: I can't remember how you apply a formula to an entire column. In other words, lets say Column A includes 10 rows worth of dates. I want to apply that formula to all corresponding 10 rows of Column B. Can you tell me how to do that . . . Many thanks!

 

[barry houdini]

Another option would be add 33 months using EDATE, e.g.

=EDATE(A1,33)

for dates at the end of the month that can give different results than

=DATE(YEAR(A1)+2,MONTH(A1)+9,DAY(A1))

e.g. if A1 = 31-May-2011 then EDATE formula gives 28-Feb-2014 but the other one gives 3-Mar-2014

 

[HalfAce]

Sure. (and you're going to be sorry you asked .... but)
Copy & paste the posted formula into cell B1.
Copy that cell and highlight the entire range you want to paste the formula into and just paste. (The formula is using relative cell addressing so the formula in row 2 will update itself to be:

=DATE(YEAR(A[COLOR=Red]2[/COLOR])+2,MONTH(A[COLOR=Red]2[/COLOR])+9,DAY(A[COLOR=Red]2[/COLOR]))

the formula in row 3 should be:

=DATE(YEAR(A[COLOR=Red]3[/COLOR])+2,MONTH(A[COLOR=Red]3[/COLOR])+9,DAY(A[COLOR=Red]3[/COLOR]))

and so on.

Is that what you meant?

 

[aidanlukecole]

That's exactly what I meant. Thanks again!

 

[aidanlukecole]

New question: What if I have 10 rows of dates in column A and 10 rows of dates in column B. I need a formula for column C that will take the earlier of the two dates in a row (either the one in column A or the one in column B), and then calculate a date that is 2 years 9 months from that earlier date. For example, A1 has the date 7/15/02 and B1 has 4/20/01. Column C should be 01/20/04.

 

[barry houdini]

If you use the formula suggested by Halface you can modify that so that in place of all the A1s in the formula you use MIN(A1,B1), i.e.

=DATE(YEAR(MIN(A1,B1))+2,MONTH(MIN(A1,B1))+9,DAY(MIN(A1,B1)))

or using EDATE as per my suggestion

=EDATE(MIN(A1,B1),33)

 

[aidanlukecole]

Thank you SO much! This is the best website EVER!