CODEWITHSUNDEEP
excelvba
lakshmen
lakshmen

Reputation: 29064

How to get date - 1 year in vba

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.

What I have tried so far:

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.

Need some guidance on doing this.

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

Upvotes: 0

Views: 3827

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Use DateAdd:

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

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)

Upvotes: 3

Related Questions