excel - 更改日期值并保留格式

Question

我想替换列中重复的年份日期。

我从单个模块中的嵌套 for 循环开始，然后我更改了用于其相关模块中每个工作表的代码。

我尝试将重复的年份替换为空，然后添加正确的年份，但它卡在中间。

Sub change_dates()

Dim wb As Workbook
Dim o As Long
Dim k As Long
Dim y As Long

Set wb = ThisWorkbook

y = Year(Date)
o = 2
k = wb.Worksheets(11).Cells(2, 10).Value + 2

Do While o < k
    If Mid(wb.Worksheets(11).Cells(o, 1), 4, 2) = 12 Then
        y = y - 1
    End If
    wb.Worksheets(11).Cells(o, 1) = Left(wb.Worksheets(11).Cells(o, 1), 6) & CStr(y)
    o = o + 1
Loop

End Sub

运行代码后的单元格值:

...14/03/201414/02/201414/01/201413/12/201313/11/201313/10/201313/09/201313/08/201313/07/201313/06/201313/05/201313/04/201313/03/201313/02/201313/01/2013    12-12-2012    11-12-2012    10-12-2012    09-12-2012    08-12-2012    07-12-2012    06-12-2012    05-12-2012    04-12-2012    03-12-2012    02-12-2012    01-12-2012    12-11-2011    11-11-2011    ...

第一部分是我想要的。在第二部分中，月份和格式也在发生变化。

列的原始值。

...22-01-202221-12-202221-11-202221-10-202221-09-202221-08-202221-07-202221-06-202221-05-202221-04-202221-03-202221-02-202221-01-202220-12-202220-11-202220-10-202220-09-202220-08-202220-07-202220-06-2022...

这是期望的结果:

22-01-202221-12-202121-11-202121-10-202121-09-202121-08-202121-07-202121-06-202121-05-202121-04-202121-03-202121-02-202121-01-202120-12-202020-11-202020-10-202020-09-202020-08-202020-07-2020...

进一步说明:

• 我得到日期是 =A2+1 的结果。
• A 列单元格的格式均为 dd-mm-yyyy
• k 正在返回正确的数字。
• 结果应该是，所有的天数和月份都保持不变，重复的年份 2022 变成了从 2022 年开始的年份，每到 12 月就从上到下减少一个。

Answer 1

问题是，如果您使用像 Mid() 或 Left() 这样的文本/字符串函数，您会从实际数字日期(您实际上可以计算)更改为一个看起来像日期但只是文本的文本(你不能再用它来计算了)。并且 Excel 不知道这是一个日期。

因此，无论何时处理日期，都使用诸如 Day()、Month() 和 Year() 之类的数字日期函数将日期拆分为部分并使用 DateSerial(y, m, d) 将新日期放在一起。这将创建一个您可以用来计算的真实数字日期，并且您可以使用 .NumberFormat 对其进行格式化。

我将您的 Do 循环更改为 For 循环，在 Next o 上自动递增 o，这看起来更干净一些。

Public Sub change_dates()
    Dim ws As Worksheet  ' define your worksheet only once
    Set ws = ThisWorkbook.Worksheets(11) ' if it ever changes from 11 to 12 it only needs to be changed here
    
    Dim y As Long
    y = Year(Date)
    
    Dim k As Long
    k = ws.Cells(2, 10).Value + 1
    
    Dim o As Long
    For o = 2 To k  ' loop from 2 to k
        Dim m As Long  ' get month of current cell
        m = Month(ws.Cells(o, 1))
        
        Dim d As Long  ' get day of current cell
        d = Day(ws.Cells(o, 1))
        
        If m = 12 Then  ' check if year needs to change
            y = y - 1
        End If
        
        ws.Cells(o, 1) = DateSerial(y, m, d)  ' create a real numeric date and write it to the cell
        
        ' if the date needs to show in another format just change the numberformat to whatever you need
        'ws.Cells(o, 1).NumberFormat = "dd-mm-yyyy"
    Next o
End Sub