date formatting is acting weird.

[ammartino44]

Hello. I created a formula that takes values from text and creates a column that says January 2011 (for example). The format button says date, but I can't change the formatting. Additionally, when I change it to numbers it still says January 2011. This causes problems for me when I'm doing a pivot table. Any thoughts?

[NeedForExcel]

That is because it is not a date, even though it looks like a date.. It is just a Text String..

Try to get it to a proper date format..

Let me know if you need help!

[adhawan06]

Hi Amma,

You have taken values from a text in a cell which might formatted previously in format ("mmmm yyyy"). Just because of this, you are getting value getting displayed in Date format as January 2011.

If this is not the case then please upload your file.

[ammartino44]

Here is an example. How do I get the value in yellow to be a date?

[NeedForExcel]

Here!

Use this formula in Cell D2

=DATE(B2,MONTH(1&C2),1)

And Format Cells > Custom > yyyy mmmm

[adhawan06]

Here!

Use this formula in Cell D2

=DATE(B2,MONTH(1&C2),1)

And Format Cells > Custom > yyyy mmmm

Just small addition so that she does not need to go for change in date format manually . =TEXT(DATE(B2,MONTH(1&C2),1),"YYYY MMMM").

Hope this will work for you Amma

[ammartino44]

Oh, and why did I need to use the month(1&C2)? What does that do?

[NeedForExcel]

Oh, and why did I need to use the month(1&C2)? What does that do?

Hi,

That is because, =MONTH("January") wont work, as the Month function does not accept a Text sting like "January" as an argument.

Hence I used =MONTH(1&"January") as a workaround to return the Month serial number for the Month argument of the Date function..

[ammartino44]

Awesome. Thank you both. Just a question: With my formula, why don't the format buttons work? It says date even though it isn't.

[adhawan06]

Because it was string as mentioned above by @NeedForExcel. As you just taken a reference from a cell which was not in number format. So it was showing you the same you mentioned.

Please take few seconds to mark this thread SOLVED and say thanks to the people who helped you by adding *Add Reputation

[adhawan06]

as the month function can not read the Text String so it converting Text String to numbers of month.

[NeedForExcel]

Glad it helped!