Broken formula

Question

my colleague has a spreadsheet set up to calculate a future date and it looks as though she's been adding to the formula as time passes. Each account has a registration date and every 6 months they recalculate. For example, if they registered on 4/5/2009, they would recalculate on 10/5/2009, then 4/5/2010, then 10/5/2010, and so on and so forth. The spreadsheet is designed to look at thier registration date and then compute thier next recalculation date.

Here is the formula that we have now:

=IF(ISBLANK(U45),"",IF(DATE(YEAR(U45),MONTH(U45),DAY(U45))>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),DATE(YEAR(U45),MONTH(U45)+6,DAY(U45)),IF(DATE(YEAR(U45),MONTH(U45),DAY(AU45))>=DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())),DATE(YEAR(U45),MONTH(U45)+12,DAY(U45)),IF(DATE(YEAR(U45),MONTH(U45),DAY(AU45))>=DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())),DATE(YEAR(U45),MONTH(U45)+18,DAY(U45)),IF(DATE(YEAR(U45),MONTH(U45),DAY(U45))>=DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())),DATE(YEAR(U45),MONTH(U45)+24,DAY(U45)),DATE(YEAR(U45),MONTH(U45)+30,DAY(U45)))))))

It seems as though we are adding months by increments of 6... I need to know how to add in +36 months, because some of our dates are over 30 months old. Does this make sense???? OR if you can think of an easier way to do this, please let me know!!

Answer

You can simplify matters a lot. First of all, DATE(YEAR(U45),MONTH(U45),DAY(U45)) is identical to just U45. However, it'd be easier to set up a table like this, for example: In F1:F10, put 6,12,18,...,60 In E1 enter =DATE(YEAR(TODAY()),MONTH(TODAY())-F1,DAY(TODAY())) and fill to E10. In G1 enter =DATE(YEAR($U$45),MONTH($U$45)+F1,DAY($U$45)) and fill to G10. In H1 enter =$U$45>E1 and fill to H10. Now your above formula can be reduced to: =IF(ISBLANK(U45),"",INDEX(G:G,MATCH(TRUE,H:H,0)))