Convert a date & time text string to a date?
A program I use exports its measurement dates as MM/DD/YYYY HH:MM and excel sees it as text. How do I convert that so that excel recognizes it as a date? Also, the length of the text string varies (e.g. 11/24/2018 19:39 and 8/7/2018 8:45).
Coworker helped me write a solution, ended up have to use RIGHT and LEFT to pull the values out and then convert them. There's probably a cleaner way to do this (and if you can figure it out, please let me know!), but it took 6 more columns:
Date, Month, Day, Year, Time, Combined; where initial date text string is in column B2.
Date column formula: J2=LEFT(B2,SEARCH(" ",B2)-1)
Month column formula: K2=IF(SEARCH("/",J2)=2,LEFT(J2,1),LEFT(J2,2))
Day column formula: L2=RIGHT(LEFT(J2,SEARCH("/",J2,4)-1),(SEARCH("/",J2,4)-SEARCH("/",J2)-1))
Year column formula: M2=RIGHT(J2,4)
Time column formula: N2=TRIM(RIGHT(B2,5))
Combined column formula: O2=DATE(M2,K2,L2)+TIMEVALUE(N2)
microsoft-excel
asked Feb 19 at 23:43
swastleswastle
62
|
show 1 more comment
A program I use exports its measurement dates as MM/DD/YYYY HH:MM and excel sees it as text. How do I convert that so that excel recognizes it as a date? Also, the length of the text string varies (e.g. 11/24/2018 19:39 and 8/7/2018 8:45).
Coworker helped me write a solution, ended up have to use RIGHT and LEFT to pull the values out and then convert them. There's probably a cleaner way to do this (and if you can figure it out, please let me know!), but it took 6 more columns:
Date, Month, Day, Year, Time, Combined; where initial date text string is in column B2.
Date column formula: J2=LEFT(B2,SEARCH(" ",B2)-1)
Month column formula: K2=IF(SEARCH("/",J2)=2,LEFT(J2,1),LEFT(J2,2))
Day column formula: L2=RIGHT(LEFT(J2,SEARCH("/",J2,4)-1),(SEARCH("/",J2,4)-SEARCH("/",J2)-1))
Year column formula: M2=RIGHT(J2,4)
Time column formula: N2=TRIM(RIGHT(B2,5))
Combined column formula: O2=DATE(M2,K2,L2)+TIMEVALUE(N2)
microsoft-excel
asked Feb 19 at 23:43
swastleswastle
62
How is your data organized? If all of these dates are in one column, you could change the formatting of the column to a date format and then use "Text to Columns" to update all of the values/formatting. If that matches your data set, let me know and I'll post the steps to do that.
– Rey Juna
Feb 20 at 0:50
All of my data is in one column, and I've tried the date format and 'Text to Columns' and it didn't work unfortunately. I'm on a work computer that won't let me change the date short format (in the adjust date/time settings) so the datevalue function returns '#VALUE!' error too.
– swastle
Feb 20 at 16:48
Excel should recognize those strings as dates, depending on how the program I use delivers them. What are your windows regional settings for date? DMY or MDY?
– Ron Rosenfeld
Feb 20 at 18:40
Regional settings are YMD on my computer. If it makes any difference, the program exports the data as a csv.
– swastle
Feb 20 at 18:51
And what is the displayed value if you format that Cell as General? If the value does not change to a long number with a decimal, the simplest solution for you would be to IMPORT the csv rather than just OPENing it. Depending on your version of Excel, this will give you the opportunity to tell Excel the date format of your file being imported, and it should then convert it properly
– Ron Rosenfeld
Feb 20 at 19:08
|
show 1 more comment
A program I use exports its measurement dates as MM/DD/YYYY HH:MM and excel sees it as text. How do I convert that so that excel recognizes it as a date? Also, the length of the text string varies (e.g. 11/24/2018 19:39 and 8/7/2018 8:45).
Coworker helped me write a solution, ended up have to use RIGHT and LEFT to pull the values out and then convert them. There's probably a cleaner way to do this (and if you can figure it out, please let me know!), but it took 6 more columns:
Date, Month, Day, Year, Time, Combined; where initial date text string is in column B2.
Date column formula: J2=LEFT(B2,SEARCH(" ",B2)-1)
Month column formula: K2=IF(SEARCH("/",J2)=2,LEFT(J2,1),LEFT(J2,2))
Day column formula: L2=RIGHT(LEFT(J2,SEARCH("/",J2,4)-1),(SEARCH("/",J2,4)-SEARCH("/",J2)-1))
Year column formula: M2=RIGHT(J2,4)
Time column formula: N2=TRIM(RIGHT(B2,5))
Combined column formula: O2=DATE(M2,K2,L2)+TIMEVALUE(N2)
microsoft-excel
asked Feb 19 at 23:43
swastleswastle
62
A program I use exports its measurement dates as MM/DD/YYYY HH:MM and excel sees it as text. How do I convert that so that excel recognizes it as a date? Also, the length of the text string varies (e.g. 11/24/2018 19:39 and 8/7/2018 8:45).
Coworker helped me write a solution, ended up have to use RIGHT and LEFT to pull the values out and then convert them. There's probably a cleaner way to do this (and if you can figure it out, please let me know!), but it took 6 more columns:
Date, Month, Day, Year, Time, Combined; where initial date text string is in column B2.
Date column formula: J2=LEFT(B2,SEARCH(" ",B2)-1)
Month column formula: K2=IF(SEARCH("/",J2)=2,LEFT(J2,1),LEFT(J2,2))
Day column formula: L2=RIGHT(LEFT(J2,SEARCH("/",J2,4)-1),(SEARCH("/",J2,4)-SEARCH("/",J2)-1))
Year column formula: M2=RIGHT(J2,4)
Time column formula: N2=TRIM(RIGHT(B2,5))
Combined column formula: O2=DATE(M2,K2,L2)+TIMEVALUE(N2)
microsoft-excel
microsoft-excel
asked Feb 19 at 23:43
swastleswastle
62
asked Feb 19 at 23:43
swastleswastle
62
edited Feb 20 at 17:14
swastle
asked Feb 19 at 23:43
swastleswastle
62
asked Feb 19 at 23:43
swastleswastle
62
asked Feb 19 at 23:43
swastleswastle
62
62
How is your data organized? If all of these dates are in one column, you could change the formatting of the column to a date format and then use "Text to Columns" to update all of the values/formatting. If that matches your data set, let me know and I'll post the steps to do that.
– Rey Juna
Feb 20 at 0:50
All of my data is in one column, and I've tried the date format and 'Text to Columns' and it didn't work unfortunately. I'm on a work computer that won't let me change the date short format (in the adjust date/time settings) so the datevalue function returns '#VALUE!' error too.
– swastle
Feb 20 at 16:48
Excel should recognize those strings as dates, depending on how the program I use delivers them. What are your windows regional settings for date? DMY or MDY?
– Ron Rosenfeld
Feb 20 at 18:40
Regional settings are YMD on my computer. If it makes any difference, the program exports the data as a csv.
– swastle
Feb 20 at 18:51
And what is the displayed value if you format that Cell as General? If the value does not change to a long number with a decimal, the simplest solution for you would be to IMPORT the csv rather than just OPENing it. Depending on your version of Excel, this will give you the opportunity to tell Excel the date format of your file being imported, and it should then convert it properly
– Ron Rosenfeld
Feb 20 at 19:08
|
show 1 more comment
How is your data organized? If all of these dates are in one column, you could change the formatting of the column to a date format and then use "Text to Columns" to update all of the values/formatting. If that matches your data set, let me know and I'll post the steps to do that.
– Rey Juna
Feb 20 at 0:50
All of my data is in one column, and I've tried the date format and 'Text to Columns' and it didn't work unfortunately. I'm on a work computer that won't let me change the date short format (in the adjust date/time settings) so the datevalue function returns '#VALUE!' error too.
– swastle
Feb 20 at 16:48
Excel should recognize those strings as dates, depending on how the program I use delivers them. What are your windows regional settings for date? DMY or MDY?
– Ron Rosenfeld
Feb 20 at 18:40
Regional settings are YMD on my computer. If it makes any difference, the program exports the data as a csv.
– swastle
Feb 20 at 18:51
And what is the displayed value if you format that Cell as General? If the value does not change to a long number with a decimal, the simplest solution for you would be to IMPORT the csv rather than just OPENing it. Depending on your version of Excel, this will give you the opportunity to tell Excel the date format of your file being imported, and it should then convert it properly
– Ron Rosenfeld
Feb 20 at 19:08
How is your data organized? If all of these dates are in one column, you could change the formatting of the column to a date format and then use "Text to Columns" to update all of the values/formatting. If that matches your data set, let me know and I'll post the steps to do that.
– Rey Juna
Feb 20 at 0:50
How is your data organized? If all of these dates are in one column, you could change the formatting of the column to a date format and then use "Text to Columns" to update all of the values/formatting. If that matches your data set, let me know and I'll post the steps to do that.
– Rey Juna
Feb 20 at 0:50
All of my data is in one column, and I've tried the date format and 'Text to Columns' and it didn't work unfortunately. I'm on a work computer that won't let me change the date short format (in the adjust date/time settings) so the datevalue function returns '#VALUE!' error too.
– swastle
Feb 20 at 16:48
All of my data is in one column, and I've tried the date format and 'Text to Columns' and it didn't work unfortunately. I'm on a work computer that won't let me change the date short format (in the adjust date/time settings) so the datevalue function returns '#VALUE!' error too.
– swastle
Feb 20 at 16:48
Excel should recognize those strings as dates, depending on how the program I use delivers them. What are your windows regional settings for date? DMY or MDY?
– Ron Rosenfeld
Feb 20 at 18:40
Excel should recognize those strings as dates, depending on how the program I use delivers them. What are your windows regional settings for date? DMY or MDY?
– Ron Rosenfeld
Feb 20 at 18:40
Regional settings are YMD on my computer. If it makes any difference, the program exports the data as a csv.
– swastle
Feb 20 at 18:51
Regional settings are YMD on my computer. If it makes any difference, the program exports the data as a csv.
– swastle
Feb 20 at 18:51
And what is the displayed value if you format that Cell as General? If the value does not change to a long number with a decimal, the simplest solution for you would be to IMPORT the csv rather than just OPENing it. Depending on your version of Excel, this will give you the opportunity to tell Excel the date format of your file being imported, and it should then convert it properly
– Ron Rosenfeld
Feb 20 at 19:08
And what is the displayed value if you format that Cell as General? If the value does not change to a long number with a decimal, the simplest solution for you would be to IMPORT the csv rather than just OPENing it. Depending on your version of Excel, this will give you the opportunity to tell Excel the date format of your file being imported, and it should then convert it properly
– Ron Rosenfeld
Feb 20 at 19:08
|
show 1 more comment
1 Answer
1
active
oldest
votes
Use datevalue() & timevalue().
=DATEVALUE(B5)+TIMEVALUE((RIGHT(TRIM(B2),5))
Hope it solves.
p/s : please share if it doesn't work. ( :
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
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%2f1407645%2fconvert-a-date-time-text-string-to-a-date%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
Use datevalue() & timevalue().
=DATEVALUE(B5)+TIMEVALUE((RIGHT(TRIM(B2),5))
Hope it solves.
p/s : please share if it doesn't work. ( :
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
add a comment |
Use datevalue() & timevalue().
=DATEVALUE(B5)+TIMEVALUE((RIGHT(TRIM(B2),5))
Hope it solves.
p/s : please share if it doesn't work. ( :
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
add a comment |
Use datevalue() & timevalue().
=DATEVALUE(B5)+TIMEVALUE((RIGHT(TRIM(B2),5))
Hope it solves.
p/s : please share if it doesn't work. ( :
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
Use datevalue() & timevalue().
=DATEVALUE(B5)+TIMEVALUE((RIGHT(TRIM(B2),5))
Hope it solves.
p/s : please share if it doesn't work. ( :
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
answered Feb 21 at 7:33
p._phidot_p._phidot_
691412
691412
add a comment |
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%2f1407645%2fconvert-a-date-time-text-string-to-a-date%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
How is your data organized? If all of these dates are in one column, you could change the formatting of the column to a date format and then use "Text to Columns" to update all of the values/formatting. If that matches your data set, let me know and I'll post the steps to do that.
– Rey Juna
Feb 20 at 0:50
All of my data is in one column, and I've tried the date format and 'Text to Columns' and it didn't work unfortunately. I'm on a work computer that won't let me change the date short format (in the adjust date/time settings) so the datevalue function returns '#VALUE!' error too.
– swastle
Feb 20 at 16:48
Excel should recognize those strings as dates, depending on how the program I use delivers them. What are your windows regional settings for date? DMY or MDY?
– Ron Rosenfeld
Feb 20 at 18:40
Regional settings are YMD on my computer. If it makes any difference, the program exports the data as a csv.
– swastle
Feb 20 at 18:51
And what is the displayed value if you format that Cell as General? If the value does not change to a long number with a decimal, the simplest solution for you would be to IMPORT the csv rather than just OPENing it. Depending on your version of Excel, this will give you the opportunity to tell Excel the date format of your file being imported, and it should then convert it properly
– Ron Rosenfeld
Feb 20 at 19:08