MS Excel - How to read DATE from text generated with different locale
I'm writing this question after reading How to use DATEVALUE with diffent locale setting without changing system settings didn't solve my problem.
It's about reading the correct month from a text created with a different locale. For example the dates come from US locale and I'm using GE locale. Then the months are slightly different:
US months JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
GE months JAN, FEB, MRZ, APR, MAI, JUN, JUL, AUG, SEP, OKT, NOV, DEZ
I've seen a solution to write a different locale using TEXT function, but I wonder what is the efficient way to do it now. (I consider creating a table or array as last resort ;-)).
Thanks for your hints!
+Daniel+
microsoft-excel date-time locale
asked Jan 23 at 11:00
user10884217user10884217
61
add a comment |
I'm writing this question after reading How to use DATEVALUE with diffent locale setting without changing system settings didn't solve my problem.
It's about reading the correct month from a text created with a different locale. For example the dates come from US locale and I'm using GE locale. Then the months are slightly different:
US months JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
GE months JAN, FEB, MRZ, APR, MAI, JUN, JUL, AUG, SEP, OKT, NOV, DEZ
I've seen a solution to write a different locale using TEXT function, but I wonder what is the efficient way to do it now. (I consider creating a table or array as last resort ;-)).
Thanks for your hints!
+Daniel+
microsoft-excel date-time locale
asked Jan 23 at 11:00
user10884217user10884217
61
3
".. from a text created with a different locale.. " so the source cell is formatted as a text or excel date value? if text, what is the format? any sample data/intended-output?
– p._phidot_
Jan 23 at 11:05
Example '03-JAN-2019 15:43' to Excel date (serial number).
– user10884217
Feb 8 at 10:42
add a comment |
I'm writing this question after reading How to use DATEVALUE with diffent locale setting without changing system settings didn't solve my problem.
It's about reading the correct month from a text created with a different locale. For example the dates come from US locale and I'm using GE locale. Then the months are slightly different:
US months JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
GE months JAN, FEB, MRZ, APR, MAI, JUN, JUL, AUG, SEP, OKT, NOV, DEZ
I've seen a solution to write a different locale using TEXT function, but I wonder what is the efficient way to do it now. (I consider creating a table or array as last resort ;-)).
Thanks for your hints!
+Daniel+
microsoft-excel date-time locale
asked Jan 23 at 11:00
user10884217user10884217
61
I'm writing this question after reading How to use DATEVALUE with diffent locale setting without changing system settings didn't solve my problem.
It's about reading the correct month from a text created with a different locale. For example the dates come from US locale and I'm using GE locale. Then the months are slightly different:
US months JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
GE months JAN, FEB, MRZ, APR, MAI, JUN, JUL, AUG, SEP, OKT, NOV, DEZ
I've seen a solution to write a different locale using TEXT function, but I wonder what is the efficient way to do it now. (I consider creating a table or array as last resort ;-)).
Thanks for your hints!
+Daniel+
microsoft-excel date-time locale
microsoft-excel date-time locale
asked Jan 23 at 11:00
user10884217user10884217
61
asked Jan 23 at 11:00
user10884217user10884217
61
asked Jan 23 at 11:00
user10884217user10884217
61
asked Jan 23 at 11:00
user10884217user10884217
61
asked Jan 23 at 11:00
user10884217user10884217
61
61
3
".. from a text created with a different locale.. " so the source cell is formatted as a text or excel date value? if text, what is the format? any sample data/intended-output?
– p._phidot_
Jan 23 at 11:05
Example '03-JAN-2019 15:43' to Excel date (serial number).
– user10884217
Feb 8 at 10:42
add a comment |
3
".. from a text created with a different locale.. " so the source cell is formatted as a text or excel date value? if text, what is the format? any sample data/intended-output?
– p._phidot_
Jan 23 at 11:05
Example '03-JAN-2019 15:43' to Excel date (serial number).
– user10884217
Feb 8 at 10:42
3
3
".. from a text created with a different locale.. " so the source cell is formatted as a text or excel date value? if text, what is the format? any sample data/intended-output?
– p._phidot_
Jan 23 at 11:05
".. from a text created with a different locale.. " so the source cell is formatted as a text or excel date value? if text, what is the format? any sample data/intended-output?
– p._phidot_
Jan 23 at 11:05
Example '03-JAN-2019 15:43' to Excel date (serial number).
– user10884217
Feb 8 at 10:42
Example '03-JAN-2019 15:43' to Excel date (serial number).
– user10884217
Feb 8 at 10:42
add a comment |
1 Answer
1
active
oldest
votes
..using TEXT function, but I wonder what is the efficient way ..
Efficient in term of?... I'm not sure whether it simpler or more complex .. I think simple is relative. What I hold on to is.. a method is efficient when we have faith (and clear understanding) in it. IMHO, it'll be more troublesome when we have doubt (in the method) added in. ( :
Assuming the source text is in A1,
do :
=DATE(MID(A1,8,4),CHOOSE(MATCH(MID(A1,4,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),1,2,3,4,5,6,7,8,9,10,11,12),MID(A1,1,2))+TIME(MID(A1,13,2),MID(A1,16,2),0)
Idea :
- use MID() to get the year/month/day/hh/ss (number&text) values
- use choose() n match() to "convert" the month.
- use date()&time() to "convert" to excel
hope it helps.
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution.
– user10884217
Feb 14 at 9:32
the closest to that I knew is datevalue() which can be applied by=DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0)|| but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable.
– p._phidot_
Feb 15 at 1:24
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
});
}
});
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%2f1397409%2fms-excel-how-to-read-date-from-text-generated-with-different-locale%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
..using TEXT function, but I wonder what is the efficient way ..
Efficient in term of?... I'm not sure whether it simpler or more complex .. I think simple is relative. What I hold on to is.. a method is efficient when we have faith (and clear understanding) in it. IMHO, it'll be more troublesome when we have doubt (in the method) added in. ( :
Assuming the source text is in A1,
do :
=DATE(MID(A1,8,4),CHOOSE(MATCH(MID(A1,4,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),1,2,3,4,5,6,7,8,9,10,11,12),MID(A1,1,2))+TIME(MID(A1,13,2),MID(A1,16,2),0)
Idea :
- use MID() to get the year/month/day/hh/ss (number&text) values
- use choose() n match() to "convert" the month.
- use date()&time() to "convert" to excel
hope it helps.
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution.
– user10884217
Feb 14 at 9:32
the closest to that I knew is datevalue() which can be applied by=DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0)|| but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable.
– p._phidot_
Feb 15 at 1:24
add a comment |
..using TEXT function, but I wonder what is the efficient way ..
Efficient in term of?... I'm not sure whether it simpler or more complex .. I think simple is relative. What I hold on to is.. a method is efficient when we have faith (and clear understanding) in it. IMHO, it'll be more troublesome when we have doubt (in the method) added in. ( :
Assuming the source text is in A1,
do :
=DATE(MID(A1,8,4),CHOOSE(MATCH(MID(A1,4,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),1,2,3,4,5,6,7,8,9,10,11,12),MID(A1,1,2))+TIME(MID(A1,13,2),MID(A1,16,2),0)
Idea :
- use MID() to get the year/month/day/hh/ss (number&text) values
- use choose() n match() to "convert" the month.
- use date()&time() to "convert" to excel
hope it helps.
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution.
– user10884217
Feb 14 at 9:32
the closest to that I knew is datevalue() which can be applied by=DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0)|| but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable.
– p._phidot_
Feb 15 at 1:24
add a comment |
..using TEXT function, but I wonder what is the efficient way ..
Efficient in term of?... I'm not sure whether it simpler or more complex .. I think simple is relative. What I hold on to is.. a method is efficient when we have faith (and clear understanding) in it. IMHO, it'll be more troublesome when we have doubt (in the method) added in. ( :
Assuming the source text is in A1,
do :
=DATE(MID(A1,8,4),CHOOSE(MATCH(MID(A1,4,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),1,2,3,4,5,6,7,8,9,10,11,12),MID(A1,1,2))+TIME(MID(A1,13,2),MID(A1,16,2),0)
Idea :
- use MID() to get the year/month/day/hh/ss (number&text) values
- use choose() n match() to "convert" the month.
- use date()&time() to "convert" to excel
hope it helps.
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
..using TEXT function, but I wonder what is the efficient way ..
Efficient in term of?... I'm not sure whether it simpler or more complex .. I think simple is relative. What I hold on to is.. a method is efficient when we have faith (and clear understanding) in it. IMHO, it'll be more troublesome when we have doubt (in the method) added in. ( :
Assuming the source text is in A1,
do :
=DATE(MID(A1,8,4),CHOOSE(MATCH(MID(A1,4,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),1,2,3,4,5,6,7,8,9,10,11,12),MID(A1,1,2))+TIME(MID(A1,13,2),MID(A1,16,2),0)
Idea :
- use MID() to get the year/month/day/hh/ss (number&text) values
- use choose() n match() to "convert" the month.
- use date()&time() to "convert" to excel
hope it helps.
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
answered Feb 12 at 3:10
p._phidot_p._phidot_
691412
691412
OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution.
– user10884217
Feb 14 at 9:32
the closest to that I knew is datevalue() which can be applied by=DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0)|| but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable.
– p._phidot_
Feb 15 at 1:24
add a comment |
OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution.
– user10884217
Feb 14 at 9:32
the closest to that I knew is datevalue() which can be applied by=DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0)|| but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable.
– p._phidot_
Feb 15 at 1:24
OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution.
– user10884217
Feb 14 at 9:32
OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution.
– user10884217
Feb 14 at 9:32
the closest to that I knew is datevalue() which can be applied by
=DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0) || but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable.– p._phidot_
Feb 15 at 1:24
the closest to that I knew is datevalue() which can be applied by
=DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0) || but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable.– p._phidot_
Feb 15 at 1:24
add a comment |
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%2f1397409%2fms-excel-how-to-read-date-from-text-generated-with-different-locale%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
".. from a text created with a different locale.. " so the source cell is formatted as a text or excel date value? if text, what is the format? any sample data/intended-output?
– p._phidot_
Jan 23 at 11:05
Example '03-JAN-2019 15:43' to Excel date (serial number).
– user10884217
Feb 8 at 10:42