Tjyylli

How to educate team mate to take screenshots for bugs with out unwanted stuff

(Codewars) Linked Lists-Sorted Insert

Are these two graphs isomorphic? Why/Why not?

Movie: boy escapes the real world and goes to a fantasy world with big furry trolls

Does the US political system, in principle, allow for a no-party system?

What should I do when a paper is published similar to my PhD thesis without citation?

Locked Away- What am I?

School performs periodic password audits. Is my password compromised?

PTIJ: Who was the sixth set of priestly clothes for?

What does *dead* mean in *What do you mean, dead?*?

Will expression retain the same definition if particle is changed?

Writing text next to a table

Would those living in a "perfect society" not understand satire

Can I negotiate a patent idea for a raise, under French law?

I can't die. Who am I?

Why do we say 'Pairwise Disjoint', rather than 'Disjoint'?

Was it really inappropriate to write a pull request for the company I interviewed with?

Having the player face themselves after the mid-game

How do you make a gun that shoots melee weapons and/or swords?

If nine coins are tossed, what is the probability that the number of heads is even?

Create chunks from an array

Does an unused member variable take up memory?

Help! My Character is too much for her story!

Is there a way to make cleveref distinguish two environments with the same counter?

Text function in Excel isn't formatting year

Excel TEXT function does not work properlyMicrosoft Excel 2007 - Cannot Fit All Text Into CellSplit text seperated by multiple characters using excel 2007MS EXCEL 2010 formulaExcel date format transposing numbersExcel “IF” function has totally stopped workingExcel VBA or Formula to convert text to Date with Time typeExcel Convert the correct Date typeHow to convert dd.mm.yy date format in excel 2007 dateError extracting year when converting date to text in ExcelExcel Text to Date format

2

2

I've got a problem with the text function in Excel (2007).
It doesn't format the year, it just states yyyy instead of e.g. 2014.

If I have a cell with 23-7-2014 and use the formula =TEXT(A1,"dd-mm-yyyy") I get 23-7-yyyy.

My PC is set to using the dd-mm-yyyy format.

I'm using this function in a larger formula mainly consisting of merging multiple columns in a single cell: ="reg"&C1533&"_"&D1533&"_"&E1533&"_"&F1533&"_"&TEXT(G1533,"mm-dd-yyyy")

Here's a screenshot of the issue:

microsoft-excel microsoft-excel-2010 microsoft-excel-2007 microsoft-excel-2013 microsoft-excel-2011

share|improve this question

edited Feb 7 at 6:41

angelofdev

929120

asked Jul 23 '14 at 8:34

JvE010JvE010

1612

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I can't get excel to duplicate this behaviour. For me, =TEXT(A1, "dd-mm-yyyy") works as does =TEXT(A1, "mm-dd-yyyy"). It doesn't make a difference whether the original data is a date or just text, it works either way.
  
  – mcalex
  Jul 23 '14 at 9:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is your language on the PC?
  
  – Dave
  Jul 23 '14 at 9:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DaveRook The language is set to Dutch. I also tried changing the date formatting in "Region and Language" but still no luck.
  
  – JvE010
  Jul 24 '14 at 8:51
  
  
  

  
  
  
  

add a comment | 

2

2

I've got a problem with the text function in Excel (2007).
It doesn't format the year, it just states yyyy instead of e.g. 2014.

If I have a cell with 23-7-2014 and use the formula =TEXT(A1,"dd-mm-yyyy") I get 23-7-yyyy.

My PC is set to using the dd-mm-yyyy format.

I'm using this function in a larger formula mainly consisting of merging multiple columns in a single cell: ="reg"&C1533&"_"&D1533&"_"&E1533&"_"&F1533&"_"&TEXT(G1533,"mm-dd-yyyy")

Here's a screenshot of the issue:

microsoft-excel microsoft-excel-2010 microsoft-excel-2007 microsoft-excel-2013 microsoft-excel-2011

share|improve this question

edited Feb 7 at 6:41

angelofdev

929120

asked Jul 23 '14 at 8:34

JvE010JvE010

