Problem with TEXT command with date in heading

TotallyConfused · Oct 18, 2017

Hello
I’m having a problem with the date line in one of my reports. In my ‘Variables’ sheet, I have the following ranges and information.

Code:

          A                B
1    CUR_YEAR_V          2017
2    CUR_MONTH_V            6
3    CUR_DAY_V              7

I have named the cells in B1:B3 according to what you see in A1:A3. I know the range names are correct, because I can place the cursor any place on the sheet, then press F5, type in CUR_MONTH_V and the cursor will immediately jump to B2.

As part of the heading on my ‘Report’ sheet, I have the following line:

Code:

="As of Closing:  "&TEXT(CUR_DAY_V,"DDDD ")&TEXT(CUR_MONTH_V,"MMMM ")&CUR_DAY_V&", "&CUR_YEAR_V

This line works great, except for one problem. The ‘Day’ and the ‘Year’ are fine, however, it doesn’t matter what number I enter for CUR_MONTH_V, I ALWAYS get January listed as part of the date on the report.

I thought perhaps there was something in that date line that was interfering with the month part, so as a test, and in a separate cell, I typed: =TEXT(CUR_MONTH_V,”MMMM”)
I still received the same answer of January.

As added information, the user starts out with MENU.xlsm. After selecting the button for this program, they enter the date. Here they type in 2017 6 7. Control then passes to this program, which links to (imports) that data. I can’t see where there would be a problem in this process because the ‘DAY’ and ‘YEAR’ work okay.

Why do I always receive the month of January for a date? Thank you in advance for any help or advice you are able to offer.

TotallyConfused

Rick Rothstein · Oct 18, 2017

Does this work for you...

="As of Closing: "&TEXT(DATE(CUR_YEAR_V,CUR_MONTH_V,CUR_DAY_V),"dddd, mmm d, yyyy")

TotallyConfused · Oct 18, 2017

Rick Rothstein said:
Does this work for you...

="As of Closing: "&TEXT(DATE(CUR_YEAR_V,CUR_MONTH_V,CUR_DAY_V),"dddd, mmm d, yyyy")

Hello Rick

To answer your question, YES, it works perfectly. I can’t THANK YOU enough. You have no idea what a relief it was when I copied/pasted your response into a cell, to see the right answer appear. It seems as if you always manage to come up with a solution that is shorter (fewer words) and simpler to read than anyone else. Your code showed me something that I wouldn’t have thought possible. I assumed I’d need ‘dd’ in order to show the double digit days of a month. Now, thanks to you, I know that a single ‘d’ will work for both single and double digit days. You are a GENIUS!

This makes me wonder about something. Since the DAY and YEAR part worked okay, why didn’t the MONTH? Any ideas why?

Now a question on a slightly different topic. I’d like to ‘vote’ for you to show my appreciation and to give you credit for your help, but not sure how. I see you have 21 posts Mentioned and 4 threads Tagged. I’m not exactly sure what that means, but that is okay. At the bottom I see where I can click on ‘Thank you for posting this’ and also ‘Like this post’. If I click on those two options, will this help you? If there is some other way, please let me know.

THANKS again for your help.

TotallyConfused (yup, I still am at times)

pkdeimos · Oct 18, 2017

Hi,

My two cents.

When you use Text(value, format), with a date format, excel considers value as the serial number for a date.

So, if value is 7, date equivalent is 7th Jan 1900. So in you formula, TEXT(CUR_MONTH_V,"MMMM ") considers the date 7th Jan 1900 and formats it as per your format string, which is "MMMM" and return January.

Rick's formula converts individual day, month and year arguments to a date using DATE function and formats it as per your requirement.

Regards

Rick Rothstein · Oct 18, 2017

TotallyConfused said:
This makes me wonder about something. Since the DAY and YEAR part worked okay, why didn’t the MONTH? Any ideas why?

