CODEWITHSUNDEEP
exceldate
Kiwi
Kiwi

Reputation: 143

Converting a long series of numbers in excel to a date format

I have a long series of numbers in Excel that I have been given: For example '20130116074506.000265-300'

And I need these numbers to appear in a date format. Now from what I can see, the format is kind of YYYYMMDD followed by timing details.

I could do this manually, but obviously with lots of records this will t ake a long time. I have tried to format the columns in to date and custom, I have tried various formulas to change this - such as:

=--TEXT(A1,"0000-00-00") =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

But I get errors - I have spent a fair bit of time research online but have had no luck. Any help would be greatly appreciated!

Many thanks in advance. Kiwi.

Upvotes: 0

Views: 7674

Answers (1)

CuberChase
CuberChase

Reputation: 4518

I think your trouble is if you use the Left and Mid function straight into the Date function then you are getting string results from Left and Mid which the Date function doesn't like. If you convert it to Integers using Int then it should work. Assuming the input text is in cell A1 then try this:

=DATE(INT(LEFT(A1,4)), INT(MID(A1,5,2)), INT(MID(A1, 7,2)))

Or you can use the individual LEFT and MID functions to break it into cells and read those in which will convert it to a number for you.

Upvotes: 1

Related Questions