Hello my most xlegant fans! It's been a long time since my last post. I've been pursuing other occupations: reading history, writing family history, turning bowls, and many more. Here are some of my results on the learning curve, made from a hickory tree and an ash tree that were cut down last year:
As things often go, I am cycling back through my interests, and have recently landed on the problem of working in Excel with dates prior to 1900. Excel recognizes Jan 1, 1900 as day 1, counts up from there, and throws an error in your face if you dare to presume that any earlier date exists. Is it a problem?
Anyone interested in family or general history frequently encounters dates before 1900. Genealogy really demands the use of special software that handles ancient dates, but there are still cases where Excel can add value, such as the timeline I previously presented, or general historical research. I believe there is a niche for a simple solution to extend the date horizon back to earlier centuries.
Objective
Note I said "simple". There are many complexities involved in working with old dates, such as the switch from Julian to Gregorian calendar, resulting in "double dates," leap year rules, and BC dates. I don't mean to tackle such complexities, and I don't need absolute accuracy. My objective here is to make a VBA-free solution that's good enough for the great majority of uses. (For those interested, there is a good discussion and VBA solution for early dates written by Charley Kyd available here.)
This is a clear case for LAMBDA, which will provide similar user benefit to VBA without the concerns. LAMBDA has some of its own concerns, including portability, which I have discussed in previous posts along with some solutions. LAMBDA is an outgrowth of Excel, which for a large population of users is still challenging enough as a second language, and reason enough to avoid VBA as a third language.
To promote good use of LAMBDA, I'll apply my emerging standards:
use namespace and function name to make it easy to find
minimize required arguments
include optional arguments for flexibility
define default values for all optional arguments
use process variables liberally to make the logic easy to follow
try to handle most errors
under-promise and over-deliver
I'll develop two functions: one to calculate the difference between two dates, and one to calculate one date from another.
The Offset Concept
The Excel calendar has a much longer forward horizon than backward. The highest date possible is December 31, 9999 (YTK!). That means there are about 8000 years of valid future dates, compared to 122 years of valid past dates. This is the reverse of what would seem to be a useful range, since we know about and work with the past a lot more than the future. To get around this we can use an offset to temporarily shift the valid range backward, to enable functions to work on more of the past. I'll use 1900 as the offset, enabling calculation of dates all the way back to 0 AD.
Example: George Washington's age at the signing of the Declaration of Independence
Input: from date = Feb 22, 1732; to date = July 4, 1776
Solution:
enter both dates in Excel (which accepts them as text)
split the text into two parts: year = RIGHT(textdate,4); what's left is month and day
add the offset to the year and convert to real dates (see below)
calculate the difference between the dates (= 44.36 years)
To split, offset and convert invalid dates to real dates (steps 2 and 3 combined):
date1 = DATEVALUE(LEFT(from,LEN(from)-4) & RIGHT(from,4)+offset) = Feb 22, 3632
date2 = DATEVALUE(LEFT(to,LEN(to)-4) & RIGHT(to,4)+offset) = July 4, 3676
For the sake of simplicity, I accept the requirement that input dates must be in a recognizable date format and must end with 4-digit year. That means that in the very rare case of working with first millennium dates, the year must be padded with zeroes. The offset date range may have a different number of leap years than the original date range, but it will be negligible in effect.
Solution
Difference between dates
This is a modification of a previous LAMBDA, "my.date.dif", so to start, I make a copy of it in AFE (see previous posts) and rename it "my.date.xdif". Then I can start the process as laid out above.
Inputs
This would seem a simple arithmetic problem, since = to-from, or age = today-birthdate, but Excel will only give the number of days between. If you want any other time unit you must calculate it from days. This and other options result in the following list of input arguments:
from date is required.
[to] defaults to today if not entered.
various time units are set by [type]; years, months, weeks, days or ymd.
the output can have a [label]: none, short (y, m, etc.) or long (yrs, mos, etc.).
the output will be rounded down to an optional number of [decimals].
the default space between numbers and labels can be removed by [nospace].
The comment explaining the inputs is added in Name Manager, not in AFE.
This is the only way for the help to appear when entering the formula in the sheet.
Logic
Below is the code with comments added in blue.
my.date.xdif = LAMBDA(from, [to], [type], [label], [decimals], [nospace], LET( note1, "+/- date dif in 5 types: 1=y; 2=m; 3=w; 4=d; 5=ymd", note2, "label none,1,2: 1=y,m,w,d; 2=yrs,mos,wks,days", note3, "decimals for types 1-4", note4, "text dates must be in valid date format ending in yyyy", offset, 1900, set offset constant textfr,TEXT(from,"mmm d, yyyy"), convert "from" to text textto,TEXT(IF(LEN(to), to, TODAY()), (has no effect if already text)
"mmm d, yyyy"), default and convert "to" datef, DATEVALUE(LEFT(textfr,LEN(textfr)-4)&
RIGHT(textfr,4)+offset), add offset and convert "from" to date datet, DATEVALUE(LEFT(textto,LEN(textto)-4)&
RIGHT(textto,4)+offset), add offset and convert "to" to date dir, SIGN(datet - datef), get direction (+ if to > from) ydec, dir * YEARFRAC(datef, datet, 1), decimal years yymd, ROUNDDOWN(ydec, 0), ymd years mdec, ydec * 12, decimal months mymd, ROUNDDOWN((ydec - yymd) * 12, 0), ymd months ddec, datet - datef, decimal days dymd, ROUNDDOWN(datet -
EDATE(datef, 12 * yymd + mymd), 0), ymd days unit, MAX(MIN(N(type), 5), 1), force type between 1 and 5, default 1 value, ROUNDDOWN(CHOOSE(unit, ydec, mdec, ddec / 7, ddec, 0),
N(decimals)), pick decimal value by unit type, 1-4
format, IFERROR(CHOOSE(unit, set format per type 1-4 and label inputs
CHOOSE(N(label) + 1, "", "y", "yrs"),
CHOOSE(N(label) + 1, "", "m", "mos"),
CHOOSE(N(label) + 1, "", "w", "wks"),
CHOOSE(N(label) + 1, "", "d", "days")),""),
sep,IF(N(nospace),""," "), remove space if option entered
IF(value,value & sep & format, concatenate value and label, type 1-4
yymd & "y" & sep & dir * mymd & "m" & sep & dir * dymd & "d"
) assemble ymd string, type 5
)
);
Output Being text, the output is initially aligned left. Here it has been aligned right.
One Date from Another
Within the normal valid date range, we can easily add or subtract years, months and/or days to a date to calculate a dependent date. Years can be added within the DATE function, months can be added with the EDATE function, and days can be added with simple addition. Here we combine them all into one so that any combination of years, months and days can be added.
Inputs
The input arguments are:
from, required, must end with 4-digit year, zero-padded if necessary
[y], [m], [d], years, months, days in any combination, + or -
[format] for the output, must end in yyyy, default "mmm d, yyyy"
As noted above, input help is added in the Name Manager comment.
Logic
As with the date difference function, we begin by normalizing the input date to a text date, then offset and convert it to a valid date for processing. After the ymd additions have been made, the resulting date is converted back to text.
There are two nuances in the output. First, if the resulting date is in the first century, it needs zero-padding to avoid the deception of a two-digit year - "yyyy" in the format will not force four digits. The second nuance is to raise a flag if the result is BCE.
Below is the code with comments added in blue.
my.date.xcalc = LAMBDA(from, [y], [m], [d], [format], LET( note1, "calculate date2 from date1 +/- y/m/d", note2, "from and [format] must end with yyyy", offset, 1900, set offset constant fromtxt, TEXT(from,"mmm d, yyyy"), convert "from" to text fromdat, DATEVALUE(LEFT(fromtxt, LEN(fromtxt) - 4) & RIGHT(fromtxt, 4) + offset), add offset and convert "from" to date addyr, DATE(YEAR(fromdat) + N(y),
MONTH(fromdat), DAY(fromdat)), add y years addmo, EDATE(addyr, N(m)), add m months (cumulative) addday, addmo + N(d), add d days (cumulative) outtxt, TEXT(addday, IF(LEN(format),
format, "mmm d, yyyy")), convert to text, input or default format output, LEFT(outtxt, LEN(outtxt) - 4) & TEXT(RIGHT(outtxt, 4) - offset, "0000"), subtract offset and force 4-digit year IF(YEAR(fromdat) + y > offset, output, "#BCE") add BCE flag ) ); Output
Samples:
Supplemental Information
Double Dates
Beginning in 45 B.C., many parts of the world used the Julian calendar to mark the passage of time. According to the Julian calendar, March 25 was the first day of the year and each year was 365 days and 6 hours long. In 1582, Pope Gregory XIII determined that the Julian calendar was incorrect: each day was just a little bit too long. This meant that the human calendar wasn't keeping up with nature's calendar, and the seasons kept arriving slightly earlier in the year. To solve the problem, Pope Gregory XIII created the Gregorian calendar. This is the calendar that we use officially in the United States. This new calendar changed the first day of the year from March 25 to January 1. Pope Gregory also had everyone jump ahead by 10 days to make up for the days that were lost when the world was using the old Julian calendar.
The practice of writing double dates resulted from this switch from the Julian to the Gregorian calendar, and also from the fact that not all countries and people accepted the new calendar at the same time. For example, England and the American colonies didn't officially accept the new calendar until 1752. Before 1752, the English government still observed March 25 as the first of the year, but most of the population observed January 1 as the first of the year. For this reason, many people wrote dates falling between January 1 and March 25 with both years, as in the following examples.
By the time England and the colonies adopted the new Gregorian calendar, the discrepancy between the two calendars was eleven days, instead of ten. To resolve the discrepancy, the government ordered that September 2, 1752 be followed by September 14, 1752. Some people also added 11 days to their birth dates (a fact which is not noted on their birth certificates).
Leap Year Rules
The Julian calendar adds a leap day every four years. Over the years this results in a discrepancy between the common calendar and the natural calendar. The Gregorian calendar addressed this by adding criteria to the leap year determination:
The year must be evenly divisible by 4
If the year can also be evenly divided by 100, it is not a leap year; unless...
The year is also evenly divisible by 400. Then it is a leap year.
Comments