Help with DateValue function.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pank - 29 Mar 2007 11:06 GMT

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.

Reply to this Message

macropod - 29 Mar 2007 11:19 GMT

Hi Pank,

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

Cheers

Signature

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

>I have the following formula in a cell:-
>
[quoted text clipped - 8 lines]
>
> Thank you.

Reply to this Message

Pank - 29 Mar 2007 12:10 GMT

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,
>
[quoted text clipped - 14 lines]
> >
> > Thank you.

Reply to this Message

JE McGimpsey - 29 Mar 2007 12:46 GMT

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.

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

Reply to this Message

JE McGimpsey - 29 Mar 2007 12:44 GMT

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.

> I have the following formula in a cell:-
>
[quoted text clipped - 8 lines]
>
> Thank you.

Reply to this Message

Pank - 29 Mar 2007 12:58 GMT

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?

> One way:
>
[quoted text clipped - 15 lines]
> >
> > Thank you.

Reply to this Message

macropod - 29 Mar 2007 13:15 GMT

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

Signature

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

> Hi JE McGimpsey,
>
[quoted text clipped - 26 lines]
>> >
>> > Thank you.

Reply to this Message

Pank - 29 Mar 2007 14:04 GMT

Macropod and JE McGimpsey,

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

> Sorry,
>
[quoted text clipped - 32 lines]
> >> >
> >> > Thank you.

Reply to this Message