Help with DateValue function (part 2)

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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)
 
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
 
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)
 
Bob,

Thanks for your input.

Regards

Bob Phillips said:
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)
Click to expand...
 
Back
Top