跳到主要内容

如何在Excel中计算两个日期之间的百分比？

假设我有一个包含开始日期和结束日期的任务列表，现在，我想基于今天的日期计算任务的完成百分比，如下面的屏幕截图所示。 您如何在Excel中解决这项工作？

用公式计算两个日期之间的完成百​​分比

用公式计算两个日期之间的完成百​​分比

要计算两个日期之间的任务完成百分比，请使用以下公式：

1。 在要获取结果的空白单元格中输入以下公式：

=(DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)

备注：在此公式中， B2 是开始日期单元格，并且 C2 是结束日期单元格。

2。 然后，将填充手柄向下拖动到要应用此公式的单元格上，您将获得结果，如下图所示：

3。 继续选择公式单元格，然后单击 百分比样式 在下面 主页 选项​​卡中 联系电话 组，您将获得百分比值，如以下屏幕截图所示：

更多相对百分比文章：

  • 从折价百分比计算原始价格
  • 通常，在我们的日常生活中，有些商品在购物中心打折。 如果您具有显示的以下屏幕折扣价和百分比折扣，那么如何在Excel中计算该产品的原始价格？
  • 计算通过的年或月的百分比
  • 假设您在工作表中有一个日期列表，现在，您想根据给定的日期获取已过去或剩余的年或月的百分比。 您如何在Excel中解决这项工作？
  • 创建具有百分比和价值的图表
  • 对我们来说，向条形图或柱形图添加百分比或值很容易，但是，您是否曾经尝试过创建在Excel中显示百分比和值的柱形图或条形图？
  • Countif在Excel中计算百分比
  • 例如，我有一份研究论文的摘要报告，有三个选项A，B，C，现在我要计算这三个选项中每个选项的百分比。 也就是说，我需要知道选项A占所有选项的百分比。 在本文中，我将讨论如何获得特定选项在范围内的百分比。
  • 超级公式栏 （轻松编辑多行文本和公式）； 阅读视图 （轻松读取和编辑大量单元格）； 粘贴到过滤范围...
  • 合并单元格/行/列 和保存数据； 拆分单元格内容； 合并重复的行和总和/平均值...防止细胞重复； 比较范围...
  • 选择重复或唯一 行; 选择空白行 （所有单元格都是空的）； 超级查找和模糊查找 在许多工作簿中； 随机选择...
  • 确切的副本 多个单元格，无需更改公式参考； 自动创建参考 到多张纸； 插入项目符号，复选框等...
  • 收藏并快速插入公式，范围，图表和图片； 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 提取文字，添加文本，按位置删除， 删除空间; 创建和打印分页小计； 在单元格内容和注释之间转换...
  • 超级筛选 （将过滤方案保存并应用于其他工作表）； 高级排序 按月/周/日，频率及更多； 特殊过滤器 用粗体，斜体...
  • 结合工作簿和工作表; 根据关键列合并表； 将数据分割成多个工作表; 批量转换xls，xlsx和PDF...
  • 数据透视表分组依据 周号，周几等 显示未锁定的单元格 用不同的颜色 突出显示具有公式/名称的单元格...
kte选项卡201905
  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
officetab底部
Comments (24)
No ratings yet. Be the first to rate!
creativekat
creativekat
This comment was minimized by the moderator on the site
Hello - Is there a way to combine both these options?

So that the calculation will show O instead of #NUM! if the date is in the future AND stay at 100% if the end date has passed.

That would be the ideal thing.
Reply
Report
0
0
creativekat
creativekat
This comment was minimized by the moderator on the site
Is there a way to incorporate both these items? I would like it to stay at 100% once the date passes and that it shows 0% instead of the #NUM! error if the date is in the future?
Reply
Report
0
0
Alan
Alan     creativekat
This comment was minimized by the moderator on the site
I am using the formula:

=MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1))

