Date 'DD' not returning '0' in the date

user_15572227 0
Viewed 45

I'm creating a procedure which will return the year and month (number and character format) from 2 months ago.

For example, Month_2M should return the value for February, 02 but instead returns 2. This is an issue as I'm using these values to open a folder named '02. February 2021' but the current path opens '2. February 2021' which does not exist.

 date1 = Format(Date, "DD")

'Declaring year value of 1 month
'This is important to compare datasets from last month
If date1 > 26 Then
Year_2M = Format(Date - 67, "YYYY")

Else
Year_2M = Format(Date - 57, "YYYY")

End If

'Declaring month value of 1 month
'This is important to compare datasets from last month
If date1 > 26 Then
Month_2M = Format(Date - 67, "MM")

Else
Month_2M = Format(Date - 57, "MM")

End If

'This translates the current month from number to character format
MonthChar_2 = MonthName(Month_2M, False)
vba excel
3 Answers

If you want the date from 2 month ago just use the DateAdd function with a month interval and a negative number argument (to subtract):

Dim TwoMonthAgo As Date
TwoMonthAgo = DateAdd("m", -2, Date) 'subtract 2 month from today

Then use Day(TwoMonthAgo), Month(TwoMonthAgo) and Year(TwoMonthAgo) to get day and month as numeric values:

Dim DayNumeric As Long
DayNumeric = Day(TwoMonthAgo)

Dim MonthNumeric As Long
MonthNumeric = Month(TwoMonthAgo)

Dim YearNumeric As Long
YearNumeric = Year(TwoMonthAgo)

Or use the Format function if you need a string with leading zeros:

Dim DayString As String
DayString = Format$(TwoMonthAgo, "dd")

Dim MonthString As String
MonthString = Format$(TwoMonthAgo, "mm")

Dim YearString As String
YearString = Format$(TwoMonthAgo, "yyyy")

Or any other fancy date format:

Dim ISO8601_Format As String
ISO8601_Format = Format$(TwoMonthAgo, "yyyy-mm-dd")
user_32196130

Adding and removing months is actually a pretty common task in VBA. These are the functions I am using for it:

Public Function GetLastDayOfMonth(ByVal myDate As Date) As Date
    GetLastDayOfMonth = DateSerial(Year(myDate), Month(myDate) + 1, 0)
End Function

Public Function GetFirstDayOfMonth(ByVal myDate As Date) As Date
    GetFirstDayOfMonth = DateSerial(Year(myDate), Month(myDate), 1)
End Function

Public Function AddMonths(ByVal myDate As Date, ByVal lngMonth As Long) As Date
    AddMonths = GetLastDayOfMonth(DateAdd("m", lngMonth, myDate))
End Function

Public Function AddMonthsAndGetFirstDate(ByVal my_date As Date, ByVal lngMonth As Long) As Date
    AddMonthsAndGetFirstDate = GetFirstDayOfMonth(DateAdd("m", lngMonth, my_date))
End Function

Public Function DateDiffInMonths(a As Date, b As Date) As Long
    DateDiffInMonths = DateDiff("m", a, b)
End Function

Sub TestMe()

    Debug.Print GetLastDayOfMonth(DateSerial(2020, 2, 22))
    Debug.Print GetLastDayOfMonth(DateSerial(2021, 2, 22))        
    Debug.Print GetFirstDayOfMonth(DateSerial(2021, 2, 22))
    Debug.Print AddMonths(DateSerial(2020, 2, 23), 3)
    Debug.Print AddMonthsAndGetFirstDate(DateSerial(2020, 2, 23), 3)        
    Debug.Print DateDiffInMonths(DateSerial(1988, 8, 18), DateSerial(1998, 10, 18))
    
End Sub
user_54486260

Use Format:

FolderName = Format(DateAdd("m", -2, Date), "mm\. mmmm yyyy")
user_35272970
Related