Text Function in Excel do not recognize YYYY as Date

[galliena]

Hello All,

I have a problem with my Text function in excel.

When I type Text(02/12/2014,"ddmmyyyy") the function show me 1202yyyy.

I know that there are other posts about this problem here and on google, however none of them seem to solve my problem.

Solutions that I already tried:

When I go to Control Panel-Regional and Language-Location, my current location is United States

In exce-file-Options-language my editing language is English(U.S.) <default> / My display Language is English <default> / Help Language English<default>.

None of these seems to work, does any one have any other idea to solve this isssue?

Other details:

Excel 2010
Windos 7</default></default></default>

 

[Rick Rothstein]

Hello All,

I have a problem with my Text function in excel.

When I type Text("02/12/2014","ddmmyyyy") the function show me 1202yyyy.

Because you are providing a "date" constant rather than a reference to a cell containing a date, try putting quote marks around your date value.

 

[galliena]

Sorry, this was just a typo.

Actually it does not matter if I write Text("02/12/2014","ddmmyyyy") or Text(A1,"ddmmyyyy") with A1=02/12/2014 or text(41682,"ddmmyyy"), all of them generates the same problem 1202yyyy.

Any other advice?

 

[MARK858]

Have you tried selecting the column and using data-text to column-click next twice- then under date select dmy

 

[galliena]

unfortunately, it does not work.

 

[dabrisbon]

This is so odd. When I copy and paste all your examples - I get I get 2014 - not yyyy. But I have HAD a this problem in historical spreadsheets. Try this instead:

=Text(A1,"ddmm")&year(a1)

Not sure why - but this usually works. And I betcha if you try your original solution in the future at some point - it will magically work.

(Also - are you truly wanting day,month, then year - or is that a typo and you really need month, day year - without dashes because if so - thats =Text(A1,"mmdd")&year(a1)

Hope this helps.

 

[galliena]

I am gonna go for your solution dabrisbon at least until it magically work(it always happens).

Thank you,

If any one has any permanent solution please tell me.

 

[dabrisbon]

You're welcome

 

[minhhieule89]

Sorry, this was just a typo.

Actually it does not matter if I write Text("02/12/2014","ddmmyyyy") or Text(A1,"ddmmyyyy") with A1=02/12/2014 or text(41682,"ddmmyyy"), all of them generates the same problem 1202yyyy.

Any other advice?

it's Text("02/12/2014","dd-mm-yyyy")

just tried myself and it worked