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
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
VBA will support negative numbers as dates, but Excel worksheets will not.

Your workaround of adding 200 years is fine, as long as you take note of the fact that Excel worksheet recognizes February 29, 1900 as a Leap Day (to be compatible with Lotus 123), while VBA knows that there is no Leap Day that year.

Entertaining story describing the origin of VBA in Excel. Of particular note is the discussion on dates.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamCommented:
@WeThotUWasAToad

there is a free add-in by John Walkenbach that solves your problem, check the link below.


http://j-walk.com/ss/excel/files/xdate.htm
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Generally, you should only increment in groups of 28 years. That's because, with the leap days, if 1 January 2000 is a Saturday (which it is), then so will 1 January 2028, and 1 January 2056. You can't guarantee it if you don't jump to the similar cycle in the future.

Just to add to byundt's point, February 29, 1900 did not in fact exist, so VBA is right and Excel is wrong.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Rob HensonFinance AnalystCommented:
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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
Rob HensonFinance AnalystCommented:
Indeed there was, apologies I rearranged what I was writing and ended up writing it wrong.

Thanks for the correction.
0
WeThotUWasAToadAuthor Commented:
Thanks for the insights & suggestions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.