标签：11 F2 表格 22 进度条 DATEDIF WPS TODAY E2
显示效果
1，直接使用公式：（可使用txt 替换B2和C2)
=IF(DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))>0,REPT("█",(IF(DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))>=1,1,DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))))*50)&" "&TEXT(IF(DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))>=1,1,DATEDIF(B2,TODAY(),"d")/IF(DATEDIF(B2,C2,"d")=0,DATEDIF(B2,TODAY(),"d"),DATEDIF(B2,C2,"d"))),"0.0%"),"未开始")
1.1 演示结果如下：
| 项目 |
项目开始日期 |
终止日期 |
项目进度 |
| 项目1 |
22/12/7 |
22/12/22 |
未开始 |
| 项目2 |
22/12/2 |
22/12/22 |
未开始 |
| 项目3 |
22/11/25 |
22/12/15 |
未开始 |
| 项目4 |
22/11/30 |
22/12/15 |
未开始 |
| 项目5 |
22/11/20 |
22/12/8 |
未开始 |
| 项目6 |
22/11/16 |
22/12/1 |
未开始 |
| 项目7 |
22/11/11 |
22/12/1 |
未开始 |
| 项目8 |
22/11/29 |
22/12/1 |
未开始 |
| 项目9 |
22/11/15 |
22/11/25 |
未开始 |
| 项目10 |
22/11/22 |
22/11/25 |
未开始 |
| 项目11 |
22/11/5 |
22/11/25 |
███████ 15.0% |
| 项目12 |
22/11/20 |
22/11/25 |
未开始 |
| 项目13 |
22/11/15 |
22/11/25 |
未开始 |
| 项目14 |
22/11/17 |
22/11/25 |
未开始 |
| 项目15 |
22/11/10 |
22/11/25 |
未开始 |
| 项目16 |
22/11/19 |
22/11/25 |
未开始 |
| 项目17 |
22/11/8 |
22/11/18 |
未开始 |
| 项目18 |
22/11/3 |
22/11/18 |
████████████████ 33.3% |
| 项目19 |
22/11/8 |
22/11/18 |
未开始 |
| 项目20 |
22/11/13 |
22/11/18 |
未开始 |
| 项目21 |
22/11/13 |
22/11/18 |
未开始 |
| 项目22 |
22/11/1 |
22/11/11 |
███████████████████████████████████ 70.0% |
| 项目23 |
22/11/6 |
22/11/11 |
████████████████████ 40.0% |
| 项目24 |
22/11/6 |
22/11/11 |
████████████████████ 40.0% |
| 项目25 |
22/11/6 |
22/11/11 |
████████████████████ 40.0% |
1.2规则详情：
| 类型 |
区域 |
项目开始日期 |
终止日期 |
项目进度 |
| 测试数据 |
北美 |
11月5日 |
2022/11/30 |
██████ 12.0% |
| 测试数据 |
亚洲 |
11月12日 |
2022/11/30 |
未开始 |
| 测试数据 |
中东 |
10月5日 |
2022/12/4 |
████████████████████████████ 56.7% |
| 名称 |
公式 |
结果 |
| 基础公式 |
REPT("█",(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d"))*50)&" "&TEXT(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d"),"0.0%") |
* |
| 设置颜色 |
IF(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d")<=60%,1,0) |
* |
| 开始天数 |
DATEDIF(E2,TODAY(),"d") |
项目开始到距离现在差3『天』 |
| 计划天数 |
DATEDIF(E2,F2,"d") |
计划项目开始到结束的时间 25『天』 |
| 百分比数 |
|
|
| IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))) |
公式结果是: 0.12 |
|
| rept数据 |
IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d")))*50 |
6 |
| rept字符 |
REPT("█",IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d")))*50) |
██████ |
| 百分格式 |
TEXT(IF(DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))>=1,1,DATEDIF(E2,TODAY(),"d")/IF(DATEDIF(E2,F2,"d")=0,DATEDIF(E2,TODAY(),"d"),DATEDIF(E2,F2,"d"))),"0.0%") |
12.0% |
| 未开始与进行中 |
IF(DATEDIF(E2,TODAY(),"d")/DATEDIF(E2,F2,"d")>0,"进行中进度函数","未开始") |
进行中进度函数 |
标签：11,
F2,
表格,
22,
进度条,
DATEDIF,
WPS,
TODAY,
E2
From： https://www.cnblogs.com/q1104460935/p/16868030.html