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.
- Get Excel Today’s Date.
- Menu Option: To Format Excel Date.
- Macro: VBA Date format conversion.
- Excel Formula: To format date display.
1. Formula to Get Excel Today’s Date
Tomorrow Never Comes. So start with 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.
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 date to test the methods explained under this article.
2. Excel Convert Number to Date or Date to String
To do Excel date format process, choose the cell that has data and then follow these steps.
- Select Excel cell that has Date.
- Right Click & choose “Format Cells” (short cut – ‘CTRL + 1’).
- Choose ‘Number’ tab.
- Click ‘Custom’ under ‘Category’ list.
- Enter required format under ‘Type’ or choose a format in any of the defined types.
- 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. VBA Date Format in Excel Worksheet
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 format, not the actual data.
Visual Basic12345678910111213Sub Excel_VBA_Date_Format()'Display Date Format in Selection - Jul/21/2016Selection.NumberFormat = "mmm/d/yyyy"'Display Date Format in Cell Reference - Thursday, July 21, 2016ThisWorkbook.Sheets(1).Cells(1, 12).NumberFormat = "[$-409]dddd, mmmm dd, yyyy"'Display Date Format using WorksheetFunction - Thursday, July 21, 2016ThisWorkbook.Sheets(2).Cells(2, 12) = Application.WorksheetFunction.Text(42572, "dddd mmmm-dd-yyyy")'Display Date Format using WorksheetFunction inside Cell - Thursday, July 21, 2016ThisWorkbook.Sheets(2).Cells(3, 12) = "=TEXT(42573,""mmmm/dd/yyyy"")"End Sub
4. How to Format Date in Excel using Formula?
Consider the sample date & time value we got from above formulas to explore the available functions. To change this Excel date format from numeric date to text with month name like “04-Mar-14”. Then use this formula
- =TEXT(TODAY(),”dd-mmm-yyyy”) or
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.
How Date is Stored in Excel? – Junk Values while copying Date value
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.
Excel Date Format Conversion – Number Format Changes