Excel-想要将预计日期与目标日期进行比较 [英] Excel - Want to compare a projected date against a target date

问题描述

在Excel中，我试图将预测日期与目标日期进行比较，并在第三列中说明差异，我搜索了要放在第三列中的公式，例如DATEDIF(F5，G5，"m")，但如果预计日期小于目标日期，则无法说明差异

In Excel, I am trying to compare a forecast date against a target date and state in the 3rd column the difference, I have searched for a formula to put in the 3rd column such as DATEDIF (F5, G5,"m") but this is not able to state the difference if the projected date is less than the target date,

请参见以下示例:

EXCHANGE目标日期EXCHANGE预测日期EXCHANGE差异01/12/2018 11/01/2019 101/08/2017 16/02/2019 1802/08/2017 01/06/2017 #NUM！

EXCHANGE Target Date EXCHANGE Forecast Date EXCHANGE Difference 01/12/2018 11/01/2019 1 01/08/2017 16/02/2019 18 02/08/2017 01/06/2017 #NUM!

推荐答案

使用一些错误处理，如果日期是过去的日期，则将计算结果翻转并乘以-1或从0中减去.不能自己处理，但是好吧...

Use some Error handling, if the date is in the past, flip the calculation and multiply it by -1 or subtract it from 0. It's annoying that the function doesn't handle this itself but oh well...

IFERROR(DATEDIF(F5，G5，"m")，DATEDIF(G5，F5，"m")*-1)

IFERROR(DATEDIF(F5，G5，"m")，0-DATEDIF(G5，F5，"m"))

要评估周数，您将使用相同的函数，但以天为单位进行计算，然后可以将结果除以7(如果您不想将结果除以 ROUND()函数，十进制答案)

To evaluate weeks you would use the same function but taking the difference in days, you can then divide the result by 7 (make use of the ROUND() function if you don't want a decimal answer)

IFERROR(DATEDIF(F5，G5，"D")/7,0-DATEDIF(G5，F5，"D")/7)

IFERROR(ROUND(DATEDIF(F5，G5，"D")/7,0)，ROUND(0-DATEDIF(G5，F5，"D")/7,0))

这篇关于Excel-想要将预计日期与目标日期进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！