Date Format - Enclosed with #Date#

  • Date should enclosed with "# Date #"

    •  

    Private Sub CommandButton1_Click()
    Dim i As Integer, j As Date
    'Date should start from October 1 2016
    j = #10/1/2015#
    'I want to addd 15 days
    For i = 1 To 15
    Cells(i, 1).Value = j
    j = j + 1
    Next
    Columns(1).AutoFit
    End Sub

     

     

    Date Format - Single date multple times

     

  • Single date Multiple times

    •  

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To 15
    Cells(i, 1).Value = #1/1/2016#
    Next
    End Sub

     

    Now

     

  • denotes about Current and date & time

    • Private Sub CommandButton1_Click()
    'Place current date & time in range A1
    Range("A1").Value = Now
    'Place current date & time on message box
    MsgBox Now
    End Sub

     

     

    Date

     

  • denotes about Current date

    • Private Sub CommandButton1_Click()
    'Place current date on message box
    MsgBox Date
    'Place current date on Range A1
    Range("A1").Value = Date
    End Sub

     

     

    Year

     

  • It retrieves the YEAR from the given date

    •  

    Private Sub CommandButton1_Click()
    Range("A1").Value = Year("1 - Oct - 2016")
    End Sub

     

     

    Time

     

  • It denotes current time

    •  

    Private Sub CommandButton1_Click()
    MsgBox Time
    Range("A1").Value = Time
    End Sub

     

     

    Day Function

     

  • Retruns the day from the given date
  • Value returns between 1 to 31

    •  

    Code:

    Private Sub CommandButton1_Click()
    Range("A1").Value = Day("1-oct-2016")
    Range("A2").Value = Day("15-oct-2016")
    End Sub

     

     

    Month Function

  • It returns Month value from the given date
  • Range between 1 to 12

    •  

    Code:

    Private Sub CommandButton1_Click()
    MsgBox Month("1-Oct-2016")
    Range("A1").Value = Month("1-Oct-2016")
    End Sub

     

     

    Hour Function

  • It returns Hour

    • Private Sub CommandButton1_Click()
    MsgBox Hour("23:15")
    Range("A1").Value = Hour("23:15")
    End Sub

     

    Second Function

     

  • It returns Second value

    •  

    Private Sub CommandButton1_Click()
    MsgBox Second("17:35:49")
    Range("A1").Value = Second("17:18:49")
    Range("A2").Value = Second("23:35:49")
    End Sub

     

     

    Date Functions

     

    Sub Createdates()
    Range("A1").Value = Now()
    Range("A2").Value = Date
    Range("A3").Value = Year(Date)
    Range("A4").Value = Month(Date)
    Range("A5").Value = Day(Date)
    Range("A6").Value = Time
    Range("A7").Value = Weekday(Date)
    Range("A8").Value = MonthName(Month(Date))
    'MsgBox DateSerial(2016, 1, 0)
    Range("A9").Value = Day(DateSerial(2016, 4, 0))
    End Sub

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli