CODEWITHSUNDEEP
excelexcel-formula
user1099123
user1099123

Reputation: 6675

How do I convert this date text to a date field in excel?

I have a date that looks like this text. 17-OCT-16 03.24.11.000000000 AM

I need to format that text into a date that I can then manipulate. Ultimate I want to transform that time to millis from epoch but I'm not sure if that's even possible

Upvotes: 0

Views: 35

Answers (1)

lllpratll
lllpratll

Reputation: 378

Its definitely possible, you can make use of DATEVALUE and TIMEVALUE function which convert a time or date in string format to date/time format:

=DATEVALUE(LEFT(A1,9)) + TIMEVALUE(SUBSTITUTE(MID(A1,11,8), ".", ":")&MID(A1, 19, 9))

This will give you a single number (42660.1417939815) in this case which is the number of days since 1/1/1900 (including milliseconds)

It should just be some simple maths to get total milliseconds

Upvotes: 2

Related Questions