Forum Discussion
Date format
If you really are going for the year 1724, that is the cause. That date doesn't exist in the Excel universe.. So far as Excel is concerned, the Big Bang occurred on 1/1/1900. All dates are serial numbers beginning with that date. See this link.
So depending on what you are creating, you're going to need to find a different solution. Perhaps store the day, month and year as separate columns altogether. If 1724 isn't actually the date you want, then please come back with a more complete and clear description of what you're seeking to do.
mathetes I'm helping a friend create a spreadsheets of Popes, and she wants to enter in the date their term started, and the date it ended.
So she is going back to the Apostle Peter, presumptively the first pope?
In any event, normal Excel dates (and associated date functions) will not work for dates prior to 1/1/1900. If your friend wants to do any date math--calculate average years in the position, for example--then you're going to need to store the particulars in discrete columns. Otherwise, if she's just planning to print listings showing the information, the date of a Pope's investiture or death (etc) can be stored as text.
As a suggestion--again if no math is planned--you and she might find creating a table in Microsoft Word to be every bit as functional, perhaps more so. I suggest that because Word would offer more space in any given cell for descriptions of papal accomplishments, controversies, conflict, or other contemporaneous historical events. In my experience, people often assume Excel is suited solely because it has those nice rows and columns, but table in Word offer the same with more capabilities for manipulation of the words themselves.
As noted, if Excel is going to be used for purposes that Excel, per se, offers, then by all means continue to use Excel. I have in mind such things as:
- calculations (as noted earlier)
- data sorting
- data extraction (e.g., to list all Italian born popes, or whatever)
mathetes she is adamant she wants it in Excel, so she can use it for data entry, editing, filters and sorting.
I am now researching how to make this happen. The basic spreadsheet I can do, but it's the working out how to enter pre1900 dates is beyond me.