Where am I going wrong? - Date type function.

[mr tom]

Hi,

I'm trying to generate the following title:

"Wealth Management - March (current month) 2008(current year)"

I'm using the following formula:
="Wealth Management - "&TEXT(MONTH(TODAY()),"mmmm")&" "&YEAR(TODAY())

The year bit works, returning 2008.

The month just won't work - it keeps returning January and I can't figure
out why.

Where am I going wrong?

Cheers!

 

[JMB]

="Wealth Management - "&TEXT(TODAY(),"mmmm yyyy")

XL stores dates as numbers. Month(Today()) returns 3, which is interpreted
as 1/3/1900 (and is why you keep getting January).

 

[Tyro]

It's because MONTH(TODAY()) returns 3. That is the dateserial number of day
3 which is January 3, 1900. So "mmmm:" returns January.
What you need is
="Wealth Management - "&TEXT(TODAY(),"mmmm")&" "&YEAR(TODAY()) or
="Wealth Management - "&TEXT(TODAY(),"mmmm")&" "&TEXT(TODAY(),"yyyy")

That will give you Wealth Management - March 2008.

Tyro

 

[JMB]

Unless your options are set up to use the 1904 date system, then 3 would be
1/3/1904.

 

[mr tom]

So what's a good solution?

Cheers.

="Wealth Management - "&TEXT(TODAY(),"mmmm yyyy")

XL stores dates as numbers. Month(Today()) returns 3, which is interpreted
as 1/3/1900 (and is why you keep getting January).

 

[Tyro]

Or you can use JMB's answer which is superior to mine

Tyro

 

[JMB]

I included a solution in the first line of my post - please re-read.

 

[mr tom]

Sorry - missed it!

Thanks so much.

Tom.

Unless your options are set up to use the 1904 date system, then 3 would be
1/3/1904.

 

[JMB]

quite alright - thanks for the feedback.

Sorry - missed it!

Thanks so much.

Tom.

 

[JMB]

only because my first post didn't go through, giving me an opportunity to
tweak the formula before posting my *final* answer <g>

 

[Tyro]

Actually, I really don't have a lot of time to play with this stuff. I've
been in the computer field since September 1964. I don't try anymore to
produce the best answer, just an answer. Perhaps when I retire in 6 years at
age 70, I may play with formulas to the extent that they become obfuscated.
But in the meantime, JMB, continue to contribute. It is very important.

Tyro