✨ OfficeTricks

Excel Convert - Number To Date - VBA Date Format

📅 Published: 2014-03-04 📁 Category: Excel, Excel Formula Tricks, Excel VBA Macro

How to Change Excel Date Format?

Excel date format related function are grouped in Menu-> Formula -> Date & Time Option. Using this, we can get today's date & time, calculate time differences, format date & time as per our needs. Lets start to learn some basics with Date and Time formula that are available within Excel under these titles.
  1. Get Excel VBA Today Date Functions.
  2. Excel Convert Number to Date
  3. Excel VBA Date Format conversion
  4. Excel Date Format Formula

1. Excel VBA Date Today

To get Excel today's date use one of the following formula in any worksheet.
  • "=Today()" : will fetch current date from System clock and display in the cell.
  • "=Now()": This command will fetch the date along with time from the system clock.
VBA Format Date Time
  • VBA.Date : To get Excel VBA date today
  • VBA.Now : To get VBA date & current tim
Sample Output from =Now() formula. '3/4/2014 15:02'. With "=Today()" command, only date will be displayed without time. This function is explained here, as we need a sample Today's date in Excel to test the methods explained under this article.

2. Excel Convert Number to Date or Date to String

Choose the cell that has data & use the Excel date format conversion as explained below.
  1. Select Excel cell that has Date.
  2. Right Click & choose "Format Cells" (short cut - 'CTRL + 1').
  3. Choose 'Number' tab.
  4. Click 'Custom' under 'Category' list.
  5. Enter required format under 'Type' or choose a format in any of the defined types.
  6. Click 'Ok' to complete date formatting changes.
The selected cell will reflect the date format changes and display in the chosen format. This is not only for date, any number can be customized to a desired format using this option. Remember that it does not change the way Excel stores the data. This formatting changes only the display format.

3. Excel VBA Date Format

Use VBA date format codes explained in the below sample code inside your Excel macro. In these sample, there are 4 different methods explained and it only converts the display of Excel VBA date format, not the actual data. It can be considered as converting number to date or string to date, only the visible mode. 
Sub Excel_VBA_Date_Format()
    'Display Date Format in Selection  - Jul/21/2016
    Selection.NumberFormat = "mmm/d/yyyy"
    
    'Display Date Format in Cell Reference  - Thursday, July 21, 2016
    ThisWorkbook.Sheets(1).Cells(1, 12).NumberFormat = "[$-409]dddd, mmmm dd, yyyy"
    
    'Display Date Format using WorksheetFunction  - Thursday, July 21, 2016
    ThisWorkbook.Sheets(2).Cells(2, 12) = Application.WorksheetFunction.Text(42572, "dddd mmmm-dd-yyyy")
    
    'Display Date Format using WorksheetFunction inside Cell - Thursday, July 21, 2016
    ThisWorkbook.Sheets(2).Cells(3, 12) = "=TEXT(42573,""mmmm/dd/yyyy"")"
End Sub
Enter different date formats in a new Excel workbook & try the above code to learn how each line make date format conversions.

4. Excel Date Format Formula

Consider the sample date & time value we got from above formulas to explore the available functions. To change this format from numeric date to text with month name like “04-Mar-14”. Then use this Excel Date format formula
  • =TEXT(TODAY(),"dd-mmm-yyyy") or
  • =TEXT(NOW(),"dd-mmm-yyyy")
To know other possible parameters or conversions that can be done using this formula, refer to the Wiki page mentioned at end of this article.

Excel Convert Number To Date - How Date is Stored in Excel?

It is enough to convert any date that is not in proper format to readable format with this "TEXT" function. Before we convert any date, lets understand bit of basic behind how Excel stores the date in Worksheets. To do this, copy the output of Today(), Select another empty cell, right click, Paste Special, Choose Values and click ok. You could see some junk value or some random number. What is it? It is the number of days from 1st January 1900 till date. Excel will convert the dates entered into a number that equals number of days from 1-jan-1900. Excel uses this number in most of its Date calculations. To convert this back to a date, change the format of cells and choose date. To know the date, month or year from this Date Serial number use the formula Date(), Month() or Year(). Similar to the Date, Time is also converted to a decimal.

Additional Reference

http://office.microsoft.com/en-in/excel-help/date-and-time-functions-reference-HP010342402.aspx