Microsoft Excel provides flexibility to convert the date(s) into different formats. These dates are either automatically convert based on the system settings or based on the formula used in the cell or based on the format of the cell. Through this article we are going through the ways we can convert the date(s) into different formats.
Change date format through Format Cells dialog
This is the more frequently and easy to use option to change the date format of the selected cells in the Excel sheet. Select the Cell(s) whose date format you want to change, then right click on, and then select Format Cells... option. This will open the Format Cells dialog to allow to change the format of the cells.
Select Date category from the Category: list, and then select the date format for the selected cells from the list of formats available in the right side list. These date format types (under Type:) will vary depending on the locale selection from Locale (location): combo box; also depends on the calendar type section.
- Here is the tricky thing you need to remember, when select the date formats begins with an asterisk (*) symbol, these formats automatically changes whenever the Operating System settings for regional date and time settings are changed. That means, the selected Cell formats will vary depends on the Operating System settings.
- When normal date formats (without an asterisk (*) symbol) are selected, the same format will continue irrespective of an Operating System settings.
Another format we can apply to on the Cell(s) is the custom format. Select Custom category from the list of categories, allows to select our own custom date format for the selected Cells.
- Select the list of pre-defined date formats from the list or enter / update the date format from Type: text box. Through custom format we can change the date formats as “yyyy-mm-dd“, “mm-dd-yyyy“, “dd-mm-yyyy“, “mmm-yy” etc,.
Click on OK button on Format Cells dialog to apply the changes to the selected Cells.
Change date formats through formula(s)
Using TEXT function we can convert one date format to another date format.
When date format in the Cell(s) are not in recognized format, none of these conversions will work. Then we need convert the unrecognized date format, to the format what we need by using DATE function.
🙂 Sahida