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 subtractin Google Spreadsheet this results in a #NUM! error.
A workaround would be:
YEAR:but this does not work properly with days.=DATEDIF(-693990,A1,"Y")
MONTH:=DATEDIF(-693990,A1,"YM")
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.
No comments:
Post a Comment