Where am I going wrong? - Date type function.

Where am I going wrong? - Date type function.

Post by bXIgdG9 » Sun, 09 Mar 2008 08:53:01


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!
 
 
 

Where am I going wrong? - Date type function.

Post by Sk1C » Sun, 09 Mar 2008 09:03:01

="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).

 
 
 

Where am I going wrong? - Date type function.

Post by Tyro » Sun, 09 Mar 2008 09:06:05

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
 
 
 

Where am I going wrong? - Date type function.

Post by Sk1C » Sun, 09 Mar 2008 09:07:11

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

Where am I going wrong? - Date type function.

Post by bXIgdG9 » Sun, 09 Mar 2008 09:07:13

So what's a good solution?

Cheers.
 
 
 

Where am I going wrong? - Date type function.

Post by Tyro » Sun, 09 Mar 2008 09:08:14

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

Tyro
 
 
 

Where am I going wrong? - Date type function.

Post by Sk1C » Sun, 09 Mar 2008 09:10:03

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

Where am I going wrong? - Date type function.

Post by bXIgdG9 » Sun, 09 Mar 2008 09:12:00

Sorry - missed it!

Thanks so much.

Tom.
 
 
 

Where am I going wrong? - Date type function.

Post by Sk1C » Sun, 09 Mar 2008 09:16:02

quite alright - thanks for the feedback.
 
 
 

Where am I going wrong? - Date type function.

Post by Sk1C » Sun, 09 Mar 2008 09:19:01

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

Where am I going wrong? - Date type function.

Post by Tyro » Sun, 09 Mar 2008 13:43:36

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