Forum Discussion
Date format
I am having an issue formatting dates. Usually when I enter a date, it changes automatically to the selected format I want straight away, but today it's not working.
I want the date formatted dd-mmm-yyyy - but when I try and enter the date 29May1724, nothing changes. I have tried entering the date every which way
I have tried everything, but nothing has worked
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.
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)