which is working great and leaves the percentage at 100% once the end date has passed.
Reply
0
0
Sebatian
Sebatian
This comment was minimized by the moderator on the site
How can you calculate a date difference in days using a percentage in excel?

Basically, I'm looking to implement a start date for an activity based on the fraction of 15/52. The idea is that an activity starts 3 weeks from a kick-off date.

However, a series of projects have variable durations. As such I want to represent 15 weeks out of 52 weeks in a formula I can use across all projects to work on the same principle.

The idea is to use a start date and project into the future using a percentage to define the next start date for another activity to commence.
Reply
0
0
Murilo
Murilo
This comment was minimized by the moderator on the site
Qual a formula para não dar mais que cem e menos de zero?
Reply
0
0
Натали
Натали
This comment was minimized by the moderator on the site
И как быть, если дата начала не определена, а дата окончания известна. В этом случае по формуле пишет 100%. А хочется чтобы писал 0 или ничего.
Reply
0
0
Натали
Натали
This comment was minimized by the moderator on the site
Привет! Спасибо за формулы!

Что добавить в формулу, чтобы не ругался, если дата начала и окончания совпадают? (предполагается, что задача выполняется в теч. дня.
Reply
0
0
Натали
Натали
This comment was minimized by the moderator on the site
Привет! Спасибо за формулы!! Весь интернет перерыла, нашла только у вас.
Ругается "параметр 2 не может равняться нулю", когда дата начала и дата окончания одна и та же (заложено, что задача делается в течении дня). Что добавить в формулу?
Reply
0
0
adit
adit
This comment was minimized by the moderator on the site
kenapa jika tepat waktu hasil nya menjadi tidak 100%?
Reply
0
0
MandyZhou
MandyZhou     adit
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)).
It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Ona
Ona     MandyZhou
This comment was minimized by the moderator on the site
Thank you for this, help me a lot. But how to show blank if one of the date is not input yet? Because it keep shows 100%.
Reply
0
0
indraputra
indraputra     adit
This comment was minimized by the moderator on the site
waktunya habis jam 12 tgh malam..jadi 12.01 baru akan bertukar kepada 100%. harap membantu
Reply
Report
0
0
MandyZhou
MandyZhou     indraputra
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)). 
It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Carlo
Carlo     MandyZhou
This comment was minimized by the moderator on the site
Hi Mandy,
I am trying to exeute =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)) but it gives me always an error.
I am using a MAC, but I don't think is matter of OS.
THANKS
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
thank you for the formula, when i use it for a dates in the future it returns #NUM!. is there a way to get it to return a 0.00 or some sort of text return like "Not Started"? it would be cleaner and more informative and i have other formulas based on the response so having the option to return a 0.00 (and or text options) would be greatly appreciated. thanks!
Reply
0
0
skyyang
skyyang     Jeff
This comment was minimized by the moderator on the site
Hi, Jeff,To solve your problem, please apply the below formula:=IFERROR((DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1),"Not start").
Please try, hope it can help you!

Reply
Report
1
0
indraputra
indraputra     skyyang
This comment was minimized by the moderator on the site
Hi skyyang, very helpful this one. How about if today date is exceed the end date, how to maintain it at 100% instead of 100 over percent.
Reply
Report
0
0
MandyZhou
MandyZhou     indraputra
This comment was minimized by the moderator on the site
Hello,Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)). It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.Sincerely,Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Indra
Indra     skyyang
This comment was minimized by the moderator on the site
Hi skyyang, very helpfull this one. How about if today date is exceed the end date, how to maintain it at 100% instead of 100 over percent.
Reply
0
0
MandyZhou
MandyZhou     Indra
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)). It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Adib
Adib     Jeff
This comment was minimized by the moderator on the site
same here.
Reply
0
0
MandyZhou
MandyZhou     Adib
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)).

It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
bertina
bertina
This comment was minimized by the moderator on the site
how do i find a 62% percentage date between two dates
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations