Help with DateValue function.

[Pank]

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.

 

[macropod]

Hi Pank,

Try:
=IF($A$4="spring",DATE(YEAR(TODAY()-1),12,31),FALSE)

Cheers

 

[Pank]

Hi macropod,

Firstly, many thanks for the promt response.

Having put the formula in it gives a date of 31/12/2007 and not 31/12/2006
as I want.

ANy ideas what needs to be changed.

Pank

Hi Pank,

Try:
=IF($A$4="spring",DATE(YEAR(TODAY()-1),12,31),FALSE)

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

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.

 

[JE McGimpsey]

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.

 

[JE McGimpsey]

Parens in the wrong place. Could use


=IF($A$4="spring",DATE(YEAR(TODAY())-1,12,31),FALSE)

or use the formula in my other reply.

 

[Pank]

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?

 

[macropod]

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

 

[Pank]

Macropod and JE McGimpsey,

Worked a treat, many thanks for your assistance, much appreciated.

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]
-------------------------

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?