如何让Google Sheets自动更新每月及双周日期

以下是两种核心实现方案，分别适用于无需宏的公式场景和需要自动触发的VBA场景：

一、公式方案（无需宏，自动计算） #

1. 每月自动更新日期 #

当当前日期超过单元格内的原始日期时，自动更新为下一个月的同一天；若下一个月无对应日期（如31号），则自动转为当月最后一天。

假设原始日期存于A1，在目标单元格输入公式：

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

公式说明：

• TODAY()：获取当前系统日期
• EOMONTH(A1,1)：计算A1日期下一个月的最后一天
• 先判断当前日期是否过期，再校验下一个月是否存在对应日期，避免生成无效日期

2. 双周自动更新日期 #

当当前日期过期时，自动跳转到最近的未过期双周日期（每14天更新一次）。

假设原始日期存于B1，在目标单元格输入公式：

=IF(TODAY()>B1,B1+14*CEILING((TODAY()-B1)/14,1),B1)

公式说明：

• CEILING((TODAY()-B1)/14,1)：计算过期天数对应的完整双周周期数（向上取整）
• 用周期数乘以14天，加到原始日期上，得到最近的有效双周日期

二、VBA方案（自动触发，无需手动操作） #

如果需要打开文件或切换工作表时自动更新，可使用VBA代码实现：

1. 核心更新函数 #

按Alt+F11打开VBA编辑器，插入模块并粘贴以下代码：

' 更新每月日期
Sub UpdateMonthlyDates()
    Dim rng As Range, cell As Range
    Set rng = Range("A1:A10") ' 替换为你的目标单元格范围
    For Each cell In rng
        If cell.Value <> "" And IsDate(cell.Value) Then
            If Date > cell.Value Then
                Dim nextMonthDate As Date
                nextMonthDate = DateSerial(Year(cell.Value), Month(cell.Value) + 1, Day(cell.Value))
                ' 处理月底无效日期
                If Day(nextMonthDate) <> Day(cell.Value) Then
                    nextMonthDate = DateSerial(Year(cell.Value), Month(cell.Value) + 2, 0)
                End If
                cell.Value = nextMonthDate
            End If
        End If
    Next cell
End Sub

' 更新双周日期
Sub UpdateBiweeklyDates()
    Dim rng As Range, cell As Range
    Set rng = Range("B1:B10") ' 替换为你的目标单元格范围
    For Each cell In rng
        If cell.Value <> "" And IsDate(cell.Value) Then
            If Date > cell.Value Then
                Dim overdueWeeks As Long
                overdueWeeks = Int((Date - cell.Value) / 14) + 1
                cell.Value = cell.Value + overdueWeeks * 14
            End If
        End If
    Next cell
End Sub

2. 设置自动触发 #

在ThisWorkbook或对应工作表对象中添加触发事件：

' 打开工作簿时自动更新
Private Sub Workbook_Open()
    UpdateMonthlyDates
    UpdateBiweeklyDates
End Sub

' 切换到工作表时自动更新
Private Sub Worksheet_Activate()
    UpdateMonthlyDates
    UpdateBiweeklyDates
End Sub

注意：

• 保存文件时需选择.xlsm格式（启用宏的工作簿）
• 首次打开文件需启用宏才能生效

内容的提问来源于stack exchange，提问作者Nur