Help with DateValue function.
 

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 dont 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.

Help with DateValue function.
 

Hi Pank,

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

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

"Pank" wrote in message ...
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 dont 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.

Help with DateValue function.
 

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

"macropod" wrote:

Hi Pank,

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

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

"Pank" wrote in message ...
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 dont 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.

Help with DateValue function.
 

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 dont 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.

Help with DateValue function.
 

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.


In article ,
"macropod" wrote:

Hi Pank,

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

Cheers

Help with DateValue function.
 

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.

Help with DateValue function.
 

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.

Help with DateValue function.
 

Macropod and JE McGimpsey,

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



"macropod" wrote:

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.