用途 计算表格中开始日期和结束日期, 显示经过了几天, 还有几天结束, 经过天数和总天数的百分比 计算Done和Total列的数值(任务完成的值和总值), 计算任务完成度, 显示任务完成了多少 代码 使用时, 删除缩进和换行. 使代码变为1行复制到表格Formula列. 注意替换列名 vscode里删除缩进和换行→按ctrl+shift+h替换→正则模式\n| 替换为空. "进度: " + format(slice("::::::::::::::::::::::::::::::::::::::::", 0, ceil(prop("Done") / prop("Total") * 40))) + format(slice("........................................", 0, 40 - ceil(prop("Done") / prop("Total") * 40))) + " " + format(round(prop("Done") / prop("Total") * 100)) + "%" + "\n日期: " + if( empty(start(prop("Date"))), "........................................ 经过-天 还有-天结束 -%", if( start(prop("Date")) == end(prop("Date")), "........................................ 经过" + format(dateBetween(now(), start(prop("Date")), "days")) + "天 还有-天结束 -%", if( start(prop("Date")) > now(), "........................................ 还有" + format(dateBetween(start(prop("Date")), now(), "days")) + "天开始 还有" + format(dateBetween(end(prop("Date")), now(), "days")) + "天结束 -%", if( dateBetween(now(), start(prop("Date")), "days") < dateBetween(end(prop("Date")), start(prop("Date")), "days"), format(slice("::::::::::::::::::::::::::::::::::::::::", 0, floor(dateBetween(now(), start(prop("Date")), "days") / dateBetween(end(prop("Date")), start(prop("Date")), "days") * 40))) + format(slice("........................................", 0, 40 - floor(dateBetween(now(), start(prop("Date")), "days") / dateBetween(end(prop("Date")), start(prop("Date")), "days") * 40))) + " 经过" + format(dateBetween(now(), start(prop("Date")), "days")) + "天" + " 还有" + format(dateBetween(end(prop("Date")), now(), "days")) + "天结束" + " " + format(round(dateBetween(now(), start(prop("Date")), "days") / dateBetween(end(prop("Date")), start(prop("Date")), "days") * 100)) + "%", "::::::::::::::::::::::::::::::::::::::::" + " 用" + format(dateBetween(end(prop("Date")), start(prop("Date")), "days")) + "天 还有0天结束 100%" ) ) ) ) 一些常用函数
format: 转换为字符串 format(随便啥的) slice: 字符串切片 slice(str, 开始index, 结束index(取不到)) ceil: 小数取大于这个数的整数 ceil(1.1)=2 floor: 小数取小于这个数的整数 ceil(1.1)=1 round: 小数四舍五入 round(4.4) == 4 round(4.5) == 5 prop: 取某一列元素 prop("Date") 取日期那一列 end(prop("Date")), start(prop("Date")): 日期格式开始日期和结束日期(如果没有结束日期时, 默认结束日期等于开始日期) now(): 当前日期 dateBetween: 计算两个日期间的天数或月数 dateBetween(结束日期, 开始日期, ["days", "months", "years"]) if: if判断 if(判断条件, 如果为true运行代码, 如果为false运行代码) empty: 空返回true, 非空返回false 也可以将天数转换为年月日
需要修改下面代码
format(dateBetween(now(), start(prop("Date")), "days")) 改为
if( dateBetween(now(), start(prop("Date")), "years") > 0, format(dateBetween(now(), start(prop("Date")), "years")) + "年", "" ) + if( dateBetween(now(), start(prop("Date")), "months") > 0, format(dateBetween(now(), start(prop("Date")), "months") - dateBetween(now(), start(prop("Date")), "years") * 12) + "月", "" ) + if( dateBetween(now(), start(prop("Date")), "days") > 0, format(dateBetween(now(), start(prop("Date")), "days") - floor(dateBetween(now(), start(prop("Date")), "months") * 30.44)) + "天", "0天" ) 合并后 版本2
"进度: " + format(slice("::::::::::::::::::::::::::::::::::::::::", 0, ceil(prop("Done") / prop("Total") * 40))) + format(slice("........................................", 0, 40 - ceil(prop("Done") / prop("Total") * 40))) + " " + format(round(prop("Done") / prop("Total") * 100)) + "%" + "\n日期: " + if( empty(start(prop("Date"))), "........................................ 经过-天 还有-天结束 -%", if( start(prop("Date")) == end(prop("Date")), "........................................ 经过" + if( dateBetween(now(), start(prop("Date")), "years") > 0, format(dateBetween(now(), start(prop("Date")), "years")) + "年", "" ) + if( dateBetween(now(), start(prop("Date")), "months") > 0, format(dateBetween(now(), start(prop("Date")), "months") - dateBetween(now(), start(prop("Date")), "years") * 12) + "月", "" ) + if( dateBetween(now(), start(prop("Date")), "days") > 0, format(dateBetween(now(), start(prop("Date")), "days") - floor(dateBetween(now(), start(prop("Date")), "months") * 30.44)) + "天", "0天" ) + " 还有-天结束 -%", if( start(prop("Date")) > now(), "........................................ 还有" + if( dateBetween(start(prop("Date")), now(), "years") > 0, format(dateBetween(start(prop("Date")), now(), "years")) + "年", "" ) + if( dateBetween(start(prop("Date")), now(), "months") > 0, format(dateBetween(start(prop("Date")), now(), "months") - dateBetween(start(prop("Date")), now(), "years") * 12) + "月", "" ) + if( dateBetween(start(prop("Date")), now(), "days") > 0, format(dateBetween(start(prop("Date")), now(), "days") - floor(dateBetween(start(prop("Date")), now(), "months") * 30.44)) + "天", "0天" ) + "开始 还有" + if( dateBetween(end(prop("Date")), now(), "years") > 0, format(dateBetween(end(prop("Date")), now(), "years")) + "年", "" ) + if( dateBetween(end(prop("Date")), now(), "months") > 0, format(dateBetween(end(prop("Date")), now(), "months") - dateBetween(end(prop("Date")), now(), "years") * 12) + "月", "" ) + if( dateBetween(end(prop("Date")), now(), "days") > 0, format(dateBetween(end(prop("Date")), now(), "days") - floor(dateBetween(end(prop("Date")), now(), "months") * 30.44)) + "天", "0天" ) + "结束 -%", if( dateBetween(now(), start(prop("Date")), "days") < dateBetween(end(prop("Date")), start(prop("Date")), "days"), format(slice("::::::::::::::::::::::::::::::::::::::::", 0, floor(dateBetween(now(), start(prop("Date")), "days") / dateBetween(end(prop("Date")), start(prop("Date")), "days") * 40))) + format(slice("........................................", 0, 40 - floor(dateBetween(now(), start(prop("Date")), "days") / dateBetween(end(prop("Date")), start(prop("Date")), "days") * 40))) + " 经过" + if( dateBetween(now(), start(prop("Date")), "years") > 0, format(dateBetween(now(), start(prop("Date")), "years")) + "年", "" ) + if( dateBetween(now(), start(prop("Date")), "months") > 0, format(dateBetween(now(), start(prop("Date")), "months") - dateBetween(now(), start(prop("Date")), "years") * 12) + "月", "" ) + if( dateBetween(now(), start(prop("Date")), "days") > 0, format(dateBetween(now(), start(prop("Date")), "days") - floor(dateBetween(now(), start(prop("Date")), "months") * 30.44)) + "天", "0天" ) + " 还有" + if( dateBetween(end(prop("Date")), now(), "years") > 0, format(dateBetween(end(prop("Date")), now(), "years")) + "年", "" ) + if( dateBetween(end(prop("Date")), now(), "months") > 0, format(dateBetween(end(prop("Date")), now(), "months") - dateBetween(end(prop("Date")), now(), "years") * 12) + "月", "" ) + if( dateBetween(end(prop("Date")), now(), "days") > 0, format(dateBetween(end(prop("Date")), now(), "days") - floor(dateBetween(end(prop("Date")), now(), "months") * 30.44)) + "天", "0天" ) + "结束" + " " + format(round(dateBetween(now(), start(prop("Date")), "days") / dateBetween(end(prop("Date")), start(prop("Date")), "days") * 100)) + "%", "::::::::::::::::::::::::::::::::::::::::" + " 经过" + if( dateBetween(end(prop("Date")), start(prop("Date")), "years") > 0, format(dateBetween(end(prop("Date")), start(prop("Date")), "years")) + "年", "" ) + if( dateBetween(end(prop("Date")), start(prop("Date")), "months") > 0, format(dateBetween(end(prop("Date")), start(prop("Date")), "months") - dateBetween(end(prop("Date")), start(prop("Date")), "years") * 12) + "月", "" ) + if( dateBetween(end(prop("Date")), start(prop("Date")), "days") > 0, format(dateBetween(end(prop("Date")), start(prop("Date")), "days") - floor(dateBetween(end(prop("Date")), start(prop("Date")), "months") * 30.44)) + "天", "0天" ) + " 还有0天结束 100%" ) ) ) ) ...