Google表格：自动将日期增加1个月 [英] Google Sheets: Automatically Increment Date by 1 Month

问题描述

我当前正在使用预算表，该表会自动将日期单元格更新为帐单的下一个付款日期。例如：

I am currently using a budget sheet that automatically updates date cells to the next "payment" date for a bill. As an example:

=VALUE("2019-03-08")+14*FLOOR((NOW()-VALUE("2019-03-08"))/14)

该单元格以日期开头，然后每14天更新一次。这显示了一个单元格，该单元格具有每两周一次付款的日期。在上面的示例中，单元格显示的值是``2019-03-08''，直到2周过去为止。经过14天后，该单元格将自动上传到 2019-03-22。此自动化将以14天为增量永久持续。单元格中显示的日期将始终自动更新为下一个14天日期。这可以帮助我显示每两周一次付款的确切到期日期，而无需更新单元格。

The cell starts with a date and it then updates every 14 days to the new date. This shows me a cell that has the date of the biweekly payment that is due. In the above example, the cell shows the value of "2019-03-08" until 2 weeks have passed. Once 14 days have passed, the cell will automatically upload to "2019-03-22". This automation will continue forever in 14-day increments. The date displayed in the cell will always update automatically to the next 14-day date. This helps me display the exact due date of a biweekly payment without updating the cell.

我想为每月付款做同样的事情，但是使用的是30不是很准确。有没有一种方法可以修改此方法，使我可以将日期自动更新到该月的每月24号。所以我从2/24/2019开始，当点击3/24/2019时，单元格更新到该日期，依此类推。这是不准确的原因是月份并非全部为30天。 2月是28天，有些是31天。我希望单元格在1个月内增加，就像上述公式以14天为增量进行更新一样。例如：日期将显示 2/24/2019，直到下个月的同一天，然后该单元格将更新为 3/24/2019。此过程将永远持续到2019年4月24日，2019年5月24日等。它还将随着年份持续进行，因此12/24/2019将自动更改为01/24/2020。

I want to do the same thing for a monthly payment, but using a day count of 30 is not very accurate. Is there a way to modify this method to let me update the date automatically to the 24th of the month, every month, on that day. So I start with 2/24/2019 and when 3/24/2019 hits, the cell updates to that date, and so on. The reason this is not accurate is that the months are not all 30 day periods. February is 28 days and some are 31 days. I want the cell to increment in 1 month periods in the same way that the above formula updates in 14-day increments. As an example: The date will display "2/24/2019" until the same day on the next month occurs, then the cell will update to "3/24/2019". This process will continue forever with 4/24/2019, 5/24/2019, etc. It will continue with the year as well, so 12/24/2019 will change automatically to 01/24/2020.

更新：更新了有关行为方式和我正在寻找的示例的说明...

UPDATE: Updated the description with examples of how the behavior works and what I am looking for...

推荐答案

此公式将每24日增加一个月份，并且也考虑年份更改

this formula will increase the month every 24th and accounts for year change as well

=IF(DAY(TODAY())>=24,
 DATE(YEAR(TODAY()), MONTH(TODAY())+1, 24),
 DATE(YEAR(TODAY()), MONTH(TODAY()),   24))

这篇关于Google表格：自动将日期增加1个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！