WeThotUWasAToad
asked on
Using dates prior to 1900 in Excel
Hello,
Since the Windows date system begins with # 1 on January 1, 1900, can negative numbers be used for dates in the 1800's and earlier? If not, is there a workaround or add-on which enables earlier dates to be used in Excel charts & formulas, etc?
I began adding 2000 to all years whenever dates prior to 1900 are present in a range of values. Then, I would just need to remember to subtract 2000 whenever the date was to be displayed. I think that's accurate considering the rules for leap year but doing that is a couple of extra steps and it would be nice if there is a simpler way to handle that situation.
Thanks
Since the Windows date system begins with # 1 on January 1, 1900, can negative numbers be used for dates in the 1800's and earlier? If not, is there a workaround or add-on which enables earlier dates to be used in Excel charts & formulas, etc?
I began adding 2000 to all years whenever dates prior to 1900 are present in a range of values. Then, I would just need to remember to subtract 2000 whenever the date was to be displayed. I think that's accurate considering the rules for leap year but doing that is a couple of extra steps and it would be nice if there is a simpler way to handle that situation.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There was no 29 Feb 2000 either.
As you are no doubt aware, leap years occur every 4 years except for centenary years.
The addition of a day every 4 years would add 25 days per century but the movement of the planets only needs 24, hence no leap year in centenary years.
Thanks
Rob
As you are no doubt aware, leap years occur every 4 years except for centenary years.
The addition of a day every 4 years would add 25 days per century but the movement of the planets only needs 24, hence no leap year in centenary years.
Thanks
Rob
You need a new 2000 calendar - There was a 29 Feb 2000!
The movement on the planets actually needs about 24.25, so there is a leap day every 400 years.
The movement on the planets actually needs about 24.25, so there is a leap day every 400 years.
Indeed there was, apologies I rearranged what I was writing and ended up writing it wrong.
Thanks for the correction.
Thanks for the correction.
ASKER
Thanks for the insights & suggestions.
Just to add to byundt's point, February 29, 1900 did not in fact exist, so VBA is right and Excel is wrong.