Sorry,

The '-1' was in the wrong place. As JE noted, the formula should have been:
=IF($A$4="spring",DATE(YEAR(TODAY())-1,12,31),FALSE)

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Pank" wrote in message ...
Hi JE McGimpsey,

Thank you for you help, you formula, works as expected retuning a date of
31/12/06. Unfortunately, I now have to cater for other date and not just
31/12/06.

Therefore macropod formula would be perfect but for the year being returned
as the current year and not the previous year.

Any ideas how to change macropod formula so that it returns the previous year?


"JE McGimpsey" wrote:

One way:

=IF($A$4="spring",DATE(YEAR(TODAY()),1,0),FALSE)

Note that the 0th day of the month, to XL, is the last day of the
previous month.

In article ,
Pank wrote:

I have the following formula in a cell:-

=IF($A$4="spring",DATEVALUE("31-12-06€�),FALSE), which works fine.

However to make the formula more flexible, I don€„¢t want to hard code the
year. Basically the year part should be current year minus 1 (i.e. 2007-1).
Please note that the date in the DateValue field will always be 31st December
current year -1

Can someone advise me how I can do that?

Thank you.