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)
No comments:
Post a Comment