1612

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I can't get excel to duplicate this behaviour. For me, =TEXT(A1, "dd-mm-yyyy") works as does =TEXT(A1, "mm-dd-yyyy"). It doesn't make a difference whether the original data is a date or just text, it works either way.
  
  – mcalex
  Jul 23 '14 at 9:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is your language on the PC?
  
  – Dave
  Jul 23 '14 at 9:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DaveRook The language is set to Dutch. I also tried changing the date formatting in "Region and Language" but still no luck.
  
  – JvE010
  Jul 24 '14 at 8:51
  
  
  

  
  
  
  

add a comment | 

I've got a problem with the text function in Excel (2007).
It doesn't format the year, it just states yyyy instead of e.g. 2014.

If I have a cell with 23-7-2014 and use the formula =TEXT(A1,"dd-mm-yyyy") I get 23-7-yyyy.

My PC is set to using the dd-mm-yyyy format.

I'm using this function in a larger formula mainly consisting of merging multiple columns in a single cell: ="reg"&C1533&"_"&D1533&"_"&E1533&"_"&F1533&"_"&TEXT(G1533,"mm-dd-yyyy")

Here's a screenshot of the issue:

microsoft-excel microsoft-excel-2010 microsoft-excel-2007 microsoft-excel-2013 microsoft-excel-2011

share|improve this question

edited Feb 7 at 6:41

angelofdev

929120

asked Jul 23 '14 at 8:34

JvE010JvE010

1612

share|improve this question

edited Feb 7 at 6:41

angelofdev

929120

asked Jul 23 '14 at 8:34

JvE010JvE010

1612

share|improve this question

edited Feb 7 at 6:41

angelofdev

929120

asked Jul 23 '14 at 8:34

JvE010JvE010

1612

edited Feb 7 at 6:41

angelofdev

929120

edited Feb 7 at 6:41

angelofdev

929120

asked Jul 23 '14 at 8:34

JvE010JvE010

1612

asked Jul 23 '14 at 8:34

JvE010JvE010

1612

3 Answers
3

active

oldest

votes

0

This is due to your PC's regional settings. You will need to go to the Control Panel, and then "Region and Language", then set the date settings there to match dd-mm-yyyy.

share|improve this answer

answered Jul 23 '14 at 9:19

JoeyJoey

341214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Still no luck. Changed it to dd-mm-yyyy (was set to dd-M-yyyy).
  
  – JvE010
  Jul 24 '14 at 8:50
  

  
  
  
  

add a comment | 

0

I'm not sure this is the correct way to solve it, but this works on my machine with the dutch language.

In dutch the y (for year) is substituted for the j (for jaar).

TEXT(G1533, "mm-dd-jjjj")

Or ... you could resort to this:

CONCATENATE(TEXT(G1533, "mm-dd-"), YEAR(G1533))

Also see Excel TEXT function does not work properly

share|improve this answer

edited Mar 20 '17 at 10:17

Community♦

1

answered Feb 27 '15 at 10:33

Kristof NeirynckKristof Neirynck

34115

add a comment | 

-1

It is enough to write e instead of yyyy:

text(a1;"e-mm-dd")

share|improve this answer

edited Aug 5 '14 at 11:04

Jan Doggen

3,14652742

answered Aug 5 '14 at 9:56

user353435user353435

1

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f786790%2ftext-function-in-excel-isnt-formatting-year%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

This is due to your PC's regional settings. You will need to go to the Control Panel, and then "Region and Language", then set the date settings there to match dd-mm-yyyy.

share|improve this answer

answered Jul 23 '14 at 9:19

JoeyJoey

341214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Still no luck. Changed it to dd-mm-yyyy (was set to dd-M-yyyy).
  
  – JvE010
  Jul 24 '14 at 8:50
  

  
  
  
  

add a comment | 

0

This is due to your PC's regional settings. You will need to go to the Control Panel, and then "Region and Language", then set the date settings there to match dd-mm-yyyy.

share|improve this answer

answered Jul 23 '14 at 9:19

JoeyJoey

341214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Still no luck. Changed it to dd-mm-yyyy (was set to dd-M-yyyy).
  
  – JvE010
  Jul 24 '14 at 8:50
  

  
  
  
  

