用 Excel 公式计算星座与年龄听上去并不是一件困难之事,在搜索引擎上也能找到现成的答案。但是如若使用 Notion 的函数体系来执行这个任务的话,似乎就成为了一种挑战。
我这里 Notion 里有一个数据库,记载了我各路亲朋好友的小孩子们的大名、小名以及生日。为了不错过这些小朋友们的生日,我利用了 Notion 的函数功能来自动计算他们的星座、年龄以及距离下次生日的倒计时,就像这样(数据已脱敏处理):

这些函数公式我也同时用在了其他的一些数据库里,例如在我的「设备清单」中,我可以直观地看到这些设备的服役时长。接下来我就详细说说这里面的实现原理。
本文已经假设你对 Notion 的函数体系有了初步的了解。如果你还不太了解,可以看看少数派作者 Niin 的这篇文章:建立强大的 Notion 数据库,从了解函数开始。
星座的计算方式
Excel 里有一个现成的星座计算公式:
=LOOKUP(--TEXT(D2,"mdd"),{101,"摩羯座";120,"水瓶座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";622,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1024,"天蝎座";1123,"射手座";1222,"摩羯座"})
为了移植到 Notion 中来,我们可以使用 if
这个函数,它其中一种用法是 boolean ? value : value
。仿照 Excel 公式的思路,先通过 (month(prop("生日")) + 1) * 100 + date(prop("生日")
这个算式将生日转化为 mdd 格式,然后加入 if
的嵌套判断,就形成了使用 Notion 函数计算星座的方法:
((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 1222) ? "摩羯座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 1123) ? "射手座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 1024) ? "天蝎座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 923) ? "天秤座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 823) ? "处女座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 723) ? "狮子座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 622) ? "巨蟹座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 521) ? "双子座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 420) ? "金牛座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 321) ? "白羊座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 219) ? "双鱼座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 120) ? "水瓶座" : (((month(prop("生日")) + 1) * 100 + date(prop("生日")) >= 101) ? "摩羯座" : ""))))))))))))

