Forum Discussion
Need help regarding DATE FORMAT
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
In the dialog box for formatting numbers you can selec, down at the bottom of this dialog box, the selection for "More Number Formats"
And then pick the format that shows a year in 4 digits, and apply that to all the birthdates.
What you'll discover, I think---is that basically any birthdates in the early part of the last century (from 1913 up through 1929) were increased by a century. In fact, I see no birthdates between 1995 and 2013 on your sheet (because I'd already opened it, it still is on my computer--I'll delete it after we're done and promise I won't use anything fro it)...
My guess is that any birthdates between 1901 and 1929 would be there as 2001 on. For what it's worth your sheet does show one person as born in 2013 (my father's birth year as it happens.) So that's either a person who's only 7 or a person who is 107! Given your knowledge of what this list is about, is the former possible? The latter is unlikely, but possible. And it does show that person as enrolled in Medicare (which I think precludes a seven year old)
ANyway, I sorted the database in order by Date of Birth, which is how I was able to see the full sequence IN order..... Once you've done that it should be relatively easy to use a formula to correct for those needing it.
I would want to know more about the process you're going through. You say you're "not importing" so maybe you understand that word differently than I do. My usage was just to mean "importing a csv file into Excel" -- i.e., it begins as something outside the Excel universe and is brought in. What I didn't understand from you is why you refer to copying the cells over.... because when you "import" (my definition) a csv file into Excel, you can then just do a "Save As...." and convert it to Excel. There's no "copy" step in that sequence.
None of this directly addresses your date quandary. My guess regarding that is there may be a default setting under "Excel".... "Preferences" for how to deal with dates, whether or not to convert them to the current century.
