如何获得日期 - 1年在vba [英] How to get date - 1 year in vba

问题描述

我有一行交易日期，存储在A列。它包含交易日，因此，它不包含一年中的所有日子。我想得到一个特定的日期，并从中删除一年。我想在同一列中找到newdate单元格的索引。如果不可能，我想找到下一个最近的日期。

I have a row of trading dates, stored in Column A. It contains trading days and hence, it doesn't contain all the days in a year. I would like to get a particular date and remove one year from it. I would like to find the index of the newdate cell within the same column. If not possible I would like to find the next closest date.

我迄今为止所尝试的：

Dim date1 As Double
date1 = Sheets("Part2").Cells(i, 1).Value
Dim matchRow As Integer
matchRow = 3
While Sheets("1.A").Cells(matchRow, 1).Value <> date1
matchRow = matchRow + 1
Wend

我可以得到一个特别的日期表，但现在我需要得到一年前的日期，如果不是之后的最近日期。

I am able to get a particular date in sheet but now I need to get the date one year before that, if not next nearest date after that.

需要一些指导。

还试过：
表格（Part2）。单元格（i，1）.Value -365。它不工作..

Also tried: Sheets("Part2").Cells(i, 1).Value -365. It is not working..

推荐答案

使用 DateAdd ：

DateAdd("yyyy",-1,Sheets("Part2").Cells(i, 1).Value)

/ strong>，因为您的日期以格式yyyymmdd存储为文本，并从第№3行开始，请使用以下命令：

UPD since your dates are stored as text in format "yyyymmdd" and starts from row №3, use this one:

Dim date1 As Date
Dim srtDate1 As String, srtDate2 As String
Dim matchRow

strDate1 = Sheets("Part2").Cells(i, 1).Value
date1 = DateSerial(Left(strDate1, 4), Mid(strDate1, 3, 2), Right(strDate1, 2))
srtDate2 = Format(DateAdd("yyyy", -1, date1), "yyyymmdd")

matchRow = Application.Match(CDbl(srtDate2), Sheets("Part2").Range("A:A"), 1)
If IsError(matchRow) Then
    matchRow = 3
Else
    matchRow = matchRow + 1
End If

MsgBox "new date: " & Sheets("Part2").Range("A" & matchRow)

这篇关于如何获得日期 - 1年在vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！