年龄的计算方式
把年龄表述为「y 岁」似乎没有什么难度,但我想把年龄表述成「y 岁 m 个月 d 天」这种格式。用 Excel 实现的话大概是这样:
=DATEDIF(D2,TODAY(),"Y")&" 岁 "&DATEDIF(D2,TODAY(),"YM")&" 个月 "&DATEDIF(D2,TODAY(),"MD")&" 天"
但是用 Notion 函数实现就相对麻烦一些。最简单的方案是仿照上述 Excel 公式的方式,使用 dateBetween
函数分别计算岁数、余月、以及余日,再用 format
将数字转换成字符串,然后使用 concat
函数将多个字符串拼接在一起。
// 计算年龄 - 年
dateBetween(now(), prop("生日"), "years"))
// 计算年龄 - 余月
dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")
// 计算年龄 - 余日
dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")
// 使用 concat 函数进行拼接
concat(format(dateBetween(now(), prop("生日"), "years")), " 岁 ", format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月 ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天")
这样确实就形成了与 Excel 公式效果完全一致的 Notion 函数,但接下来我又发现一些问题,譬如说它会出现「3 岁 0 个月 0 天」这种情况,不太符合语法的习惯表述。于是我尝试加上零整判断,就像这样:

// 计算年龄 - 年,如果不为零则展示为「y 年」,如果为零则展示为空
(dateBetween(now(), prop("生日"), "years") != 0) ? concat(format(dateBetween(now(), prop("生日"), "years")), " 岁") : ""
// 计算年龄 - 余月,如果不为零则展示为「m 个月」,如果为零则展示为空
(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years") != 0) ? concat((dateBetween(now(), prop("生日"), "years") != 0) ? " " : "", format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月") : ""
// 计算年龄 - 余日,如果不为零则展示为「d 天」,如果为零则展示为空
(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days") != 0) ? concat(" ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天") : ""
// 使用 concat 函数进行拼接
concat((dateBetween(now(), prop("生日"), "years") != 0) ? concat(format(dateBetween(now(), prop("生日"), "years")), " 岁") : "", (dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years") != 0) ? concat((dateBetween(now(), prop("生日"), "years") != 0) ? " " : "", format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月") : "", (dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days") != 0) ? concat(" ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天") : "")
这样这个函数就可以使用了,对于「3 岁 0 个月 0 天」这种情况直接就展示为「3 岁」了。但你以为这就完了吗?对于追求完美的我来说还不够。我接下来尝试把「零」、「整」、「不到一天」这些判断统统加进来,就像这样:

为了实现这个效果,代码需要这样写:先在草稿纸上绘制好流程图,再把基于流程图的 if
嵌套的框架写好,避免弄错。
// 一种写法
y == 0 ? (m == 0 ? (d == 0 ? "A" : "B") : (d == 0 ? "C" : "D")) : (m == 0 ? (d == 0 ? "E" : "F") : (d == 0 ? "G" : "H"))
// 另一种写法
if(y == 0, if(m == 0, if(d == 0, "A", "B"), if(d == 0, "C", "D")), if(m == 0, if(d == 0, "E", "F"), if(d == 0, "G", "H")))
同时把每种文案的格式先预填好(Notion 无法声明变量,只能这样啦):
// "A"
"不到一天"
// "B"
concat(format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天")
// "C"
concat(format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月整")
// "D"
concat(format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月 ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天")
// "E"
concat(format(dateBetween(now(), prop("生日"), "years")), " 岁整")
// "F"
concat(format(dateBetween(now(), prop("生日"), "years")), " 岁零 ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天")
// "G"
concat(format(dateBetween(now(), prop("生日"), "years")), " 岁 ", format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月整")
// "H"
concat(format(dateBetween(now(), prop("生日"), "years")), " 岁 ", format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月 ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天")
再把它们全都代入进去,并在最前面加上一个判断生日是否为空的逻辑 empty(prop("生日")) ? true : false
,最后就形成了这样一段函数(是的它就有这么长,但是很好用):
empty(prop("生日")) ? "" : (dateBetween(now(), prop("生日"), "years") == 0 ? ((dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")) == 0 ? ((dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")) == 0 ? "不到一天" : concat(format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天")) : ((dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")) == 0 ? concat(format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月整") : concat(format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月 ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天"))) : ((dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")) == 0 ? ((dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")) == 0 ? concat(format(dateBetween(now(), prop("生日"), "years")), " 岁整") : concat(format(dateBetween(now(), prop("生日"), "years")), " 岁零 ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天")) : ((dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")) == 0 ? concat(format(dateBetween(now(), prop("生日"), "years")), " 岁 ", format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月整") : concat(format(dateBetween(now(), prop("生日"), "years")), " 岁 ", format(dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years")), " 个月 ", format(dateBetween(now(), dateAdd(dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), dateBetween(now(), prop("生日"), "months") - 12 * dateBetween(now(), prop("生日"), "years"), "months"), "days")), " 天"))))

距离下次生日的计算方式
最后我们再补上一列,「距离下次生日」的日期倒数。在 Excel 上我们可以这样写:
=IF(D2,DATE(YEAR(D2)+DATEDIF(D2+1,TODAY(),"y")+1,MONTH(D2),DAY(D2))-TODAY(),"")
在 Notion 上,我们需要先计算下一次生日的具体日期:
dateAdd((prop("生日")), (dateBetween(now(), prop("生日"), "years") + 1), "years")
然后再计算距离下次生日的天数,由于 dateBetween
函数计算未来是负数,此处需要变为正数,然后加上 1。再加上生日为空以及生日在当天的判断后,最终函数为:
// 简化版,不包含生日为空以及生日在当天的判断
1 - dateBetween(now(), dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years") + 1, "years"), "days")
// 完整版,包含生日为空以及生日在当天的判断
empty(prop("生日")) ? 0 : ((dateBetween(now(), dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years"), "years"), "days") == 0) ? 0 : (1 - dateBetween(now(), dateAdd(prop("生日"), dateBetween(now(), prop("生日"), "years") + 1, "years"), "days")))
这样我们就可以计算出「距离下次生日」的日期倒数。在 Notion 上可以用这一列来进行排序,它就会动态调整接下来要过生日的小朋友们的次序了。
