Embedded IF function using Dates

[bluebell123]

Hi,

I was wondering if somebody could help.

I'm trying to identify apply an embedded IF formula to identify whether a date in a one cell is before or after another cell.

The two date cells my formula is referring to are loss date and renewal date.

The IF function reads:

• if the loss date is after the renewal date - return the renewal date
• if the loss date = the renewal date - return the renewal date
• if the loss date is before the renewal date - return the renewal minus 366

Loss date is in column U and the Renewal Date is in column CA. Both columns are formatted as a short date.
So the formula I have entered is:
=IF(U1>CA1, C1, IF(U1=C1, C1, IF(U1<CA1, CA1-365)))

Applying the above formula results in the last embedded formula to be triggered even if the logical test does not apply.

Any help would be greatly appreciated.

Thanks.

 

[Joe4]

Welcome to the Board!

Since like you could just do this:

=IF(U1 < CA1,CA1-366,CA1)

 

[Scott T]

try

=IF(U1>=CA1,CA1,CA1-366)

 

[bluebell123]

Thank-you both for your reply - I realised the formulae you both suggested is alot simpler however for some reason when I go to do a manual check, the if function does not return the correct value. As you can imagine this is quite frustating as the formula makes complete sense in my head

 

[Joe4]

however for some reason when I go to do a manual check, the if function does not return the correct value.

Let us know the values you have in U1 and CA1, the result you are getting, and your expected result.

Also, make sure that the values in U1 and CA1 are entered as dates, and not as text!