Date Format in Excel

What is Date Format in Excel?

In Excel, a date is displayed according to the format selected by the user. One can either select from the different formats available or create a customized format according to the requirement. The default date format is specified in the Control Panel of the system. However, it is possible to change these default settings. For example, the date 01/01/2021 corresponds to the format dd/mm/yyyy. If the format is changed to d-mmm-yyyy, the date becomes 1-Jan-2021.

The date format in Excel can be changed either from the “number format” of the Home tab or the “format cells” option of the context menu.

In Excel for Windows, 1900 is the default date system. In Excel for Mac, 1904 is the default date system. Both these systems store the dates as consecutive numbers having a difference of 1. These numbers are known as serial values or serial numbers. The reason dates are stored as serial numbers is to facilitate calculations.

In the 1900 date system, the first date that Excel recognizes is January 1, 1900. This date is stored as the number 1 in Excel. Consequently, the number 2 represents January 2, 1900. The last date recognized by Excel is December 31, 9999. This is represented by the serial number 2958465. A date prior to the year 1900 or after the year 9999 is identified as a text value by Excel.

Dates are stored only as positive integers in the 1900 date system. However, to display negative numbers as negative dates, one needs to switch to the 1904 date system.

In the 1904 date system, 0 represents January 1, 1904 and -1 represents January -2, 1904. The number 1 represents January 2, 1904. The last date recognized by Excel (in the 1904 date system) is December 31, 9999, which is represented by the serial number 2957003.

In this article, we follow the 1900 date system.

Code of Date Format in Excel

A code (like dd-mm-yyyy) is a representation of a day (d), month (m), and year (y). The appearance of a date can be changed by changing the specified code.

The different codes, their explanation, and output (for days, months, and years) have been presented in the following images.

Notations for a Day

Notations for a Month

Notations for a Year

How to Change Date Format in Excel?

Here we look at some of the examples of Date Format in Excel and how to change them.

Example #1–Apply Default Format of Long Date in Excel

The following image shows a number in cell A1. We want to know the date represented by this number. The output should be in the long date format of Excel.

The steps to know the date represented by the number in cell A1 are listed as follows:

Select cell A1. From the Home tab, click the “number format” drop-down appearing in the “number” section.
Select “long date,” as shown in the following image.
The output is shown in the following image. The long date format displayed is dd mmmm yyyy.
Hence, the number 1 represents the date 01 January 1900 in the long date format.
Note: The short and long dates appear the way they have been set in the Control Panel. To change these default date formats, click “clock, language, and region” in the Control Panel. Thereafter, click “change date, time, or number formats.” Make the desired changes and click “Ok.”

Likewise, had there been 2 in cell A1, the long date format would have been 02 January 1900. The number 3 would have been displayed as 03 January 1900 in the long date format.

Note: To switch to the 1904 date system, select “advanced” from the “options” of the File tab. Under “when calculating this workbook,” select “use 1904 date system” and click “Ok.”

You can download this Change Date Format Excel Template here – Change Date Format Excel Template

Example #2–Change the Date Excel Format Using “Custom” Option

The following image shows some dates in the range A1:A6. These dates are in the format dd-mm-yyyy. We want to change their format to dd-mmmm-yyyy.

For instance, the date in cell A1 should appear as 25-February-2018. Use the “custom” option of the “format cells” dialog box.

The steps to change the date format in Excel are listed as follows:

Step 1: Select all the dates of the range A1:A6. The same is shown in the following image.

Step 2: Right-click the selection and choose “format cells” from the context menu. Alternatively, press the keys “Ctrl+1” together.

Step 3: The “format cells” window opens, as shown in the following image.

Note: The default short date and long date formats are marked with an asterisk (*) in the box under “type.” The short date is 3/14/2012 (m/dd/yyyy) and the long date is Wednesday, March 14, 2012 (dddd, mmmm dd, yyyy).

Step 4: From the “number” tab, select “custom” under “category.” The categories are shown at the left side of the “format cells” window.

Step 5: Under “type,” enter the required date format. Either type the format (dd-mmmm-yyyy) or select it from the various options displayed in the box below “type.”