Actually, I don't thing they worked, not exactly that is. When I tried the code you posted, it showed Saturday for today, not Wednesday. Anyway, d, m and y only work (correctly) when the first value in the TEXT function is a date... the day number, month number and year number by themselves, are not dates. If you notice, I used the DATE function to take your individual numbers and create a real Excel date from them which the TEXT function could then process using its date formatting characters.

TotallyConfused said:
At the bottom I see where I can click on ‘Thank you for posting this’ and also ‘Like this post’. If I click on those two options, will this help you? If there is some other way, please let me know.

I am not sure if "help" is the right word. What Thank and Like do is acknowledge to the poster an appreciation of the posted message. The "Thanks" link does what it says... it thanks the poster for his/her offering. The "Like" link goes one step further and tells the poster that something in the message "excited" you in someway beyond just giving you information. Maybe you found an example in the message really clarified a concept for you or provided a formula or piece of code that was constructed in a way that surprised you, or any of probably hundreds of other such reasons. Some people choose to click only the "Thanks" link, others only the "Like" link while others click both... there is no hard and fast rule for which, it is whatever moves you at the moment.

TotallyConfused · Oct 19, 2017

pkdeimos said:
Hi,

My two cents.

When you use Text(value, format), with a date format, excel considers value as the serial number for a date.

So, if value is 7, date equivalent is 7th Jan 1900. So in you formula, TEXT(CUR_MONTH_V,"MMMM ") considers the date 7th Jan 1900 and formats it as per your format string, which is "MMMM" and return January.

Rick's formula converts individual day, month and year arguments to a date using DATE function and formats it as per your requirement.

Regards

Hello

Your answer is worth a lot more than two cents. To me, it’s like a small gold mine because your reply is loaded with information that I didn’t know. I never considered the possibility that Excel was trying to convert my dates into its serial format. I should have because I’m familiar with that way of handling dates. It simply never occurred to me to think of it, because I had in mind that Excel had some way of converting numbers to words method. Maybe some IF statements or CHOOSE, etc.

The moment I started reading your second paragraph, I realized why I kept getting January as a month. There is an old saying you may have heard, ‘It all suddenly became crystal clear’. THANK YOU for explaining it in a simple, easy to understand way. I appreciate that.

Now to use Rick’s explanation of how to properly thank someone, I’m going to click on the ‘Thank You’ button and also the ‘I like this post’ button. THANKS for your help.

TotallyConfused (maybe not quite so much now)

TotallyConfused · Oct 19, 2017

Hello Rick

THANK YOU for your lengthy and detailed answer to my questions. I hadn’t realized that Sat. was perhaps the wrong day. The moment I saw I was only getting January, I knew I had a problem and kept concentrating on trying to solve it, which means I hadn’t checked anything else yet. I’m glad you brought that to my attention.

As I explained in my letter to Pkdeimos, it never occurred to me to think that Excel was trying to convert my individual numbers into its serial date format. I assumed Excel had some built in function to convert numbers to text, and therefor would treat each one (day and month) separately. I did see where you used the DATE function; however, I thought that was simply a way to shorten the formula. For as long as I’ve worked with computers, one would think by now I should have learned that you NEVER assume anything when dealing with them.

Now I’ll be putting your answer to my second question to good use. As soon as I post this letter, I’ll click on both the ‘Thank you…’ and the ‘Like this post’ buttons. This isn’t the first time you have offered a good solution to one of my problems, so I want you to know how much I appreciate your help and allowing me to pick your brain for good solutions. THANKS AGAIN!

TotallyConfused (hopefully one day I’ll be able to change my name)

Problem with TEXT command with date in heading

TotallyConfused

Board Regular

Excel Facts

Rick Rothstein

MrExcel MVP

TotallyConfused

Board Regular

pkdeimos

New Member

Rick Rothstein

MrExcel MVP

TotallyConfused

Board Regular

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

Problem with TEXT command with date in heading

TotallyConfused

Board Regular

Excel Facts

Rick Rothstein

MrExcel MVP

TotallyConfused

Board Regular

pkdeimos

New Member

Rick Rothstein

MrExcel MVP

TotallyConfused

Board Regular

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock