Monday, November 23, 2015

microsoft excel - 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)

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...