Once the format has been entered, check the preview of the first date (of the range A1:A6) under “sample.” The same is shown in the following image. If the preview of the date looks fine, click “Ok” in the “format cells” window.

Note 1: The date under “sample” is displayed according to the format specified under “type.”

Note 2: While creating custom date formats, one can use a forward slash (/), hyphen (-), comma (,), space ( ), etc.

Step 6: The output is shown in the following image. All dates of the range A1:A6 have been converted to the format dd-mmmm-yyyy. However, the default date format can still be seen in the excel formula bar. This default format corresponds with the short date set in the Control Panel.

Example #3–Apply Different Types of Customized Date Formats in Excel

The succeeding image shows certain dates in the range A1:A6. At present, the date format is dd-mm-yyyy.

We want to apply four different formats to these dates. For applying each format, the common steps to be performed are given as follows:

Select the range A1:A6.
Right-click the selection and choose “format cells.”
From the “number” tab, select “custom” under “category.”

Further, under each format, the additional steps to be performed followed by two images are given.

Format 1: dd-mmm-yyyy

In the “custom” option of the “number” tab, select the format “dd-mmm-yyyy” under “type.”
Click “Ok.”

The output is given in the following image. All dates are displayed according to the format dd-mmm-yyyy. In this format, the hyphen is the separator between the day, month, and year.

Format 2: dd mmm yyyy

Select the format “dd mmm yyyy” under “type” of the “custom” option.
Click “Ok.”

The output is given in the following image. All dates are converted to the format dd mmm yyyy. In this format, the space is the only separator between the day, month, and year.

Format 3: ddd mmm yyyy

In the “custom” option, select the format “ddd mmm yyyy” under “type.”
Click “Ok.”

The output is given in the following image. The dates are shown in the format ddd mmm yyyy. In this format, the day and the month are displayed in their short notations.

Format 4: dddd mmmm yyyy

From the “custom” option of the “number” tab, select “dddd mmmm yyyy” under “type.”
Click “Ok.”

The output is given in the following image. All dates have been converted to the format dddd mmmm yyyy. In this format, the date, month, and year are displayed in their respective full forms.

It must be observed that the date format changes as per the style set by the user. The user can select a date format according to his/her convenience.

Example #4–Convert Text Values Representing Dates to Actual Dates

The following image shows a list of dates in the range A1:A6. At present, these dates are appearing as text values. We want to convert these text values to dates having the format dd-mmm-yyyy.

The steps, to convert text values to dates having the given format, are listed as follows:

Step 1: Enter the following formula in cell B1.

“=VALUE(A1)”

Press the “Enter” key.

Note 1: The VALUE functionVALUE Excel Function In Excel, the value function returns the value of a text representing a number. So, if we have a text with the value $5, we can use the value formula to get 5 as a result, so this function gives us the numerical value represented by a text.read more returns the numeric form of a text string that represents a number. In other words, it converts a number looking like text to an actual number.

Note 2: Instead of the VALUE function, one can also use the DATEVALUE functionDATEVALUE Excel The DATEVALUE function displays any date in absolute format in Excel. It takes an argument in date text and converts it to a date format that can be recognized as a date. The syntax to use this function is = DATEVALUE(date text).read more of Excel. The latter converts a date stored as text to a serial number. This serial number is recognized as a date by Excel.

Step 2: Select cell B1 and drag the fill handle till cell B6. The output is shown in the following image. All text values (of the range A1:A6) have been converted to numbers (in the range B1:B6).

Ideally, the text string in Excel is left-aligned while the number string is right-aligned. However, we have aligned both the ranges (A1:A6 and B1:B6) centrally.

Note: When text strings representing dates have been converted to serial values (or dates), they can be used for performing different calculations like addition, subtraction, and so on.

Step 3: To view the obtained serial numbers (in column B) as dates, apply the required format. For this, select the range B1:B6, right-click and choose “format cells.”

In the “number” tab, select the option “custom.” Under “type,” enter or select the format “dd-mmm-yyyy.” The same is shown in the following image.

If the sample date looks alright, click “Ok.”

Step 4: The output is shown in the following image. Hence, all text values (of column A) have been converted to proper dates (in column B) having the format dd-mmm-yyyy.

