企业电子化的专家 Ragic 教你如何利用各种软件、
云服务让公司快速升级!
加入 Ragic 企业电子化的行列!
云数据库
博客
关于Ragic

如何根据发票日期和付款条件,自动带出应收款项到期日(Excel/Ragic 公式教学)?

作者:Maurice Lee

应收款项到期日计算

相关名词介绍

月结计算的三种逻辑

月结公式计算教学

一、简单版:发票日期加月结天数

1. Excel 作法

2. Ragic 作法

二、进阶版:固定结帐日与收款日

1. Excel 作法

2. Ragic 作法

三、变化版:变动结帐日和收款日

1. 依据客户改变付款日

2. 自行设置结帐日、收款日

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,""))))

备注:当条件公式需要参照其他字段的字符串值时,记得在字段名称后加上 .RAW

(2) 公式设置完成后,只需在表单中填入发票日期和收款条件,系统即可自动计算出对应的应收日期。

二、进阶版:固定结帐日与收款日

如果公司结帐日是每月 25 日,收款日为每月月底,如何依据发票日期和付款条件来计算应收帐款到期日?

我们可以用条件公式和日期公式 DAY () 来判断发票日期是否已超过结帐日(25 日)。如果超过,则属于下月帐款,帐款到期日将延后一个月,再依照付款条件推入算(例如月结 30 天则加一个月,60 天则加两个月,以此类推入),并让到期日落在月底。若发票日期在 25 日或之前,则该款项仍归属于当月帐款,并依据付款条件推入算到期日,使其落在月底。

举例来说,假设有两张发票,付款条件皆为月结 30 天:

1 月 24 日开立的发票:因发票日期未超过结帐日(25 日),仍属于当月帐款,月份数为 0。根据月结 30 天条件,将加 1 个月,因此到期日为 2 月底(0 + 1 = 1 个月后)。
1 月 26 日开立的发票:此发票已超过结帐日(25 日),因此属于次月帐款,先加 1 个月,再加上月结 30 天的 1 个月,最终到期日为 3 月底(1+1=2 个月后)。

为了确保到期日落在月底,我们可以用 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))

这个公式的逻辑是:

• 若“客户付款日”(A5)的日数大于或等于“原应收日的日期”(G3),则应收日期便会在同一个月。

• 若“客户付款日”的日数较小,应收日期则自动延期至下个月。

这样的设置下,即使“客户付款日”未输入,系统仍能正确计算应收日期。

(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) 公式设置完成后,便能依据公司的结帐日、收款日自动计算应收日期。

博客背后使用 Ragic! : 最强大的 No Code 企业电子化工具
    把数据放在Excel上不只是拖累团队的行政效率,他也很容易出错并且无法进行任何内控。
    当您的团队成长时,使用Excel管理数据就会越来越痛苦。
    创建你们的第一个云数据库!

    马上登记
    免费试用 Ragic!

    用 Google 帐号登记

    立即科技 Ragic, Inc.
    02-7728-8692
    info@ragic.com
    台北市中正区南昌路二段81号9楼
    用户条款 | 隐私权政策