Forum Discussion
Need help regarding DATE FORMAT
Is this a one-time import in the form of a csv file? If so, a very simple resolution that occurs to me is to go ahead and import them all with whatever happens to them. So 5/19/1942 becomes 5/19/2042. Get them all into you current Excel database.
Then subtract 100 years. And make the resulting field the one you keep.
I sometimes encounter different problems importing from (say) bank statements. Some financial institutions allow exports in Excel, so they come in ready to use. Others use csv. Still others may use Excel's format for the overall spreadsheet but (annoyingly) store the dates as text rather than serial numbers formatted as dates. So I've developed formulas to handle each of those kinds of circumstances, which can involve parsing whatever it is into month, day, year and then reassembling them through the DATE function. You might try that, if you are comfortable with such functions.
But another question: I'm actually kind of surprised that the dates are importing that way. Could you upload a sample of the way the dates come in....and maybe a sample of the csv file? (The actual files, just devoid of any names of the people who belong to those birthdays)...
mathetes Thanks. I'm not "importing". I get the original csv, then I have to add some information into the document and then upload it to a database. I have to do this every month when I get an updated list. there are over 40,000 names and I have to keep the file under 8mb which means I usually have to divide the file into 2.
When I make a copy of the original csv, the date changes. Some of the dates are correctly in 1900.
Problem has something to do with dates that are more recent than 1929.
First: please, immediately, go in to your last two posts and delete the files you posted. I had asked that you take the names out....that's very private info (DOB can be used by hackers for all kinds of nefarious purposes)... Just delete any identifiable columns like names (addresses too if they're in there; certainly SSNs or other ID codes) All I wanted to see was how the dates appear.
You can accomplish that by clicking on the litle downward pointing arrow in the top right corner of the post...then select "Edit"
Post the "anonomyzed" files after
- Thank you!!! OMG. I thought I was posting screenshots. Not sure how that happened. Anyway, I think I'm making progress having done a bit more research. And it has something to do with showing the full format of the date. If it's in the short format, the date changes, maybe? But the format in the original csv only shows two numbers for the year, even though the full year is there and correct in the 1900s. Seems when I copy the file, the date changes. So how do I copy it so that it shows four numbers for the century?
https://docs.microsoft.com/en-us/office/troubleshoot/excel/two-digit-year-numbers
