In Excel 2010, I need to determine the amount of days within a date range that fall within another specific date range.
在Excel 2010中,我需要确定日期范围内属于另一个特定日期范围的天数。
An example of this is that our client has utilized our services from 03/08/2015 to 04/08/2015 but our billing cycle is from 04/01/2015-04/30/2015. That being said, we are required to bill the client for the service days within the billing cycle.
这方面的一个例子是我们的客户在2015年8月3日至2015年8月4日期间使用了我们的服务,但我们的结算周期是04/01 / 2015-04 / 30/2015。话虽如此,我们需要在结算周期内向客户收取服务天数。
What is an Excel or Google Sheets formula to determine the amount of service days between the billing cycle dates.
什么是Excel或Google表格公式,用于确定结算周期之间的服务天数。
Service Start Date 3/8/15 Service End Date 4/8/15
服务开始日期3/8/15服务结束日期4/8/15
Billing Cycle Start Date 4/1/15
Billing Cycle End Date 4/30/15
结算周期开始日期4/1/15结算周期结束日期4/30/15
Expected Result 8
预期结果8
2 个解决方案
#1
Here is an example
这是一个例子
In Column A Actual Billed Time Range A2 Start of Billing A3 End of Billing
在A栏实际开票时间范围A2开始开票A3开票结束
In Column B Billing Cycle B2 Start of Billing Period B3 End of Billing Period
在B栏计费周期B2开始计费期B3结算期结束
In Column C Formulas Place the following in C2
在C列公式中将以下内容放在C2中
=IF(B2>A2,B2,A2)
This checks the start date of Actual Time Billed vs the Monthly Billing Start and if the Actual Time Billed date is less then it will start with the Monthly Billing Start Date
这将检查实际开票时间与每月开票日期的开始日期,如果实际开票日期少于那么它将从每月开票日期开始
Place the following in C3
将以下内容放在C3中
=IF(B3<A3,B3,A3)
This checks the end date of Actual Time Billed vs the Monthly Billing end date and if the Actual Time Billed date is greater then it will end with the Monthly Billing end date.
这将检查实际开票时间与每月开票结束日期的结束日期,如果实际开票时间更长,那么它将以每月开票结束日期结束。
To get your result place the following in D2 (format this cell as general and not date to get the proper result)
要获得结果,请将以下内容放在D2中(将此单元格格式化为常规格式而非日期格式以获得正确的结果)
=C3-C2+1
or for a simpler looking sheet enter the following into C2 instead (format this cell as general and not date to get the proper result)
或者对于更简单的表单而言,将以下内容输入C2(将此单元格格式化为常规而不是日期以获得正确的结果)
=(IF(B3<A3,B3,A3))-(IF(B2>A2,B2,A2))+1
It just combines the cells in Column C and D into one cell for a nicer look I unfortunately can not post images to show it working as i am fairly new here.
它只是将C列和D列中的单元格组合成一个单元格以获得更好的外观我很遗憾无法发布图像以显示它正常工作,因为我在这里相当新。
#2
If you have Service Start Date in A3 and Service End Date in B3 then put the Billing Cycle start and end dates in C1 and C2 respectively, then you can use this formula in C3 for the number of days in that Billing Cycle
如果您在A3中有服务开始日期,在B3中有服务结束日期,则分别将结算周期开始日期和结束日期分别放在C1和C2中,然后您可以在C3中使用此公式表示该结算周期中的天数
=MAX(0,MIN(C$2,$B3)-MAX(C$1,$A3)+1)
You may have to adjust the formula depending on whether you are including start or end dates in the calculation (how many days do you expect in the previous billing cycle for your example?)
您可能需要根据您是否在计算中包括开始日期或结束日期来调整公式(在您的示例的前一个结算周期中,您期望多少天?)
This formula can be copied across and down as required, so you can have service periods which go across many billing periods, just add the additional dates in rows 1 and 2..... or you can also add multiple service periods in columns A and B
此公式可以根据需要反复复制,因此您可以拥有跨越多个结算周期的服务期,只需在第1行和第2行中添加其他日期.....或者您也可以在列A中添加多个服务期和B.
#1
Here is an example
这是一个例子
In Column A Actual Billed Time Range A2 Start of Billing A3 End of Billing
在A栏实际开票时间范围A2开始开票A3开票结束
In Column B Billing Cycle B2 Start of Billing Period B3 End of Billing Period
在B栏计费周期B2开始计费期B3结算期结束
In Column C Formulas Place the following in C2
在C列公式中将以下内容放在C2中
=IF(B2>A2,B2,A2)
This checks the start date of Actual Time Billed vs the Monthly Billing Start and if the Actual Time Billed date is less then it will start with the Monthly Billing Start Date
这将检查实际开票时间与每月开票日期的开始日期,如果实际开票日期少于那么它将从每月开票日期开始
Place the following in C3
将以下内容放在C3中
=IF(B3<A3,B3,A3)
This checks the end date of Actual Time Billed vs the Monthly Billing end date and if the Actual Time Billed date is greater then it will end with the Monthly Billing end date.
这将检查实际开票时间与每月开票结束日期的结束日期,如果实际开票时间更长,那么它将以每月开票结束日期结束。
To get your result place the following in D2 (format this cell as general and not date to get the proper result)
要获得结果,请将以下内容放在D2中(将此单元格格式化为常规格式而非日期格式以获得正确的结果)
=C3-C2+1
or for a simpler looking sheet enter the following into C2 instead (format this cell as general and not date to get the proper result)
或者对于更简单的表单而言,将以下内容输入C2(将此单元格格式化为常规而不是日期以获得正确的结果)
=(IF(B3<A3,B3,A3))-(IF(B2>A2,B2,A2))+1
It just combines the cells in Column C and D into one cell for a nicer look I unfortunately can not post images to show it working as i am fairly new here.
它只是将C列和D列中的单元格组合成一个单元格以获得更好的外观我很遗憾无法发布图像以显示它正常工作,因为我在这里相当新。
#2
If you have Service Start Date in A3 and Service End Date in B3 then put the Billing Cycle start and end dates in C1 and C2 respectively, then you can use this formula in C3 for the number of days in that Billing Cycle
如果您在A3中有服务开始日期,在B3中有服务结束日期,则分别将结算周期开始日期和结束日期分别放在C1和C2中,然后您可以在C3中使用此公式表示该结算周期中的天数
=MAX(0,MIN(C$2,$B3)-MAX(C$1,$A3)+1)
You may have to adjust the formula depending on whether you are including start or end dates in the calculation (how many days do you expect in the previous billing cycle for your example?)
您可能需要根据您是否在计算中包括开始日期或结束日期来调整公式(在您的示例的前一个结算周期中,您期望多少天?)
This formula can be copied across and down as required, so you can have service periods which go across many billing periods, just add the additional dates in rows 1 and 2..... or you can also add multiple service periods in columns A and B
此公式可以根据需要反复复制,因此您可以拥有跨越多个结算周期的服务期,只需在第1行和第2行中添加其他日期.....或者您也可以在列A中添加多个服务期和B.