☰

如何根据开始日期和结束日期平等计算收入？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function accounting
如何根据开始日期和结束日期平等计算收入？

我的公司根据不太明确的开始/结束日期来分阶段确定收入，我需要根据这些日期来计算当年的收入。

如果合同在当月 16 日或之后开始，则收入将从下个月开始。如果合同在当月 15 日或之前开始，则收入将从当月开始：

例如：

Dec-15  16/11/2015  15/12/2015
Jan-16  16/12/2015  15/01/2016
Feb-16  16/01/2016  15/02/2016

如果该合同价值为 1000 英镑，我的公司将分期支付收入一样按月计算。

示例 1：

Contract start= 16/06/2016 means revenue will start in June 2016
Contract end =  23/02/2017 means revenue will end in March 2017

示例 2：

Contract start= 16/06/2016 means revenue will start in June 2016
Contract end = 04/02/2017 means revenue will end in Feb 2017

在示例 1 中，这将导致总收入被分成 10 个月。

在示例 2 中，这将导致总收入被分成 9 个月。

如果需要进行计算，则根据此相等的收入分期（月）来分割总收入，然后计算出有多少属于 2016 年。

答案1

Dave 简化了 Mikey 的问题，因此它不是那么开放。让我重申一下。我们知道合同何时开始和结束。有规则来决定收入阶段何时开始和结束。我们想要找到两件事：

  1. 收入阶段有多少个月？
  2. 2016年的收入是多少。

我将找出开始的那一年的收入，无论那是哪一年。

收入阶段的月份数

我已经为以下单元格定义了名称。Contract.value定义为 1000。

Start  $C$3
Stop   $D$3
Months $E$3

Start并根据合同开始和结束日期计算。我们需要从到的Stop月份数，以便找到“均等收入阶段”。这真的很容易。使用 DATEDIF 或检查StartStop如何在 Excel 中计算两个日期之间的月份数以了解替代方法。以下是单元格的公式E3。

=DATEDIF(Start,Stop,"M")+1

表示+1包含第一个月和最后一个月的月份数。示例中，从 2016 年 7 月到 2017 年 3 月共有 9 个收入月份。

以下说明如何查找 2016 年（收入阶段开始的年份）的收入。

查找 2016 年的收入

首先，找出 2016 年的月份数。CellC2计算如下：

=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1

计算收入阶段中一个月的收入乘以月数。单元格D2执行此操作：

=C2*Contract.value/Months

将C2和合并D2为一个公式会在单元格中显示相同的结果E2。

=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months

这种方法有一些缺点。如果你想要找到 2017 年的收入怎么办？2017 年的公式遵循与 相同的模式C2，但并不相同。如果收入阶段跨越两年以上，事情就会变得混乱。

这个问题可以通过制作一个简化的摊销表并使用来概括数据透视表进行总结。Start和Stop是Months列出月份和收入所需的全部内容。数据透视表查找收入阶段中每年的收入。

使用数据透视表

行2很特殊。

以下是 的公式A2:C2。

=Start
=YEAR(A2)
=Contract.value/Months

使用公式来A3:C3填充Fill Down列表。填充行数以允许收入阶段中尽可能多的月份。添加更多很容易。

=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"")
=IF(A3<>"",YEAR(A3),"")
=IF(A3<>"",Contract.value/Months,"")

“诀窍”在于A3。如果上一行的列中的日期A在 之后Stop，则列中的单元A格为空白，并且后面的每一行都为空白，因此收入阶段的每个月都会有一行——不多也不少。当您想要制作列表，但事先不知道行数时，此诀窍很有用。

要制作数据透视表，请选择整个列表，包括空白行。通过选择整个列表，即使您更改合同日期，数据透视表仍将正常工作。使用字段Year作为数据透视表行，使用Sum of Revenue和Count of Year作为摘要字段。

答案2

  • 单元格 A1：合同开始日期
  • 单元格 B1：合同结束日期

  • 单元格 C1：合同开始的月份索引：

    =YEAR(A1-15) * 12 + MONTH(A1-15) + 1

  • 单元格D1：合同结束的月份索引：

    =YEAR(B1-15) * 12 + MONTH(B1-15) + 1

  • 单元格 E1：假设合同总金额为 1000 美元，则按期付款

    =1000/(D1-C1)

  • F1 单元格：2016 年的月份数

    =24205 - C1

  • 单元格 G1：2016 年薪酬总额

    =F1*E1

相关内容