如何根据发票日期和付款条件,自动带出应收款项到期日(Excel/Ragic 公式教学)?
B2B 交易时,为了方便客户并提供周转时间,通常不会在交易当下收款,而是将这笔款项列为应收帐款,并根据双方议定的付款条件(如月结 30 天),定一个未来的收款日期。
对负责管理收款事宜的公司人员来说,掌握好每笔帐款的应收日期,知道什么时候得催帐,才能避免延迟收款,影响公司的资金周转和现金流稳定性。
然而,处理过这类帐款的人都知道,应收日期的计算,很多时候不是“出货日期是 3 月 16 号,月结 30 天就是 4 月 15 日收款”这样直接加 N 天的直线逻辑,而可能要搭配特定的基准,如公司固定的“结帐日”、“付款日”,做出更复杂的推入算。
在这篇文章里,我们会教你如何用 Excel 公式来计算应收日期,并加码告诉你如何在 Ragic 里套公式,让应收日期的计算更有弹性。(Ragic 是一个跟 Excel 很类似,但在数据串连上能更节省时间的工具。它的子表格可以一目了然列出购买商品明细,还能设置帐款到期提醒,自动发信通知客户缴款。如果你对于这样的功能有兴趣的话,可以试试看 Ragic,或者是你原来就是 Ragic 订户的话,更可以参阅这个教学。)
现在就让我们一起看看,如何透过 Excel 和 Ragic 自动推入算收款日期,减少手动操作及失误,提升工作效率吧!
帐款到期日计算的相关名词介绍
在开始编写公式之前,为了帮助你理解后续计算逻辑,这里先介绍几个计算应收日期时常用的重要名词:
发票日期
发票日期指的是公司开立发票的日期,通常发生在商品出货或服务完成时,也就是交易完成的日期。这是计算应收帐款到期日的重要依据,客户的付款期限通常从这个日期开始计算。
结帐日
结帐日是公司每月定期结算货款的日期,通常设置在每月的某一天(例如每月 25 日)。当发票日期在当月结帐日之前,该笔货款会归入当月帐款;若发票日期超过结帐日,则归入下个月的帐款。
付款条件
买卖双方通常会协议一个付款条件,并依此条件进行货款的收付。常见的付款条件有月结 30 、60 、90 或 120 天,应收帐款的到期日将根据月结天数来计算。
收款日
为了便于帐务管理,公司通常会设置一个固定的每月收款日(例如每月月底)。若客户同意,这个日期将作为应收款项的支付日期。
月结计算的三种常见逻辑
在计算应收帐款到期日时,常见的月结计算逻辑有以下三种:
1. 简单版:发票日期直接加月结天数(结帐日 = 月底,没有统一收款日)
这是最直观的计算方式,只需根据发票日期和月结条件来计算。例如,若发票日期为 7/11,月结条件为 30 天,则到期日为 8/10。
2. 进阶版:根据固定结帐日与收款日判断应收日期(最常用)
为了简化帐务处理,不少公司除了会设置固定的结帐日之外(常见为每月 25 日),也会设置一个固定的收款日,作为和大部分客户往来的帐款到期日。这个固定的收款日可以是每月的某一天(例如每月 5 日)或是月底,这是一般比较常见的做法。
以上图为例,假设 A 公司的结帐日为每月 25 日,收款日为每月月底。若发票日期为 7 月11 日,因日期落在结帐日 7 月 25 日 之前,因此这笔属于 7 月货款。根据月结条件(月结 60 天),从 7 月 25 日结帐日往后算 60 天为 9 月 23 日,而最接近的收款日为 9 月 30 日。因此,这笔帐款的到期日为 9 月 30 日。
3. 变化版:根据特定客户改变结帐日和收款日
在计算应收日期时,一般是以卖方设置的收款日为基准,但有时在面对规模较大或知名度较高的客户时,因为对方议价能力较强,卖方通常需要根据对方要求的付款日来调整计算方式。
例如,若 B 客户要求每月 10 日为固定付款日,即便 A 公司单击照月结 30 天计算的收款日是 9/30,最终仍需调整为 10/10。这类收款日期依客户付款日调整变化的情况通常是特例,仅适用于特定几笔帐款。
此外,若公司的结帐日与收款日并非固定的 25 日与月底,可以将这些日期设成字段,让公式能根据不同的结帐日和收款日进行弹性计算。这种方式更有弹性,可以根据各公司不同的情况做调整。
月结公式计算教学
一、简单版:发票日期直接加上月结天数
1. Excel 作法
(1) 如果你已经有一份记录发票日期和付款条件的 Excel 表单,要计算应收日期时,可以在表单上加入一个“预计收款日”的字段。
(2) 在计算预计收款日时,可以依照付款条件,将发票日期加上相对应的天数。假设发票日期保存在 A 栏,付款条件保存在 I 栏,则可以设置如下公式来计算预计收款日:
=IF(I2="月结 30", A2+30, IF(I2="月结 60", A2+60, IF(I2="月结 90", A2+90, IF(I2="月结 120", A2+120,""))))
设置完成后,Excel 会根据付款条件自动计算并显示预计收款日。
2. Ragic 作法
在接下来的教学中,我们将以“应收帐款”快速范本作为范例,但你也可以将这些步骤应用到自己创建的发票表单上。
(1) 在范本中,开立日期位于 A2 字段,收款条件则位于 A4 字段。因此在设计模式中,我们可以使用条件公式来根据付款条件,将发票开立日期加上相应的天数,来计算“应收日期”:
IF(A4.RAW="月结 30 (Net 30)", A2+30, IF(A4.RAW="月结 60 (Net 60)", A2+60, IF(A4.RAW="月结 90 (Net 90)", A2+90, IF(A4.RAW="月结 120 (Net 120)", A2+120,""))))
(2) 公式设置完成后,只需在表单中填入发票日期和收款条件,系统即可自动计算出对应的应收日期。
二、进阶版:固定结帐日与收款日
如果公司结帐日是每月 25 日,收款日为每月月底,如何依据发票日期和付款条件来计算应收帐款到期日?
我们可以用条件公式和日期公式 DAY () 来判断发票日期是否已超过结帐日(25 日)。如果超过,则属于下月帐款,帐款到期日将延后一个月,再依照付款条件推入算(例如月结 30 天则加一个月,60 天则加两个月,以此类推入),并让到期日落在月底。若发票日期在 25 日或之前,则该款项仍归属于当月帐款,并依据付款条件推入算到期日,使其落在月底。
举例来说,假设有两张发票,付款条件皆为月结 30 天:
为了确保到期日落在月底,我们可以用 Excel 和 Ragic 都支持的 EOMONTH 函数。这个函数可根据指定的日期和相隔的月份数,计算并传回该月的最后一天作为到期日。
1. Excel 作法
(1) 我们可以在“预计收款日”字段上,将公式设置为:
=IF(DAY(A2)>25,EOMONTH(A2, IF(I2="月结 30", 2, IF(I2="月结 60", 3, IF(I2="月结 90", 4, IF(I2="月结 120", 5, 0))))), EOMONTH(A2, IF(I2="月结 30", 1, IF(I2="月结 60", 2, IF(I2="月结 90", 3, IF(I2="月结 120", 4, 0))))))
这个公式会先检查发票日期是否超过 25 日。如果超过 25 日,则会依照月结条件(如月结 30 天、60 天等)额外加上一个月,并将到期日设为该月的月底。如果发票日期是 25 日或之前,则直接依据月结条件计算到期日,并确保到期日落在该月的月底。
(2) 设置完成后,系统就能自动计算出预计收款日。
2. Ragic 作法
(1) 我们一样以“应收帐款”快速范本作为范例,并将应收日期字段的公式设置为:
IF(DAY(A2)>25,EOMONTH(D2, IF(D4.RAW="月结 30 (Net 30)", 2, IF(D4.RAW="月结 60 (Net 60)", 3, IF(D4.RAW="月结 90 (Net 90)", 4, IF(D4.RAW="月结 120 (Net 120)", 5, 0))))), EOMONTH(D2, IF(D4.RAW="月结 30 (Net 30)", 1, IF(D4.RAW="月结 60 (Net 60)", 2, IF(D4.RAW="月结 90 (Net 90)", 3, IF(D4.RAW="月结 120 (Net 120)", 4, 0))))))
公式逻辑和 Excel 类似,在判断发票日期是否超过 25 日后,再依据月结条件来推入算应收日期。
(2) 设置完成后,只要输入发票日期和付款条件后,系统就会自动计算出应收日期。
三、变化版:根据特定客户改变结帐日和收款日
1. 依据客户改变付款日
在面对规模较大或知名度较高的客户时,可能会需要依照对方指定的付款日作为应收款项的到期日。那么,该如何让系统自动计算应收日期呢?
通常这类客户的付款日会晚于公司原本设置的应收日期,因此关键在于判断新的应收日期应该落在原本设置的月份,还是顺延至下个月。可以依照以下步骤进行设置:
(1) 新增一个数值字段 “客户付款日”(A5),用来设置客户的付款日。
(2) 新增一个“原应收日期推入算”字段(G2),并设置以下公式(与上一阶段进阶常用版的“应收日期”公式相同):
IF(DAY(A2)>25,EOMONTH(D2, IF(D4.RAW="月结 30 (Net 30)", 2, IF(D4.RAW="月结 60 (Net 60)", 3, IF(D4.RAW="月结 90 (Net 90)", 4, IF(D4.RAW="月结 120 (Net 120)", 5, 0))))), EOMONTH(D2, IF(D4.RAW="月结 30 (Net 30)", 1, IF(D4.RAW="月结 60 (Net 60)", 2, IF(D4.RAW="月结 90 (Net 90)", 3, IF(D4.RAW="月结 120 (Net 120)", 4, 0))))))
(3) 新增一个数值字段“取原应收日的日期”(G3),并设置公式 DAY(G2),用来抽取应收日期的日数部分。
(4) 在“应收日期”字段设置以下公式:
IF(A5>=G3, DATE(YEAR(G2), MONTH(G2),A5), DATE(YEAR(G2), MONTH(G2)+1,A5))
这个公式的逻辑是:
• 若“客户付款日”的日数较小,应收日期则自动延期至下个月。
这样的设置下,即使“客户付款日”未输入,系统仍能正确计算应收日期。
(5) 公式设置完成后,只要输入发票的开立日期、月结条件以及客户指定的付款日,即可自动推入算出应收日期。
2. 自行设置结帐日、收款日
如果公司的结帐日不在每月 25 日,且收款日也不是一般常见的月底,你可以将结帐日和收款日分别设为字段,并新增几个字段来判断在每个条件下,应收日期的月份数应增加多少,从而推入算出应收日期。具体判断条件如下:
• 根据月结条件,计算需要增加的月份数
• 判断收款日是否小于结帐日:若小于,则计算为下月收款(应收日期加一个月)
你可以参阅以下步骤来设计表单:
(1) 在表单上新增结帐日(A5)、收款日(D5)两个数值字段。
(2) 新增一个“超过当月结帐日?”的数值字段(G2),并将公式设置为:
IF(DAY(A2)>A5,1,0)
这样的设置代表当发票日期超过结帐日时,将会显示为 1,表示为下月帐款,月份数要加 1;若未超过,则显示为 0,表示为当月帐款。
(3) 再新增一个“收款条件加月数”的数值字段(G3),用来判断应收日期要根据月结条件多加几个月。比如,月结 30 天需多加一个月,月结 60 天多加两个月,依此类推入。公式可以设置为:
IF(A4.RAW="月结 30 (Net 30)", 1, IF(A4.RAW="月结 60 (Net 60)", 2, IF(A4.RAW="月结 90 (Net 90)", 3, IF(A4.RAW="月结 120 (Net 120)", 4, 0))))
(4) 新增一个名为“收款日小于结帐日?”的数值字段(G4),用来判断如果收款日早于结帐日,则应收日期需要多加一个月,否则为 0。公式可以设置为:
IF(D5 < A5,1,0)
(5) 在应收日期字段,将公式设置为:
DATE(YEAR(A2),MONTH(A2)+G2+G3+G4,D5)
这个公式会依据前面三个条件判断字段所增加的月份数,计算出应收日期应该落在发票开立日期后的几个月,并将日期设为收款日。
(6) 公式设置完成后,便能依据公司的结帐日、收款日自动计算应收日期。