Date difference in Notion

Notion could be used to calculate date difference in years, months and days within a database table. I was playing with formula a little bit and wanted to share my work for this matter, with variable display of year, month and day part depending on their values (0 is not shown).

If End date is not provided, difference is calculated as now() - Start. If event starts and ends on the same day, by default solution is 0, but I raise that to 1, so you may change that part if wanted.

Prerequisites

Notion table with at least Start column.

I hope it will save some time for similar task... I used to keep this kind of track in spreadsheets (Excel, Quip), but need to copy & paste formula for new entries.

Notion has the database tables where this formula is placed once and works for all new entries. (ROI explanation 😀)

Formula (with +1 days)

if(empty(prop("Start")),"",if(empty(prop("End")), if(dateBetween(now(), prop("Start"), "years") == 0, "", format(dateBetween(now(), prop("Start"), "years")) + "y ") + if(dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years") == 0, "", format(dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years")) + "m ") + if(dateBetween(now(), dateAdd(dateAdd(prop("Start"), dateBetween(now(), prop("Start"), "years"), "years"), dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years"), "months"), "days") == 0, format(dateBetween(now(), dateAdd(dateAdd(prop("Start"), dateBetween(now(), prop("Start"), "years"), "years"), dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years"), "months"), "days") + 1), format(dateBetween(now(), dateAdd(dateAdd(prop("Start"), dateBetween(now(), prop("Start"), "years"), "years"), dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years"), "months"), "days"))) + "d", if(dateBetween(prop("End"), prop("Start"), "years") == 0, "", format(dateBetween(prop("End"), prop("Start"), "years")) + "y ") + if(dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years") == 0, "", format(dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years")) + "m ") + if(dateBetween(prop("End"), dateAdd(dateAdd(prop("Start"), dateBetween(prop("End"), prop("Start"), "years"), "years"), dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years"), "months"), "days") == 0, format(dateBetween(prop("End"), dateAdd(dateAdd(prop("Start"), dateBetween(prop("End"), prop("Start"), "years"), "years"), dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years"), "months"), "days") + 1), format(dateBetween(prop("End"), dateAdd(dateAdd(prop("Start"), dateBetween(prop("End"), prop("Start"), "years"), "years"), dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years"), "months"), "days"))) + "d"))
        

Formula (with 0 days)

if(empty(prop("Start")), "", if(empty(prop("End")), if(dateBetween(now(), prop("Start"), "years") == 0, "", format(dateBetween(now(), prop("Start"), "years")) + "y ") + if(dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years") == 0, "", format(dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years")) + "m ") + if(dateBetween(now(), dateAdd(dateAdd(prop("Start"), dateBetween(now(), prop("Start"), "years"), "years"), dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years"), "months"), "days") == 0, "", format(dateBetween(now(), dateAdd(dateAdd(prop("Start"), dateBetween(now(), prop("Start"), "years"), "years"), dateBetween(now(), prop("Start"), "months") - 12 * dateBetween(now(), prop("Start"), "years"), "months"), "days"))) + "d", if(dateBetween(prop("End"), prop("Start"), "years") == 0, "", format(dateBetween(prop("End"), prop("Start"), "years")) + "y ") + if(dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years") == 0, "", format(dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years")) + "m ") + if(dateBetween(prop("End"), dateAdd(dateAdd(prop("Start"), dateBetween(prop("End"), prop("Start"), "years"), "years"), dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years"), "months"), "days") == 0, "", format(dateBetween(prop("End"), dateAdd(dateAdd(prop("Start"), dateBetween(prop("End"), prop("Start"), "years"), "years"), dateBetween(prop("End"), prop("Start"), "months") - 12 * dateBetween(prop("End"), prop("Start"), "years"), "months"), "days")) + "d")))        

#Notion, #Date, #formula