10/3/2007 is the desired format
Sorry! Actually the date is January 3, 2007. I appears my data only has 1 digit for the Month but includes 2 digits for the day and 2 digits for the year.
Let me know,
It is already set to a 4 digit year.
I am an intermediate to advanced excel user. That was the first thing I tried and the result = 3/20/1928 instead of 01/03/2007.
Should I send you a sample file?
Yes that might be helpful upload it to wikisend.com, and send me the file ID.
Yes, the issue date column highlighted in yellow is the problem. I'm trying to convert to a date format of mo/da/year
above lines should be
I have 45,000 rows of data with this problem. Is there not any other way?
I tried making the dates text and then the excel function =DATEVALUE but I get an error of #name or #value.
It must be a date because it will be used to determine tax due. The data will eventually be loaded into Microsoft access.
There are several files. I will send you one.
Does it work to use the txt to columns function and separate the last 2 digits of the year? Those could be sorted and manually keyed without too much time. But then I don't know how to put them back together into the date format.
I can try that but will first have to separate the year from month and day. If I do a find and replace on all 06 it will also change June dates to 2006 and the 6th day of the month to 06.
Once I separate and do the find and replace, can I then format to text, cancatenate back together and then use the =datevalue function to convert text to date?
I have a computer with excel 2007 - any other options there?
Is there another expert who might have another idea?
Thanks for your efforts, but yes, I would like to try someone else please.
Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated! If you have any other questions, please ask me or reply to me – I’ll be happy to respond. Thank you!Best regards,Jess
YEAH!! What a simple fix. Greatly appreciated!!!