Working with Dates || Excel VBA Master Class || 3.4e

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video. Source code will be presented in the order of the sections within the video.

Practice

Sub DateTypeTutorial()
    
        Dim dt As Date
'Current Date
        dt = VBA.Date
'Current Date & Time
        dt = VBA.Now
'Current Time
        dt = VBA.Time
'Hard Code
      	dt = #1/31/2025#
    
End Sub

Sub DateTypeTutorial()
' Convert to a Whole Number
    Dim dt As Date
    dt = #1/31/2025#
    Dim db As Double
    db = dt
    Debug.Print db

' Or, Simply
    Debug.Print CDbl(dt)
End Sub

Sub DateTypeTutorial()
' Extract Date Component from Date Time
    Dim dt As Date
    dt = #1/31/2025 11:00:00 PM#
    Debug.Print Int(dt)
'Or we can use the DateValue Function
    Debug.Print DateValue(dt)
End Sub


Sub DateTypeTutorial()
' Valid Formats
    Dim dt As Date
    dt = "31-01-2025"
    dt = "January, 31 2025"
' Invalid Formats
    dt = "20250131"
End Sub

Date Serial

Sub DateTypeTutorial()
    
    Dim dt As Date
'    dt = "20250131"
    dt = DateSerial(2025, 1, 31)
    Debug.Print dt
            
End Sub


Sub DateTypeTutorial()
    
    Dim strDate As String
    strDate = "20250131"
    Dim strYear As String, strMonth As String, strDay As String
    strYear = Left(strDate, 4)
    strMonth = Mid(strDate, 5, 2)
    strDay = Right(strDate, 2)
    
    Dim dt As Date
    dt = DateSerial(CInt(strYear), CInt(strMonth), CInt(strDay))
    Debug.Print dt
            
End Sub


Sub DateTypeTutorial()
    
    Dim dt As Date
    dt = DateSerial(2025, 1, 31) + TimeSerial(10, 10, 10)
    
    Debug.Print dt
            
End Sub

Date Part

Sub DateTypeTutorial()
    
    Dim dt As Date
    dt = #1/31/2025#
    
    Debug.Print DatePart("yyyy", dt)
            
End Sub

Date Add & Diff

Sub DateTypeTutorial()
    
    Dim dt As Date
    dt = #1/31/2025#
    
    'Debug.Print DateAdd("yyyy", 1, dt)
    Debug.Print DateAdd("yyyy", -1, dt)
            
End Sub


Sub DateTypeTutorial()
    
    Dim dt1 As Date, dt2 As Date
    dt1 = "01-04-25"
    dt2 = "01-08-25"
    'Debug.Print DateDiff("m", dt1, dt2)
    Debug.Print DateDiff("m", dt2, dt1)
End Sub

Weekday

Sub DateTypeTutorial()
    
    Dim dt As Date
    dt = "31-01-2025"
    
    'Debug.Print Weekday(dt)
    'Debug.Print Weekday(dt, vbMonday)
    'Debug.Print WeekdayName(Weekday(dt, vbMonday))
    Debug.Print MonthName(DatePart("m", dt))
    
End Sub

Format

Sub DateTypeTutorial()
    
    Dim dt As Date
    dt = "31-01-2025"
    'Jan-25
    Dim strMonth As String
    strMonth = Left(MonthName(DatePart("m", dt)), 3)
    Dim strYear As String
    strYear = Right(CStr(DatePart("yyyy", dt)), 2)
    
    Debug.Print strMonth & "-" & strYear
    
End Sub

Sub DateTypeTutorial()
    
    Dim dt As Date
    dt = "31-01-2025"
    'Jan-25

    Debug.Print Format(dt, "MMM-yy")
    
End Sub

Is Date?

Sub DateTypeTutorial()
    
    Dim strDate As String
    'dt = "31-01-2025"
    strDate = "31-01-2025"
    
    If IsDate(strDate) = True Then Debug.Print Format(strDate, "MMM-yy")

    
End Sub

Related Posts