Note: To ensure that a value is recognized as a date by Excel, check for the following signs:

The dates are right-aligned as they are numerical values.
If two or more dates are selected, the status bar (at the bottom of the worksheet) shows the count, average, numerical count, and sum. One or more of these options may be displayed according to the version of Excel being used.

If a value is a text string, it would be left-aligned and the status bar will show only the count.

Often, the excel date format needs to be changed (from text to dates) when data is downloaded (or copied and pasted) from the web. This is because, in such instances, the dates may not be displayed as numbers.

Example #5–Change the Date Format Using “Find and Replace” Box

The following image shows some text values representing dates in the range A1:A6. The days, months, and numbers have been separated with a backslash. We want to perform the following tasks:

Replace all the backslashes () with forward slashes (/) by using the “find and replace” dialog box.
Convert text values representing dates to actual dates.

The steps to perform the given tasks are listed as follows:

Step 1: Press the keys “Ctrl+H” together. The “find and replace” dialog box opens, as shown in the following image.

Step 2: In the “find what” box, type a backslash (). In the “replace with” box, type a forward slash (/).

Step 3: Click “replace all.” Excel shows a message stating the number of replacements it has made. Click “Ok” to proceed. The final output is shown in the following image.

Hence, all backslashes have been replaced with forward slashes. With this replacement, the text values representing dates have automatically been converted to actual dates by Excel.

Since column A was aligned centrally from the beginning, this alignment is retained even after the values are converted to dates.

Frequently Asked Questions

1. How can the date format in Excel be changed?

The steps to change the date format in Excel are listed as follows:

a. Select the cell containing the date. If the date format of a range needs to be changed, select the entire range.
b. Right-click the selection and choose “format cells” from the context menu. Alternatively, press the keys “Ctrl+1” together.
c. In the “number” tab, select the option “date.” Select the required date format under “type.”
d. Check the preview (of the first date of the selected range) under sample. If the preview is fine, click “Ok.”

The date format of the selected cell or cells (selected in step a) is changed.

Note 1: The required date format may or may not be available under “type” of the “date” option. If it is not available, select “custom” as the “category” from the “number” tab. Type the required date format under “type” and click “Ok.”

Note 2: If the selected cell (selected in step a) contains a text string representing a date, convert this string to a date first. Then change the format to the desired date format.

2. How to change the date format permanently in Excel?

To change a date format permanently, one needs to make changes to the date formats of the Control Panel. This is because the short and long date formats of Excel reflect the date settings of the Control Panel.

The steps to change the date settings of the Control Panel are listed as follows:

a. Open the Control Panel from the Start menu.
b. In the “clock, language, and region” category, click “change date, time, or number format.” This is available under the “region and language” option.
c. The “region and language” or “region” dialog box opens. Under “format,” select the region.
d. Enter the required short and long date formats under “date and time formats.” To enter customized short and long date formats, click “additional settings.” The “customize format” dialog box opens. Make the changes in the “date” tab and click “Ok.”
e. Check the preview under “examples” at the bottom of the “region and language” box. If the preview is alright, click “Ok.”

The default date settings have been changed. Now enter a date in any format in Excel. Then just select the short or the long date format from the “number format” (in the “number” section) of the Home tab.

The dates will appear in the format set in the Control Panel. So, the user need not change the format of each date manually.

3. How to change a date to a text string in Excel?

Let us change the date 22/1/2019 in cell A1 to a text string in Excel. The text string should be in the format yyyy-mm-dd.

The steps to change a date to a text string are listed as follows:
a. Enter the formula =TEXT(A1,“yyyy-mm-dd”) in cell B1.
b. Press the “Enter” key.

The date in cell A1 (22/1/2019) is converted to 2019-01-22 in cell B1. It must be noted that the date in cell A1 is right-aligned, being a number. In contrast, the text in cell B1 is left-aligned.

Note: The TEXT function helps convert numbers to text strings. It is used to display values in a specific format. The syntax is TEXT(value,format_text). “Value” is the number to be converted to text. “Format_text” is the format in which the number should be displayed.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Date Format in Excel