VBA date format

In VBA, you can use Format(expression[,format]) function to format a date value.
-expression argument represents the date expression to be formatted.
-format argument specifies how the date is formatted. The predefined format values that can be used are General Date, Long Date, Long Time, Medium Date, Medium Time, Shot Date, and Short Time.
Example:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
     Dim curDate, curTime
     curDate = Format(Now, "Long Date") ' long date display
     curTime = Format(Now, "Long Time") 'long time display
     Cells(1, 1) = "Current Date:" & curDate
     Cells(2, 1) = "Current Time:" & curTime
    
    
End Sub

You also can define your own date and time formats by using special characters as shown below.
-(:) is time separator.
-(/) is date separator.
-(-) is date separator.
-d displays the day as a number without 0 in front.
-dd displays two digits that represent the day number.
-ddd displays the day as an abbreviation. e.g Mon, Tues, Wed,...
-dddd display the full word of the day. e.g Monday, Tuesday, Wednesday,...
-m displays the month as a number without 0 in front.
-mm d displays two digits that represent the month number.
-mmm displays the month as an abbreviation. e.g Jan, Feb, Mar,...
-mmmm display the full word of the month. e.g January, February, March,...
-yy displays the year as two-digit.
-yyyy displays the year as four-digit.
-H displays the hour as a number without 0 in front.
-Hh displays the hour as two-digit.
-N displays the minute as number without 0 in front.
-Nn displays the minute as two-digit.
-S displays the second as a number without 0 in front.
-Sn displays the second as two-digit.

Example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
     Dim curDate, curTime
     curDate = Format(Now, "dd-mm-yy") 'short date
     curTime = Format(Now, "Hh:Nn:Ss AM/PM") 'short time
     Cells(1, 1) = "Current Date:" & curDate
     Cells(2, 1) = "Current Time:" & curTime
    
    
End Sub

Posted by: Lavy | post date: 02-20-2013 | Subject: VBA for MS Excel




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.