【Excel日期函数】月底计算利息，一次学会TODAY、DATE、DAY、EOMONTH等函数组合 网志图文版： https://www.b88104069.com/archives/4549 Excel许多日期相关函数，TODAY、DATE是基本，DAY、MONTH、YEAR很好用，EOMONTH是进 阶函数，实务中经常搭配逻辑和文字函数组合运用，本文以会计借款应付利息为例介绍。 公司销货带来应收帐款收现，进货必须应付帐款支付，还有生产运作种种环节的现金收支 ，因此在经营过程中，难免手头上都会有一些短期闲置资金。专注本业的稳健公司，不会 把这些钱从事风险性的股票投资，所以银行短期定存便是这些短期资金最佳的去处。 银行定期存款有几个关键属性：定存金额、利率、发息日、开始日期、定存期间、到期日 ，因为会决定发放多少利息、何时发放利息、何时收回本金。这些在Excel可以利用简单 数学公式计算。不过在会计有个应计基础观念，例如在25日发息好了，那麽在月底便有 5-6天的暂估应收利息，这个需要藉助Excel较为进阶的日期函数公式，以下具体分享： 一、定期存款明细 到期日由开始日期（存入日期）加上定存期间简单计算而来：「=F2+G2」。通常在定存单 上也会有到期日，这里利用Excel公式直接计算，同时也是作为复核机制。 二、TODAY函数 有时候系统跑出来的报表会是所有的定存明细，即使已经到期的也会出来，就算系统不是 如此，也有的时候是内部存档管理需要，不会去删掉已到期的定存。无论哪种情形，我们 都可以透过日期函数TODAY和逻辑函数IF，设计公式判断定存是否到期：「 =IF((TODAY()-H2)>0,"Y","N")」。 TODAY函数是传回系统今天的日期，以这一个步骤来说，当时的日期为「2019/2/5」，函 数的计算结果是每天都在变，这里的是否到期是以当天作为基准。 三、DATE函数 TODAY函数虽然很方便可以得到当天的日期，然而在会计帐务处理，都是以某个期间的月 底最後一日作为基准点，所以还是希望能得到月底日，这里的范例也是如此。 设计一连串函数公式，以便得到当期期末日期：J栏公式为「 =CONCATENATE("20",LEFT(A2,2))」、K栏公式为「=RIGHT(A2,2)」、L栏公式为「 =DATE(CONCATENATE("20",LEFT(A2,2)),RIGHT(A2,2),1)」，这些主要配合A栏的系统年月 期间，以文字函数LEFT取得左边字串、RIGHT取得右边字串，CONCATENATE合并字串，最终 再以DATE函数依照年月日三个参数得到当月的月初日期。 四、EOMONTH函数 有了月初日期，设计公式：「=EOMONTH(L2,0)」，EOMONTH函数顾名思义：「传回所指定 月份数之前或之後的月份最後一天的数列数字」，公式里的「L2」代表开始日期、「0」 代表不往前也不往後，所以正是当月份，如此计算结果即为「2019/1/31」。 图片中的「43466」为Excel的日期序列值，以日期而言便是「2019/1/1」。关於Excel日 期序列值，在下一节第四步骤将有更进一步的说明。这里从「43466」和「43496」相差刚 好30，应该能理解它就是以数字方式纪录日期，以便进行计算。 五、DAY函数 有了月末日期，设计公式：「=DAY(M2))」，表示传回「M2」日期的天数，计算结果为「 31」，亦即1月「当月天数」有「31」天。图片中的「Serial_number」其值为43496。 六、ROUND函数 最後终於可以计算暂估利息了。於O栏设计简单公式：「=IF(I2="Y",0,N2-E2)」，代表如 果已经到期，「暂估利息天数」为「0」，否则以当月天数减掉发息日计算「暂估利息天 数」。P栏公式为「=ROUND(C2*D2/365*O2,0)」，代表定存金额及利率以暂估利息天数计 算的「暂估利息」，加个ROUND函数避免尾差。 七、函数公式模拟测试 配合原始报表会将已到期定存也列入的特性，在上个步骤标黄色部份资料，可以看到「暂 估利息」皆为零。这个步骤测试性地将期间设定为「1902」﹐标黄色部份可以看到「当月 天数」皆为「28」，「暂估利息」也会同步更新，可见此报表公式模型的设计相当完整。 设计函数公式模型的好处 这里的银行定存暂估利息表，其实熟悉Excel基本操作的读者，毋须如此大费周章也能计 算暂估利息。这节范例花了很多心思设计函数公式，用意有两点： 其一：如同第六第七步骤所示，只要将公式模型架好了，几乎是一劳永逸，长期以往可以 提升每月结帐的效率； 其二：这是个很好的范例机会，尝试使用设计较为进阶的日期函数，只要熟悉了这些函数 ，在其他相关的实务个案，例如员工年资、银行借款、帐款帐龄存货库龄，都有可能会用 到，因此值得研习。 会计人的Excel小教室直播课程： https://www.b88104069.com/archives/4535 -- 周末，我们继续Excel：=>21.心得=>5.其他=>3.office 会计人的Excel小教室： https://www.facebook.com/acctexcel --

※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 60.250.152.229 (台湾) ※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Accounting/M.1592471420.A.4B1.html