Ideal and unequivocal date format for import to ExcelHow do I enter dates in ISO 8601 date format...
How can I be pwned if I'm not registered on the compromised site?
Should we avoid writing fiction about historical events without extensive research?
Replacing tantalum capacitor with ceramic capacitor for Op Amps
Practical reasons to have both a large police force and bounty hunting network?
Why do we call complex numbers “numbers” but we don’t consider 2 vectors numbers?
Dukha vs legitimate need
Is there a math expression equivalent to the conditional ternary operator?
Integrating function with /; in its definition
Are small insurances worth it
Quitting employee has privileged access to critical information
Should I use HTTPS on a domain that will only be used for redirection?
How do you make a gun that shoots melee weapons and/or swords?
Professor forcing me to attend a conference
What is Tony Stark injecting into himself in Iron Man 3?
Is every open circuit a capacitor?
How to write a chaotic neutral protagonist and prevent my readers from thinking they are evil?
Sundering Titan and basic normal lands and snow lands
What can I do if someone tampers with my SSH public key?
Is this nominative case or accusative case?
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
Why is my explanation wrong?
What is the oldest European royal house?
Computing the volume of a simplex-like object with constraints
When to use the term transposed instead of modulation?
Ideal and unequivocal date format for import to Excel
How do I enter dates in ISO 8601 date format (YYYY-MM-DD) in Excel (and have Excel recognize that format as a date value)?date format excelImporting specific date format to Excel from Microsoft SQL ServerShow date format in Excel as the OS defaultDate format worldwide in ExcelConverting text date into standard date format in Excel 2010How to set date format when manually inserting date on excel, without changing regional settings?Prevent excel from considering date based on system's date formatTable column has non-standard date format, but Excel won't recognise date for that formatExcel Power Query date format messup
Our software is used by folks around the world, so their PC's run different locales which particularly affects the presentation of dates. I need to export from our software a file to be imported into Excel by those users.
Now, bear in mind that different locales display dates in different formats - for example in the UK its is dd/mm/yy and the US has mm/dd/yy. I want the date data to appear to the users who load the file in their own locale format. But I need to write the files out of my system in a standard manner. So I can't write a format sympathetic to the locale of the recipient.
I am assuming that excel reads date data, and converts it to a display format for the locale setting of the viewer.
What is the clear and unequivocal data format to write dates into a file that will import into Excel, respecting locale, that will appear in Excel as a correctly formatted date.
I did some research with Google but could not find an obvious answer. I considered an ISO format but got mixed information from Google on that one, so am asking the question here.
microsoft-excel
New contributor
|
show 1 more comment
Our software is used by folks around the world, so their PC's run different locales which particularly affects the presentation of dates. I need to export from our software a file to be imported into Excel by those users.
Now, bear in mind that different locales display dates in different formats - for example in the UK its is dd/mm/yy and the US has mm/dd/yy. I want the date data to appear to the users who load the file in their own locale format. But I need to write the files out of my system in a standard manner. So I can't write a format sympathetic to the locale of the recipient.
I am assuming that excel reads date data, and converts it to a display format for the locale setting of the viewer.
What is the clear and unequivocal data format to write dates into a file that will import into Excel, respecting locale, that will appear in Excel as a correctly formatted date.
I did some research with Google but could not find an obvious answer. I considered an ISO format but got mixed information from Google on that one, so am asking the question here.
microsoft-excel
New contributor
3
yyyymmdd
is probably the best I've found between software packages.
– Jeeped
19 hours ago
I would agree in principle, but if you make the scope of dates 'all time' then what does 20120120 mean - is it 20th Jan 2012 or 20th Dec in the year 0120. I know its a dodgy example but without any kind of format indicator it IS open to interpretation, so fails the unequivocal test.
– Vanquished Wombat
19 hours ago
1
Part of it is also communicating the chosen date format to your users.
– cybernetic.nomad
17 hours ago
1
The ideal, unambiguous format may simply be: "January 20th, 2012"
– cybernetic.nomad
17 hours ago
@cybernetic.nomad - but that's kind of my point, I want Excel to do the presentation work and respect the locale of the viewer. I am working at the data end of the deal and I just want to know how to give Excel a date that will be perfectly understandable to Excel. Does January 20th 2012 import into a French local with the correct month name, for example?
– Vanquished Wombat
10 hours ago
|
show 1 more comment
Our software is used by folks around the world, so their PC's run different locales which particularly affects the presentation of dates. I need to export from our software a file to be imported into Excel by those users.
Now, bear in mind that different locales display dates in different formats - for example in the UK its is dd/mm/yy and the US has mm/dd/yy. I want the date data to appear to the users who load the file in their own locale format. But I need to write the files out of my system in a standard manner. So I can't write a format sympathetic to the locale of the recipient.
I am assuming that excel reads date data, and converts it to a display format for the locale setting of the viewer.
What is the clear and unequivocal data format to write dates into a file that will import into Excel, respecting locale, that will appear in Excel as a correctly formatted date.
I did some research with Google but could not find an obvious answer. I considered an ISO format but got mixed information from Google on that one, so am asking the question here.
microsoft-excel
New contributor
Our software is used by folks around the world, so their PC's run different locales which particularly affects the presentation of dates. I need to export from our software a file to be imported into Excel by those users.
Now, bear in mind that different locales display dates in different formats - for example in the UK its is dd/mm/yy and the US has mm/dd/yy. I want the date data to appear to the users who load the file in their own locale format. But I need to write the files out of my system in a standard manner. So I can't write a format sympathetic to the locale of the recipient.
I am assuming that excel reads date data, and converts it to a display format for the locale setting of the viewer.
What is the clear and unequivocal data format to write dates into a file that will import into Excel, respecting locale, that will appear in Excel as a correctly formatted date.
I did some research with Google but could not find an obvious answer. I considered an ISO format but got mixed information from Google on that one, so am asking the question here.
microsoft-excel
microsoft-excel
New contributor
New contributor
edited 10 hours ago
Vanquished Wombat
New contributor
asked 20 hours ago
Vanquished WombatVanquished Wombat
1063
1063
New contributor
New contributor
3
yyyymmdd
is probably the best I've found between software packages.
– Jeeped
19 hours ago
I would agree in principle, but if you make the scope of dates 'all time' then what does 20120120 mean - is it 20th Jan 2012 or 20th Dec in the year 0120. I know its a dodgy example but without any kind of format indicator it IS open to interpretation, so fails the unequivocal test.
– Vanquished Wombat
19 hours ago
1
Part of it is also communicating the chosen date format to your users.
– cybernetic.nomad
17 hours ago
1
The ideal, unambiguous format may simply be: "January 20th, 2012"
– cybernetic.nomad
17 hours ago
@cybernetic.nomad - but that's kind of my point, I want Excel to do the presentation work and respect the locale of the viewer. I am working at the data end of the deal and I just want to know how to give Excel a date that will be perfectly understandable to Excel. Does January 20th 2012 import into a French local with the correct month name, for example?
– Vanquished Wombat
10 hours ago
|
show 1 more comment
3
yyyymmdd
is probably the best I've found between software packages.
– Jeeped
19 hours ago
I would agree in principle, but if you make the scope of dates 'all time' then what does 20120120 mean - is it 20th Jan 2012 or 20th Dec in the year 0120. I know its a dodgy example but without any kind of format indicator it IS open to interpretation, so fails the unequivocal test.
– Vanquished Wombat
19 hours ago
1
Part of it is also communicating the chosen date format to your users.
– cybernetic.nomad
17 hours ago
1
The ideal, unambiguous format may simply be: "January 20th, 2012"
– cybernetic.nomad
17 hours ago
@cybernetic.nomad - but that's kind of my point, I want Excel to do the presentation work and respect the locale of the viewer. I am working at the data end of the deal and I just want to know how to give Excel a date that will be perfectly understandable to Excel. Does January 20th 2012 import into a French local with the correct month name, for example?
– Vanquished Wombat
10 hours ago
3
3
yyyymmdd
is probably the best I've found between software packages.– Jeeped
19 hours ago
yyyymmdd
is probably the best I've found between software packages.– Jeeped
19 hours ago
I would agree in principle, but if you make the scope of dates 'all time' then what does 20120120 mean - is it 20th Jan 2012 or 20th Dec in the year 0120. I know its a dodgy example but without any kind of format indicator it IS open to interpretation, so fails the unequivocal test.
– Vanquished Wombat
19 hours ago
I would agree in principle, but if you make the scope of dates 'all time' then what does 20120120 mean - is it 20th Jan 2012 or 20th Dec in the year 0120. I know its a dodgy example but without any kind of format indicator it IS open to interpretation, so fails the unequivocal test.
– Vanquished Wombat
19 hours ago
1
1
Part of it is also communicating the chosen date format to your users.
– cybernetic.nomad
17 hours ago
Part of it is also communicating the chosen date format to your users.
– cybernetic.nomad
17 hours ago
1
1
The ideal, unambiguous format may simply be: "January 20th, 2012"
– cybernetic.nomad
17 hours ago
The ideal, unambiguous format may simply be: "January 20th, 2012"
– cybernetic.nomad
17 hours ago
@cybernetic.nomad - but that's kind of my point, I want Excel to do the presentation work and respect the locale of the viewer. I am working at the data end of the deal and I just want to know how to give Excel a date that will be perfectly understandable to Excel. Does January 20th 2012 import into a French local with the correct month name, for example?
– Vanquished Wombat
10 hours ago
@cybernetic.nomad - but that's kind of my point, I want Excel to do the presentation work and respect the locale of the viewer. I am working at the data end of the deal and I just want to know how to give Excel a date that will be perfectly understandable to Excel. Does January 20th 2012 import into a French local with the correct month name, for example?
– Vanquished Wombat
10 hours ago
|
show 1 more comment
0
active
oldest
votes
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
});
}
});
Vanquished Wombat is a new contributor. Be nice, and check out our Code of Conduct.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1412061%2fideal-and-unequivocal-date-format-for-import-to-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Vanquished Wombat is a new contributor. Be nice, and check out our Code of Conduct.
Vanquished Wombat is a new contributor. Be nice, and check out our Code of Conduct.
Vanquished Wombat is a new contributor. Be nice, and check out our Code of Conduct.
Vanquished Wombat is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1412061%2fideal-and-unequivocal-date-format-for-import-to-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
yyyymmdd
is probably the best I've found between software packages.– Jeeped
19 hours ago
I would agree in principle, but if you make the scope of dates 'all time' then what does 20120120 mean - is it 20th Jan 2012 or 20th Dec in the year 0120. I know its a dodgy example but without any kind of format indicator it IS open to interpretation, so fails the unequivocal test.
– Vanquished Wombat
19 hours ago
1
Part of it is also communicating the chosen date format to your users.
– cybernetic.nomad
17 hours ago
1
The ideal, unambiguous format may simply be: "January 20th, 2012"
– cybernetic.nomad
17 hours ago
@cybernetic.nomad - but that's kind of my point, I want Excel to do the presentation work and respect the locale of the viewer. I am working at the data end of the deal and I just want to know how to give Excel a date that will be perfectly understandable to Excel. Does January 20th 2012 import into a French local with the correct month name, for example?
– Vanquished Wombat
10 hours ago