1

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)

Improve this question
6
  • 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, 2019 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, 2019 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, 2019 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, 2019 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, 2019 at 19:08

1 Answer 1

Reset to default
0

Use datevalue() & timevalue().

=DATEVALUE(B5)+TIMEVALUE((RIGHT(TRIM(B2),5))

Hope it solves.

p/s : please share if it doesn't work. ( :

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .