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.
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.
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.
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
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
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.
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.