Tuesday, July 31, 2018

How to subtract number of years, months and day from dates before 1900 in Google Spreadsheets?

Apparently Google Spreadsheet in incapable of handling dates before 1900 in the DATE, YEAR, MONTH, DAY functions.
So while in LibreOffice you can do this:
=DATE(YEAR(A1)-B1, MONTH(A1)-C1, DAY(A1)-D1)
where A1 is a date before 1900 and B1, C1, D1 are integers for the years, months, days to subtract
in Google Spreadsheet this results in a #NUM! error.

A workaround would be:
YEAR: =DATEDIF(-693990,A1,"Y")
MONTH: =DATEDIF(-693990,A1,"YM")
but this does not work properly with days.
A different way to put it is:
YEAR: =YEAR(A1+693961)-1900
MONTH: =MONTH(A1+693961)
DAY: =DAY(A1+693961)

However this does not help with subtracting and reconstructing the date.
So my workaround was to add a new spreadsheet function, because that can be written in javascript and these limitations do not apply.

From Tools > Script editor create a new script. Paste the following function and from Run > Test as add-on... add it to the sheet you need it in.

function dateSubtract(originalDate, yearsToSubtract, monthsToSubtract, daysToSubtract) {
  var date = new Date(originalDate);  
  date.setDate(date.getDate() - daysToSubtract);
  date.setMonth(date.getMonth() - monthsToSubtract);
  date.setFullYear(date.getFullYear() - yearsToSubtract);
  return date;
}
Than in the spreadsheet you can use this function like this:
=DATESUBTRACT(A1, B1, C1, D1)

What did I need this for?
In genealogy research the death records might indicate the age of the person in a years, months, days format. I collected some of these records in a spreadsheet and wanted to calculate the supposed birth date of the deceased.