Help with DateValue function (part 2)

[Guest]

I was provided with the following formula courtesy of macropod and JE
McGimpsey which works exactly as expected.

The formula is:-

=IF($A$4="spring",DATE(YEAR(TODAY())-1,12,31),IF($A$4="summer",DATE(YEAR(TODAY())-1,3,31),
IF($A$4="autumn",DATE(YEAR(TODAY())-1,8,31),"ENTER CORRECT SESSION")))

The question I have is that as the TODAY function is used, will the field
with the formula change on the turn of the year?
What I mean is, if the formula is in L2 and the value of A4 is spring, then
today L2 will be 31/12/06, however on the 01/01/2008, when the sheet is
opened, the contents of L2 will 31/12/07.

If my assumption is correct, then what needs to be done to the formula to
ensure the value does not change at the turn of year.

Your help will be appreciated.

 

[Bob Phillips]

It will be 31/12/2007.

If this is not what you want, change TODAY() to 2007 throughout.

BTW, shouldn't the 8 in the autumn date calculation be 9?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob,

Many thanks for the prompt. I should have posted the question that I
initially asked, that both Macropod and JE McGimpsey responded to.

The question that I initially asked was:-

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

Therefore in light of the above, I do not want to hard code the year as you
suggested.

Once the date has been entered, then on the change of a year I do not want
the value to change.

Regards

 

[Bob Phillips]

Then JE and macropod addressed your needs as they did not hard code the
year. They hard-coded the end of the year, but by putting YEAR(TODAY())-1,
they gave you a formula that would always use the end of the previous year,
31/12/2006 now, 31/12/2007 in 2008.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob,

Thanks for your input.

Regards

Then JE and macropod addressed your needs as they did not hard code the
year. They hard-coded the end of the year, but by putting YEAR(TODAY())-1,
they gave you a formula that would always use the end of the previous year,
31/12/2006 now, 31/12/2007 in 2008.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)