根据当前日期优化自动填写日期

I need to auto fill a specific column with a date. If the date occurs between Saturday through Tuesday, I need to auto fill the date column with the previous Friday. If the date occurs between Wednesday and Friday I need to put in the coming Friday date for the auto fill.

我需要使用日期自动填充特定列。如果日期发生在星期六到星期二之间,我需要在上周五自动填写日期列。如果日期发生在星期三和星期五之间,我需要在即将到来的星期五日期进行自动填充。

For example: If I run the spreadsheet on Tuesday 10/23 I need the auto fill date to be Friday 10/19. If I run the spreadsheet on Wednesday 10/24 I need the auto fill date to be Friday 10/26.

例如:如果我在10月23日星期二运行电子表格,我需要自动填写日期为10月19日星期五。如果我在10月24日星期三运行电子表格,我需要自动填写日期为10月26日星期五。

Here is the formula I have so far, so I need to invoke this formula via macro when saving the spreadsheet or by clicking a custom button. Any assistance would be greatly appreciated.

这是我到目前为止的公式,所以我需要在保存电子表格或单击自定义按钮时通过宏调用此公式。任何帮助将不胜感激。

=IF(ISBLANK($A2),"",IF(WEEKDAY(TODAY())<4,(TODAY()+(-1-WEEKDAY(TODAY()))),(TODAY()+(6-WEEKDAY(TODAY())))))

1 个解决方案

#1

I'd suggest using VBA for what you want, something like this which would go in the ThisWorkbook module and run prior to the workbook saving:

我建议将VBA用于你想要的东西,这样的东西可以放在ThisWorkbook模块中并在工作簿保存之前运行:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Check if A2 is blank
    If Cells(2, 1) = "" Then Exit Sub
    'Find the date and use it
    Dim x As Long
    Dim dateToUse As Date
    x = Weekday(Date, vbSaturday)
    If x <= 4 Then
        dateToUse = Date - x
    Else
        dateToUse = Date + (7 - x)
    End If
    'Change Cells(1, 1) to the actual target you want to have the date, 
    'which could be determined based upon the contents of your workbook/sheet.
    Cells(1, 1) = dateToUse
End Sub