add a comment | 

This is due to your PC's regional settings. You will need to go to the Control Panel, and then "Region and Language", then set the date settings there to match dd-mm-yyyy.

share|improve this answer

answered Jul 23 '14 at 9:19

JoeyJoey

341214

share|improve this answer

answered Jul 23 '14 at 9:19

JoeyJoey

341214

answered Jul 23 '14 at 9:19

JoeyJoey

341214

answered Jul 23 '14 at 9:19

JoeyJoey

341214

0

I'm not sure this is the correct way to solve it, but this works on my machine with the dutch language.

In dutch the y (for year) is substituted for the j (for jaar).

TEXT(G1533, "mm-dd-jjjj")

Or ... you could resort to this:

CONCATENATE(TEXT(G1533, "mm-dd-"), YEAR(G1533))

Also see Excel TEXT function does not work properly

share|improve this answer

edited Mar 20 '17 at 10:17

Community♦

1

answered Feb 27 '15 at 10:33

Kristof NeirynckKristof Neirynck

34115

add a comment | 

0

I'm not sure this is the correct way to solve it, but this works on my machine with the dutch language.

In dutch the y (for year) is substituted for the j (for jaar).

TEXT(G1533, "mm-dd-jjjj")

Or ... you could resort to this:

CONCATENATE(TEXT(G1533, "mm-dd-"), YEAR(G1533))

Also see Excel TEXT function does not work properly

share|improve this answer

edited Mar 20 '17 at 10:17

Community♦

1

answered Feb 27 '15 at 10:33

Kristof NeirynckKristof Neirynck

34115

add a comment | 

I'm not sure this is the correct way to solve it, but this works on my machine with the dutch language.

In dutch the y (for year) is substituted for the j (for jaar).

TEXT(G1533, "mm-dd-jjjj")

Or ... you could resort to this:

CONCATENATE(TEXT(G1533, "mm-dd-"), YEAR(G1533))

Also see Excel TEXT function does not work properly

share|improve this answer

edited Mar 20 '17 at 10:17

Community♦

1

answered Feb 27 '15 at 10:33

Kristof NeirynckKristof Neirynck

34115

TEXT(G1533, "mm-dd-jjjj")

CONCATENATE(TEXT(G1533, "mm-dd-"), YEAR(G1533))

share|improve this answer

edited Mar 20 '17 at 10:17

Community♦

1

answered Feb 27 '15 at 10:33

Kristof NeirynckKristof Neirynck

34115

edited Mar 20 '17 at 10:17

Community♦

1

edited Mar 20 '17 at 10:17

Community♦

1

answered Feb 27 '15 at 10:33

Kristof NeirynckKristof Neirynck

34115

answered Feb 27 '15 at 10:33

Kristof NeirynckKristof Neirynck

34115

-1

It is enough to write e instead of yyyy:

text(a1;"e-mm-dd")

share|improve this answer

edited Aug 5 '14 at 11:04

Jan Doggen

3,14652742

answered Aug 5 '14 at 9:56

user353435user353435

1

add a comment | 

-1

It is enough to write e instead of yyyy:

text(a1;"e-mm-dd")

share|improve this answer

edited Aug 5 '14 at 11:04

Jan Doggen

3,14652742

answered Aug 5 '14 at 9:56

user353435user353435

1

add a comment | 

It is enough to write e instead of yyyy:

text(a1;"e-mm-dd")

share|improve this answer

edited Aug 5 '14 at 11:04

Jan Doggen

3,14652742

answered Aug 5 '14 at 9:56

user353435user353435

1

share|improve this answer

edited Aug 5 '14 at 11:04

Jan Doggen

3,14652742

answered Aug 5 '14 at 9:56

user353435user353435

1

edited Aug 5 '14 at 11:04

Jan Doggen

3,14652742

edited Aug 5 '14 at 11:04

Jan Doggen

3,14652742

answered Aug 5 '14 at 9:56

user353435user353435

1

answered Aug 5 '14 at 9:56

user353435user353435

1