Streamlining a date function

[polyseal]

hi there,

this is the date function i wrote

=IF(DATE(YEAR(D568)+1,MONTH(D568),DAY(D568))>TODAY()+30,DATE(Year(D568)+1,MONTH(D568),DAY(D568)),IF(AND(DATE(YEAR(D568),MONTH(D568)+11,DAY(D568))<=TODAY(),DATE(YEAR(D568)+1,MONTH(D568),DAY(D568))>TODAY()),"SOON",IF(DATE(YEAR(D568)+1,MONTH(D568),DAY(D568))<TODAY(),"URGENT")))


its usig a date stored in column D, to see if it needs to be retested. if it is over a year since last test, displays urgent, if it is 1 year to 11 months past previous test date, display soon, if it is less than 11 months since last test date, just display the date.

the function works, but just wondering if there is a nicer way to do it?

been using excel for about a week now, and pressing f1 every hour or so google helps too, but just cant seem to get my head around some of the functions..

this is what happens when a hardware/networking tech is told to create excel stuff :D

Cheers.

Chris.

[daddylonglegs]

You could try using DATEDIF for the date difference i.e.

=CHOOSE(MATCH(DATEDIF(D568,TODAY(),"m"),{0,11,12}),DATE(YEAR(D568)+1,MONTH(D568),DAY(D568)),"Soon", "Urgent")

[polyseal]

thanks heaps.

looks nice and complex. should impress bosses :P

Chris.