Excel date conversion

How do I?

Didn't find your answer?

I have a spreadsheet from client

Dates listed as 20217 instead of 02/02/2017

Any idea how to convert to actual date, if I do format date it comes through as absolute = 08/05/1955 in this case

 

Replies (11)

Please login or register to join the discussion.

Stepurhan
By stepurhan
26th Jun 2017 17:12

Consistent error? (e.g. 2103217 for 21 March 2017 1112216 for 11 December 2016)

If not then there is no easy way of converting them. The date you are getting at the moment is simply a day count from 1/1/1900.

Thanks (0)
Replying to stepurhan:
joe
By Smokoe Joe
26th Jun 2017 17:40

Thanks stepurhan, I knew that was what was happening, but fortunately greater minds than mine new the answer.

Thanks (0)
Replying to Smokoe Joe:
Stepurhan
By stepurhan
27th Jun 2017 10:42

Glad that their error was consistent enough that the solution (which is the sort of thing I had in mind when I asked) worked. Few things are worse than having to rekey all dates in a spreadsheet by hand.

Thanks (0)
avatar
By paulwakefield1
26th Jun 2017 17:22

If the year and month are always 2 digits (with the day being one or two digits), the following should work in a cell formatted as a date:

=DATE(2000+RIGHT(A2,2),MID(A2,LEN(A2)-3,2),LEFT(A2,LEN(A2)-4))

where A2 is the source cell

Thanks (1)
Replying to paulwakefield1:
joe
By Smokoe Joe
26th Jun 2017 17:36

Thank you so very much, that's a triple I owe you, worked a treat. Trouble with excel is don't use it enough for these things to stick, but I will save that safely.

Thanks (0)
Replying to Smokoe Joe:
avatar
By paulwakefield1
26th Jun 2017 17:39

Glad it worked.

Thanks (0)
avatar
By GW
26th Jun 2017 17:28

Excel deals with dates as days from 1st January 1900 which is why the date format gives the answer as 1955.

Do you have an easy way of identifying the months?
Can you tell if 11117 is 11th of January or 1st November?

a bit long winded but try the following:
text to columns to split the date into day, month, year in seperate cells (use fixed width to mark where the divide comes - this gets round the issue of the month being one or two digits)
use CONCATENATE to combine the split date including / between the elements.
use DATEVALUE to convert the result to excel date value
Apply the date format to the date value

Thanks (0)
Replying to GW:
joe
By Smokoe Joe
26th Jun 2017 17:38

Thanks but above worked, as suggested always mm and yy, just either d or dd

Thanks (0)
avatar
By nautical
26th Jun 2017 17:47

Simple enough. Put extra columns in. Then parse the string using the LEFT and RIGHT functions to extract day month and year. Then use DATE function to convert to a date. Real excel wizzes can probably create a function or macro to do it.

Thanks (0)
Replying to nautical:
joe
By Smokoe Joe
26th Jun 2017 17:52

You lost me after put extra columns in, I don't use excel enough to learn, because by the time I need again I will have forgotten!

Thanks (0)
avatar
By SkyBlue22
27th Jun 2017 10:56

Another option could be 'Text to Columns'. Highlight the text, go to 'data' then 'text to columns', press next in the box until the end then select 'data' instead of 'general'. Usually converts most weird date formats into a propert date.

Thanks (0)