Need a formula to calculate a date based on 2 other cells that have dates in them

[Break977]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]A3
[/TD]
[TD]A4
[/TD]
[/TR]
[TR]
[TD]01 Mar 2006
[/TD]
[TD]12 May 2011
[/TD]
[TD]01 May 2020
[/TD]
[TD]240/108
[/TD]
[/TR]
</tbody>[/TABLE]


So I need a formula in cell A3 that will produce a date based on the following:

A2-A1: if the answer (in months) is greater than 108, then cell A3 should be a date that is cell A2+108
A2-A1: if the answer (in months) is less than 108, then cell A3 should be a date that is cell A1+240

Let me know if you require any further information, hope I asked the question that is easily understandable.

 

[63falcondude]

=IF(DATEDIF(A1,A2,"M")>108,EDATE(A2,108),EDATE(A1,240))

This formula check to see if the difference in months between A1 and A2 is greater than 108.
If the difference is greater than 108, the formula adds 108 months to the date in A2.
If the difference is less than or equal to 108 months, the formula adds 240 months to the date in A1.

The answer to your sample would then be 01 Mar 2026.

 

[Break977]

=IF(DATEDIF(A1,A2,"M")>108,EDATE(A2,108),EDATE(A1,240))

This formula check to see if the difference in months between A1 and A2 is greater than 108.
If the difference is greater than 108, the formula adds 108 months to the date in A2.
If the difference is less than or equal to 108 months, the formula adds 240 months to the date in A1.

The answer to your sample would then be 01 Mar 2026.

So far, works perfect, just one last thing, how do I make the date (that is the outcome of this formula) always be the first of the month, ie. 01 Mar 2012 or 01 May 2023???

Thanks again.

 

[63falcondude]

So far, works perfect, just one last thing, how do I make the date (that is the outcome of this formula) always be the first of the month, ie. 01 Mar 2012 or 01 May 2023???

Thanks again.

Try

=IF(DATEDIF(A1,A2,"M")>108,EDATE(A2,108)-DAY(EDATE(A2,108))+1,EDATE(A1,240)-DAY(EDATE(A1,240))+1)

 

[Break977]

Try

=IF(DATEDIF(A1,A2,"M")>108,EDATE(A2,108)-DAY(EDATE(A2,108))+1,EDATE(A1,240)-DAY(EDATE(A1,240))+1)

Looks good, thanks again for your help.

 

[63falcondude]

Looks good, thanks again for your help.

Glad to help.