Mixing
Converting MM:DD:YYYY hh:mm:ss to DD:MM:YYYY hh:mm:ss in excel
-1
I'm trying to convert the timestamp data in Microsoft excel where the timestamp is coming as
MM:DD:YYYY hh:mm:ss (12/25/2016 12:00:55 AM).
this Formet doesnt support in excel and i wanted to change it to
DD:MM:YYYY hh:mm:ss (25/12/2016 12:00:55 AM)
i have seached so many times but all i get is DD:MM:YYYY to MM:DD:YYYY which is not the case.
excel date datetime
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
add a comment |
-1
I'm trying to convert the timestamp data in Microsoft excel where the timestamp is coming as
MM:DD:YYYY hh:mm:ss (12/25/2016 12:00:55 AM).
this Formet doesnt support in excel and i wanted to change it to
DD:MM:YYYY hh:mm:ss (25/12/2016 12:00:55 AM)
i have seached so many times but all i get is DD:MM:YYYY to MM:DD:YYYY which is not the case.
excel date datetime
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
1
Is the cell formatted as text? If yes then you can use left() and mid()
– Andreas
Jan 2 at 10:01
No it is set as general , but if i set it as text as well how would i convert it as DD:MM:YYYY
– Jitesh Vacheta
Jan 2 at 10:12
How are dates arriving in Excel? Do you type them in or are they imported from another file (and which file type - csv?)
– Darren Bartrup-Cook
Jan 2 at 10:30
@DarrenBartrup-Cook it is coming from the other data source
– Jitesh Vacheta
Jan 2 at 10:39
It would be easier to import as a datetime rather than text.
– Jason Stallard
Jan 2 at 11:58
add a comment |
-1
-1
-1
I'm trying to convert the timestamp data in Microsoft excel where the timestamp is coming as
MM:DD:YYYY hh:mm:ss (12/25/2016 12:00:55 AM).
this Formet doesnt support in excel and i wanted to change it to
DD:MM:YYYY hh:mm:ss (25/12/2016 12:00:55 AM)
i have seached so many times but all i get is DD:MM:YYYY to MM:DD:YYYY which is not the case.
excel date datetime
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
I'm trying to convert the timestamp data in Microsoft excel where the timestamp is coming as
MM:DD:YYYY hh:mm:ss (12/25/2016 12:00:55 AM).
this Formet doesnt support in excel and i wanted to change it to
DD:MM:YYYY hh:mm:ss (25/12/2016 12:00:55 AM)
i have seached so many times but all i get is DD:MM:YYYY to MM:DD:YYYY which is not the case.
excel date datetime
excel date datetime
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
asked Jan 2 at 9:54
Jitesh VachetaJitesh Vacheta
2610
2610
1
Is the cell formatted as text? If yes then you can use left() and mid()
– Andreas
Jan 2 at 10:01
No it is set as general , but if i set it as text as well how would i convert it as DD:MM:YYYY
– Jitesh Vacheta
Jan 2 at 10:12
How are dates arriving in Excel? Do you type them in or are they imported from another file (and which file type - csv?)
– Darren Bartrup-Cook
Jan 2 at 10:30
@DarrenBartrup-Cook it is coming from the other data source
– Jitesh Vacheta
Jan 2 at 10:39
It would be easier to import as a datetime rather than text.
– Jason Stallard
Jan 2 at 11:58
add a comment |
1
Is the cell formatted as text? If yes then you can use left() and mid()
– Andreas
Jan 2 at 10:01
No it is set as general , but if i set it as text as well how would i convert it as DD:MM:YYYY
– Jitesh Vacheta
Jan 2 at 10:12
How are dates arriving in Excel? Do you type them in or are they imported from another file (and which file type - csv?)
– Darren Bartrup-Cook
Jan 2 at 10:30
@DarrenBartrup-Cook it is coming from the other data source
– Jitesh Vacheta
Jan 2 at 10:39
It would be easier to import as a datetime rather than text.
– Jason Stallard
Jan 2 at 11:58
1
1
Is the cell formatted as text? If yes then you can use left() and mid()
– Andreas
Jan 2 at 10:01
Is the cell formatted as text? If yes then you can use left() and mid()
– Andreas
Jan 2 at 10:01
No it is set as general , but if i set it as text as well how would i convert it as DD:MM:YYYY
– Jitesh Vacheta
Jan 2 at 10:12
No it is set as general , but if i set it as text as well how would i convert it as DD:MM:YYYY
– Jitesh Vacheta
Jan 2 at 10:12
How are dates arriving in Excel? Do you type them in or are they imported from another file (and which file type - csv?)
– Darren Bartrup-Cook
Jan 2 at 10:30
How are dates arriving in Excel? Do you type them in or are they imported from another file (and which file type - csv?)
– Darren Bartrup-Cook
Jan 2 at 10:30
@DarrenBartrup-Cook it is coming from the other data source
– Jitesh Vacheta
Jan 2 at 10:39
@DarrenBartrup-Cook it is coming from the other data source
– Jitesh Vacheta
Jan 2 at 10:39
It would be easier to import as a datetime rather than text.
– Jason Stallard
Jan 2 at 11:58
It would be easier to import as a datetime rather than text.
– Jason Stallard
Jan 2 at 11:58
add a comment |
1 Answer
1
active
oldest
votes
1
Either add a column with =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) where B2 is replaced with your date value cell reference (The formula only works if all dates are in that 22 character format you used as an example).
or
write a macro to amend the values in that column.
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
Hi Jason, the date format i mentioned has general format , do i have to change it to any other format before applying your formula ,or should i directly use it in new column??
– Jitesh Vacheta
Jan 2 at 10:16
The formula can only be used in a new column. You'll end up with two columns, the before conversion value and the after. If you want it as a date value rather than text you can use=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,12))
– Jason Stallard
Jan 2 at 10:22
Thanks For your Answer but... I tried your formula =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) which works but for some date its showing stranger results like.. input is this ---> 12/21/2016 1:27:00 PM Out is this ---> 21/121/2016 1:27:00 PM (121 which should not be coming) there are few other results as well which are not coming as expected.
– Jitesh Vacheta
Jan 2 at 11:09
It's all about counting characters. Try '=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,11))' instead. You can then format the column in whatever date format you want as it will be a datetime value rather than text.
– Jason Stallard
Jan 2 at 11:57
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54004236%2fconverting-mmddyyyy-hhmmss-to-ddmmyyyy-hhmmss-in-excel%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
1
Either add a column with =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) where B2 is replaced with your date value cell reference (The formula only works if all dates are in that 22 character format you used as an example).
or
write a macro to amend the values in that column.
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
Hi Jason, the date format i mentioned has general format , do i have to change it to any other format before applying your formula ,or should i directly use it in new column??
– Jitesh Vacheta
Jan 2 at 10:16
The formula can only be used in a new column. You'll end up with two columns, the before conversion value and the after. If you want it as a date value rather than text you can use=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,12))
– Jason Stallard
Jan 2 at 10:22
Thanks For your Answer but... I tried your formula =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) which works but for some date its showing stranger results like.. input is this ---> 12/21/2016 1:27:00 PM Out is this ---> 21/121/2016 1:27:00 PM (121 which should not be coming) there are few other results as well which are not coming as expected.
– Jitesh Vacheta
Jan 2 at 11:09
It's all about counting characters. Try '=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,11))' instead. You can then format the column in whatever date format you want as it will be a datetime value rather than text.
– Jason Stallard
Jan 2 at 11:57
add a comment |
1
Either add a column with =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) where B2 is replaced with your date value cell reference (The formula only works if all dates are in that 22 character format you used as an example).
or
write a macro to amend the values in that column.
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
Hi Jason, the date format i mentioned has general format , do i have to change it to any other format before applying your formula ,or should i directly use it in new column??
– Jitesh Vacheta
Jan 2 at 10:16
The formula can only be used in a new column. You'll end up with two columns, the before conversion value and the after. If you want it as a date value rather than text you can use=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,12))
– Jason Stallard
Jan 2 at 10:22
Thanks For your Answer but... I tried your formula =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) which works but for some date its showing stranger results like.. input is this ---> 12/21/2016 1:27:00 PM Out is this ---> 21/121/2016 1:27:00 PM (121 which should not be coming) there are few other results as well which are not coming as expected.
– Jitesh Vacheta
Jan 2 at 11:09
It's all about counting characters. Try '=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,11))' instead. You can then format the column in whatever date format you want as it will be a datetime value rather than text.
– Jason Stallard
Jan 2 at 11:57
add a comment |
1
1
1
Either add a column with =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) where B2 is replaced with your date value cell reference (The formula only works if all dates are in that 22 character format you used as an example).
or
write a macro to amend the values in that column.
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
Either add a column with =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) where B2 is replaced with your date value cell reference (The formula only works if all dates are in that 22 character format you used as an example).
or
write a macro to amend the values in that column.
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
answered Jan 2 at 10:10
Jason StallardJason Stallard
312111
312111
Hi Jason, the date format i mentioned has general format , do i have to change it to any other format before applying your formula ,or should i directly use it in new column??
– Jitesh Vacheta
Jan 2 at 10:16
The formula can only be used in a new column. You'll end up with two columns, the before conversion value and the after. If you want it as a date value rather than text you can use=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,12))
– Jason Stallard
Jan 2 at 10:22
Thanks For your Answer but... I tried your formula =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) which works but for some date its showing stranger results like.. input is this ---> 12/21/2016 1:27:00 PM Out is this ---> 21/121/2016 1:27:00 PM (121 which should not be coming) there are few other results as well which are not coming as expected.
– Jitesh Vacheta
Jan 2 at 11:09
It's all about counting characters. Try '=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,11))' instead. You can then format the column in whatever date format you want as it will be a datetime value rather than text.
– Jason Stallard
Jan 2 at 11:57
add a comment |
Hi Jason, the date format i mentioned has general format , do i have to change it to any other format before applying your formula ,or should i directly use it in new column??
– Jitesh Vacheta
Jan 2 at 10:16
The formula can only be used in a new column. You'll end up with two columns, the before conversion value and the after. If you want it as a date value rather than text you can use=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,12))
– Jason Stallard
Jan 2 at 10:22
Thanks For your Answer but... I tried your formula =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) which works but for some date its showing stranger results like.. input is this ---> 12/21/2016 1:27:00 PM Out is this ---> 21/121/2016 1:27:00 PM (121 which should not be coming) there are few other results as well which are not coming as expected.
– Jitesh Vacheta
Jan 2 at 11:09
It's all about counting characters. Try '=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,11))' instead. You can then format the column in whatever date format you want as it will be a datetime value rather than text.
– Jason Stallard
Jan 2 at 11:57
Hi Jason, the date format i mentioned has general format , do i have to change it to any other format before applying your formula ,or should i directly use it in new column??
– Jitesh Vacheta
Jan 2 at 10:16
Hi Jason, the date format i mentioned has general format , do i have to change it to any other format before applying your formula ,or should i directly use it in new column??
– Jitesh Vacheta
Jan 2 at 10:16
The formula can only be used in a new column. You'll end up with two columns, the before conversion value and the after. If you want it as a date value rather than text you can use
=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,12))– Jason Stallard
Jan 2 at 10:22
The formula can only be used in a new column. You'll end up with two columns, the before conversion value and the after. If you want it as a date value rather than text you can use
=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,12))– Jason Stallard
Jan 2 at 10:22
Thanks For your Answer but... I tried your formula =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) which works but for some date its showing stranger results like.. input is this ---> 12/21/2016 1:27:00 PM Out is this ---> 21/121/2016 1:27:00 PM (121 which should not be coming) there are few other results as well which are not coming as expected.
– Jitesh Vacheta
Jan 2 at 11:09
Thanks For your Answer but... I tried your formula =MID(B2,4,3) & LEFT(B2,2) & RIGHT(B2,17) which works but for some date its showing stranger results like.. input is this ---> 12/21/2016 1:27:00 PM Out is this ---> 21/121/2016 1:27:00 PM (121 which should not be coming) there are few other results as well which are not coming as expected.
– Jitesh Vacheta
Jan 2 at 11:09
It's all about counting characters. Try '=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,11))' instead. You can then format the column in whatever date format you want as it will be a datetime value rather than text.
– Jason Stallard
Jan 2 at 11:57
It's all about counting characters. Try '=DATEVALUE(MID(B2,4,3) & LEFT(B2,2) & MID(B2,6,5)) + TIMEVALUE( RIGHT(B2,11))' instead. You can then format the column in whatever date format you want as it will be a datetime value rather than text.
– Jason Stallard
Jan 2 at 11:57
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54004236%2fconverting-mmddyyyy-hhmmss-to-ddmmyyyy-hhmmss-in-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
1
Is the cell formatted as text? If yes then you can use left() and mid()
– Andreas
Jan 2 at 10:01
No it is set as general , but if i set it as text as well how would i convert it as DD:MM:YYYY
– Jitesh Vacheta
Jan 2 at 10:12
How are dates arriving in Excel? Do you type them in or are they imported from another file (and which file type - csv?)
– Darren Bartrup-Cook
Jan 2 at 10:30
@DarrenBartrup-Cook it is coming from the other data source
– Jitesh Vacheta
Jan 2 at 10:39
It would be easier to import as a datetime rather than text.
– Jason Stallard
Jan 2 at 11:58