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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
byundtConnect With a Mentor Commented:
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
 
ProfessorJimJamConnect With a Mentor Commented:
@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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.