This question comes from a reader of the Notion course. It has been organized and shared here. If you encounter similar issues while using Notion, feel free to leave a comment.
Introduction
This Notion formula helps you automatically evaluate a task's status, deadline, and completion, displaying the results with different colors and icons. It can show whether a task was completed on time, completed early, completed late, or for incomplete tasks, how much time remains or how long it's been overdue.
Setup Instructions
1. Create the Required Fields
Make sure your Notion database has the following properties. The names must match exactly:
- 排期 (Type: Date) — Schedule Date
- 状态 (Type: Status) — Status
- 完成日期 (Type: Date) — Completion Date
2. Configure the Status Field
Add the following options to the Status property:
- 收集 — Collect
- 搁置 — On Hold
- 待办 — Todo
- 执行中 — In Progress
- 完成 — Completed
- 放弃 — Abandoned
You can assign different colors to these statuses based on your preference.
3. Create the Formula Field
- Add a Formula field
- Name the property "逾期提醒" (Overdue Reminder) or any name you prefer
- Set the property type to "公式" (Formula)
- Paste the following code into the formula editor
Reference Template
- Template link: https://leon21.notion.site/1e80e68aa04680cd8793cd68562247c5?pvs=4
- The reference code on this page may not be the latest version. Please refer to the template link above for the most up-to-date version.
Formula Code
/* ================================================== */
/* 任务状态与逾期判断 v21.1 @二一的笔记 */
/* ================================================== */
/* 依赖属性: */
/* - 排期 (Date) */
/* - 状态 (Select/Status) */
/* - 完成日期 (Date) */
/* ================================================== */
let(
/* --- 基本变量 --- */
schedule, prop("排期"),
status, prop("状态"),
completionDate, prop("完成日期"),
nowDate, now(),
isScheduleEmpty, empty(schedule),
isCompletionDateEmpty, empty(completionDate),
/* --- 日期范围相关 (如果排期不为空) --- */
startDate, if(isScheduleEmpty, "", dateStart(schedule)), /* 范围开始日期 */
endDate, if(isScheduleEmpty, "", dateEnd(schedule)), /* 范围结束日期 (截止日期) */
/* --- 检查是否有具体时间 (不是00:00) --- */
hasEndTime, not isScheduleEmpty and formatDate(endDate, "HH:mm") != "00:00", /* 检查结束日期是否有时间 */
hasStartTime, not isScheduleEmpty and formatDate(startDate, "HH:mm") != "00:00", /* 检查开始日期是否有时间 */
hasCompletionTime, not isCompletionDateEmpty and formatDate(completionDate, "HH:mm") != "00:00",
shouldShowDetailedTime, hasEndTime or hasCompletionTime or hasStartTime, /* 只要任一有时间，就可能需要详细显示 */
/* --- 主逻辑判断 (基于状态) --- */
ifs(
/* --- 状态: 放弃 --- */
status == "放弃",
if(not isCompletionDateEmpty,
style("✅ 已完成 · 请修改任务状态", "green,b,green_background,c"),
style("🚫 已放弃", "gray,b,gray_background,c")
),
/* --- 状态: 完成 --- */
status == "完成",
let(
deadline, endDate, /* 完成状态主要看截止日期 */
ifs(
isScheduleEmpty and isCompletionDateEmpty,
style("✅ 已完成", "green,b,green_background,c"),
isCompletionDateEmpty, /* 有排期，无完成日期 */
style("✅ 按时完成", "green,b,green_background,c"),
isScheduleEmpty, /* 无排期，有完成日期 */
style("✅ 已完成", "green,b,green_background,c"),
/* --- 有排期和完成日期的情况 --- */
ifs(
completionDate > deadline,
let( diff_minutes, dateBetween(completionDate, deadline, "minutes"), let( days, floor(diff_minutes / 1440), let( hours, floor(mod(diff_minutes, 1440) / 60), let( minutes, mod(diff_minutes, 60),
style( "✅ 完成 · 逾期" + if(days > 0, days + "天", "") + if(hours > 0, hours + "小时", "") + if(minutes > 0, minutes + "分钟", "") + if(days == 0 and hours == 0 and minutes == 0, "片刻", ""), "orange,b,orange_background,c" ) )))),
completionDate == deadline,
style("✅ 按时完成", "green,b,green_background,c"),
let( diff_minutes, dateBetween(deadline, completionDate, "minutes"), let( days, floor(diff_minutes / 1440), let( hours, floor(mod(diff_minutes, 1440) / 60), let( minutes, mod(diff_minutes, 60),
style( "🎉 提前" + if(days > 0, days + "天", "") + if(hours > 0, hours + "小时", "") + if(minutes > 0, minutes + "分钟", "") + if(days == 0 and hours == 0 and minutes == 0, "片刻", "") + "达成", "pink,b,pink_background,c" ) ))))
)
)
),
/* --- 状态: 其他活动状态 (收集, 搁置, 待办, 执行中) --- */
if(isScheduleEmpty,
style("⚪ 未设置排期", "gray"),
let(
deadline, endDate, /* 活动状态关注截止日期 */
if(not isCompletionDateEmpty,
if(completionDate <= deadline,
style("✅ 已完成 · 请修改任务状态", "green,b,green_background,c"),
style("❗ 已逾期完成 · 请修改任务状态", "red,b,red_background,c")
),
/* --- 完成日期未填，按正常活动状态判断 --- */
ifs(
/* 1. 今天截止? (精确判断) */
formatDate(nowDate, "YYYY-MM-DD") == formatDate(deadline, "YYYY-MM-DD"),
if(hasEndTime, /* 检查结束日期是否有时间 */
if(nowDate > deadline, /* 如果当前时间已超过截止时间 */
let( diff_minutes, dateBetween(nowDate, deadline, "minutes"), let( hours, floor(diff_minutes / 60), let( minutes, mod(diff_minutes, 60),
style( "⏰ 已超期" + if(hours > 0, hours + "小时", "") + if(minutes > 0, minutes + "分钟", "") + if(hours == 0 and minutes == 0, "片刻", ""), "red,b,red_background,c" ) ))),
/* 如果当前时间未超过截止时间 */
style("⚠️ 今天 " + formatDate(deadline, "HH:mm") + " 截止", "yellow,b,yellow_background,c")
),
/* 如果没有具体截止时间 */
style("⚠️ 今天截止", "yellow,b,yellow_background,c")
),
/* 2. 明天开始? (针对有开始时间的范围) */
hasStartTime and formatDate(nowDate, "YYYY-MM-DD") == formatDate(dateAdd(startDate, -1, "days"), "YYYY-MM-DD"),
let( remainingHours, dateBetween(startDate, nowDate, "hours"),
style("🚀 明天 " + formatDate(startDate, "HH:mm") + " 开始，还剩" + remainingHours + "小时", "blue,b,blue_background,c")
),
/* 3. 明天截止? */
formatDate(nowDate, "YYYY-MM-DD") == formatDate(dateAdd(deadline, -1, "days"), "YYYY-MM-DD"),
if(hasEndTime, /* 检查是否有具体结束时间 */
style("🔔 明天 " + formatDate(deadline, "HH:mm") + " 截止", "orange,b,orange_background,c"),
style("🔔 明天截止", "orange,b,orange_background,c")
),
/* 4. 后天开始? (检查开始日期是后天且有时间) */
hasStartTime and formatDate(nowDate, "YYYY-MM-DD") == formatDate(dateAdd(startDate, -2, "days"), "YYYY-MM-DD"),
let(
diff_minutes, dateBetween(startDate, nowDate, "minutes"),
let(
total_hours, floor(diff_minutes / 60), /* 计算总剩余小时 */
let(
days, floor(total_hours / 24), /* 换算成天 */
let(
hours, mod(total_hours, 24), /* 剩余小时 */
style(
"🗓️ 后天 " + formatDate(startDate, "HH:mm") + " 开始，还剩" +
if(days > 0, days + "天", "") +
if(hours > 0, hours + "小时", "") +
if(days == 0 and hours == 0 and diff_minutes > 0, "不到1小时", "") + /* 如果不足一小时 */
if(days == 0 and hours == 0 and diff_minutes <= 0, "片刻", ""), /* 精确到开始时间 */
"purple,b,purple_background,c"
)
)
)
)
),
/* 5. 已逾期? (当前时间已晚于截止日期) */
nowDate > deadline,
if(shouldShowDetailedTime,
let( diff_minutes, dateBetween(nowDate, deadline, "minutes"), let( days, floor(diff_minutes / 1440), let( hours, floor(mod(diff_minutes, 1440) / 60), let( minutes, mod(diff_minutes, 60),
style( "⏰ 已超期" + if(days > 0, days + "天", "") + if(hours > 0, hours + "小时", "") + if(minutes > 0, minutes + "分钟", "") + if(days == 0 and hours == 0 and minutes == 0, "片刻", ""), "red,b,red_background,c" ) )))),
let( overdueDays, dateBetween(dateStart(nowDate), dateStart(deadline), "days"),
style("⏰ 已超期" + if(overdueDays == 0, 1, overdueDays) + "天", "red,b,red_background,c")
)
),
/* 6. 即将到来 (默认情况)，计算距离截止时间 --- */
if(shouldShowDetailedTime,
let( diff_minutes, dateBetween(deadline, nowDate, "minutes"), let( days, floor(diff_minutes / 1440), let( hours, floor(mod(diff_minutes, 1440) / 60), let( minutes, mod(diff_minutes, 60),
if(days == 0 and hours < 24, style( "⌛ 剩余" + if(hours > 0, hours + "小时", "") + if(minutes > 0, minutes + "分钟", "") + if(hours == 0 and minutes == 0, "片刻", ""), "purple,b,purple_background,c" ),
style( "⏳ 剩余" + if(days > 0, days + "天", "") + if(hours > 0, hours + "小时", "") + if(minutes > 0, minutes + "分钟", ""), "purple,b,purple_background,c" )
) )))),
/* 否则使用天数计算 (只显示距离截止日期的天数) */
let( remainingDays, dateBetween( parseDate(formatDate(deadline, "YYYY-MM-DD")), parseDate(formatDate(nowDate, "YYYY-MM-DD")), "days" ),
/* 后天截止(无时间) 或 更远日期 */
if(remainingDays == 2,
style("🗓️ 后天到期", "purple,b,purple_background,c"), /* 截止日是后天，但无时间 */
style("⏳ 剩余" + remainingDays + "天", "purple,b,purple_background,c") /* 超过后天 */
)
)
)
)
)
)
)
)
)Once set up, the formula will automatically display different indicators based on the task's status, schedule date, and completion date.
If you have further questions, please reach out via email at eryidebiji@gmail.com
2025-08-19 Update
This task status formula has now been integrated into the comprehensive Notion template "FLO.W". Feel free to check it out for more details.
📘 FLO.W — Notion Personal Management System
FLO.W is a Notion-based personal management template that integrates tasks, notes, projects, habits, and more, complete with comprehensive tutorials.
Ready-to-use Notion template system
Complete tutorials included, beginner-friendly
Continuously updated and maintained
Learn About FLO.W Template✨ 1237+ users chose FLO.W
