At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel 2010 - Date arithmetic BEFORE 1900 (1774 up to 1900)
Posted on 2013-06-02
Hi:
I need to compute the number of days/months/years on a group of dates from 1700 to 1900. I've got about 100 dates I need to compute durations on.
How can I do this?
Example: October 7, 1779 to January 13, 1800
How many days/months/years.
I need to compute the number of days/months/years on a group of dates from 1700 to 1900. I've got about 100 dates I need to compute durations on.
How can I do this?
Example: October 7, 1779 to January 13, 1800
How many days/months/years.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
3
7 Comments
Active today
VBA supports dates before 1900, but Excel does not. So, you could roll your own function in VBA to do this.
It would be much easier to download John Walkenbach's Power Utility Pak add-in, though: http://spreadsheetpage.com/index.php/pupv7/home/
It includes functions that handle pre-1900 dates.
It would be much easier to download John Walkenbach's Power Utility Pak add-in, though: http://spreadsheetpage.com/index.php/pupv7/home/
It includes functions that handle pre-1900 dates.
Active today
Try adding 400 years to all dates with a formula like this
=VALUE(SUBSTITUTE(A1,RIGHT
and then you can get the date difference.
=VALUE(SUBSTITUTE(A1,RIGHT
and then you can get the date difference.
I created a few UDFs which you can test on the attached workbook. They require that the dates are entered as Text on the worksheet. You can either format the cells as Text or precede your entries with an apostrophe which has the same effect. If you wish to install the functions in your workbook I suggest that you drag the entire module 'DateMan' to your own project and then save your file in XLSM format.
You can change the sequence of year, month and day to your liking. Just change the sequence of the Nds enumerations to match what you enter in the cells you want evaluated. You can also change the Date separator, just below the enum just mentioned, before the actual code starts.
There are samples of how to call the functions on the worksheet.
These are the functions available:-
YEARDIFF, MONTHDIFF, DAYDIFF - These functions were created to allow you to calculate the years, months and days between two dates. They will work on modern days as well as on historical ones. Note that the MONTHDIFF can be used to count the entire difference in months. Please observe how it is deployed in the worksheet.
DATESERIAL / HDATESERIAL - The former is designed as a UDF to call the latter which is designed as VBA function. The HDATESERIAL gives a number to each day since January 1, 0000, also converting dates entered as Text. This would enable you to sort your entries by date.
Finally, there is HWEEKDAY which works very much like VBA's own WEEKDAY function, largely because it utilizes that function. If Sunday is the first day of the week (the WEEKDAY function allows you to select your first day of the week), Monday would be the second. Hence, the result you see in the worksheet confirms that Charlemagne was crowned on a Tuesday, on Christmas Day in the year 800. Perhaps this function will be useful to you in some way.
EXX-130602-Dates-from-0000.xlsm
You can change the sequence of year, month and day to your liking. Just change the sequence of the Nds enumerations to match what you enter in the cells you want evaluated. You can also change the Date separator, just below the enum just mentioned, before the actual code starts.
There are samples of how to call the functions on the worksheet.
These are the functions available:-
YEARDIFF, MONTHDIFF, DAYDIFF - These functions were created to allow you to calculate the years, months and days between two dates. They will work on modern days as well as on historical ones. Note that the MONTHDIFF can be used to count the entire difference in months. Please observe how it is deployed in the worksheet.
DATESERIAL / HDATESERIAL - The former is designed as a UDF to call the latter which is designed as VBA function. The HDATESERIAL gives a number to each day since January 1, 0000, also converting dates entered as Text. This would enable you to sort your entries by date.
Finally, there is HWEEKDAY which works very much like VBA's own WEEKDAY function, largely because it utilizes that function. If Sunday is the first day of the week (the WEEKDAY function allows you to select your first day of the week), Monday would be the second. Hence, the result you see in the worksheet confirms that Charlemagne was crowned on a Tuesday, on Christmas Day in the year 800. Perhaps this function will be useful to you in some way.
EXX-130602-Dates-from-0000.xlsm
Active today
Faustulus,
I have not checked all of your UDFs, but your HDATESERIAL function has a logical flaw in how it handles leap years.
Specifically, a year ending in "00" is only a leap year if it is divisible by 400. Your code has it backward: your code makes the years divisible by 400 a standard year, and the "00" years that are not evenly divisible by 400 leap years. (Of course, Excel treats 1900 as a leap year--incorrectly--while VBA does not.)
Also, keep in mind that when the Gregorian calendar was adopted, there was a 10-day skip: 1582-10-04 was immediately followed by 1582-10-15. Your UDF makes no adjustment for that, so any date calculation that spans that adjustment will be off by 10 days.
:)
Patrick
I have not checked all of your UDFs, but your HDATESERIAL function has a logical flaw in how it handles leap years.
Specifically, a year ending in "00" is only a leap year if it is divisible by 400. Your code has it backward: your code makes the years divisible by 400 a standard year, and the "00" years that are not evenly divisible by 400 leap years. (Of course, Excel treats 1900 as a leap year--incorrectly--while VBA does not.)
Also, keep in mind that when the Gregorian calendar was adopted, there was a 10-day skip: 1582-10-04 was immediately followed by 1582-10-15. Your UDF makes no adjustment for that, so any date calculation that spans that adjustment will be off by 10 days.
:)
Patrick
Patrick,
It's all that late night hobbying that produces the errors. :-)
The wrong treatment of leap years in my procedure HDATESERIAL isn't one of them, though. Please take another look. Perhaps I am blind on that eye even in the light of morning.
The Gregorian skip is another can of worms. Thank you for pointing this out to me. I have added the function 'GregorianSkip' which makes this adjustment adjustable in view of the different dates on which different countries made different changes to their then calendars.
The attached version of the same file previously uploaded has one correction, a number of improvements - in addition to the above - and doesn't feature the HWEEKDAY function any more. In order to determine the weekday of Charlemagne's coronation one may not be able to work backward in leaps of 7 days from today because the precise number of intervening days is subject to some doubt where sovereigns, both wise, haughty and practical, gave or took a few unaccounted days at their whim (or the advice of their tax consultants) in order to stay abreast of science's latest enlightenment during the otherwise dark ages.
EXX-130603-Dates-from-0000.xlsm
It's all that late night hobbying that produces the errors. :-)
The wrong treatment of leap years in my procedure HDATESERIAL isn't one of them, though. Please take another look. Perhaps I am blind on that eye even in the light of morning.
The Gregorian skip is another can of worms. Thank you for pointing this out to me. I have added the function 'GregorianSkip' which makes this adjustment adjustable in view of the different dates on which different countries made different changes to their then calendars.
The attached version of the same file previously uploaded has one correction, a number of improvements - in addition to the above - and doesn't feature the HWEEKDAY function any more. In order to determine the weekday of Charlemagne's coronation one may not be able to work backward in leaps of 7 days from today because the precise number of intervening days is subject to some doubt where sovereigns, both wise, haughty and practical, gave or took a few unaccounted days at their whim (or the advice of their tax consultants) in order to stay abreast of science's latest enlightenment during the otherwise dark ages.
EXX-130603-Dates-from-0000.xlsm
Active today
For my prior comment, I used the following test cases...
=HDATESERIAL(1800,1,1) ----> 657447
=HDATESERIAL(1800,3,1) ----> 657507
Diff of 60, implying there is a Feb 29 (thus a leap year)
=HDATESERIAL(1900,1,1) ----> 693972
=HDATESERIAL(1900,3,1) ----> 694032
Diff of 60, implying there is a Feb 29 (thus a leap year)
=HDATESERIAL(2000,1,1) ----> 730496
=HDATESERIAL(2000,3,1) ----> 730555
Diff of 59, implying there is no Feb 29 (thus not a leap year)
=HDATESERIAL(1800,1,1) ----> 657447
=HDATESERIAL(1800,3,1) ----> 657507
Diff of 60, implying there is a Feb 29 (thus a leap year)
=HDATESERIAL(1900,1,1) ----> 693972
=HDATESERIAL(1900,3,1) ----> 694032
Diff of 60, implying there is a Feb 29 (thus a leap year)
=HDATESERIAL(2000,1,1) ----> 730496
=HDATESERIAL(2000,3,1) ----> 730555
Diff of 59, implying there is no Feb 29 (thus not a leap year)
Yes, Patrick, it was the blindness. Thank you for your assistance.
I attach another version of the workbook which - I guarantee - has fewer errors now than it had this morning.
With three quarters of a million days gone by there are just too many ways to test and, it seems, too many ways of erring, too. I will keep on correcting while some one finds errors.
EXX-130603-Dates-from-0000.xlsm
I attach another version of the workbook which - I guarantee - has fewer errors now than it had this morning.
With three quarters of a million days gone by there are just too many ways to test and, it seems, too many ways of erring, too. I will keep on correcting while some one finds errors.
EXX-130603-Dates-from-0000.xlsm
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 1 hour left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.