Forum Discussion
Need help regarding DATE FORMAT
I have a csv with dates of birth in the 1900s. When I copy the information it changes the date from 1900x to 2000. I need to know how to keep the dates as 1900 - as many options as you can suggest -- how to lock the dates, how to change the dates -- anything please.. I tried changing them to text and that didn't work. I asked before but no-one answered. Please help. I'm using Mac MSOffice 2019.
Thanks
10 Replies
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
