Date format with asterisk (*) in Excel

When we try to format an Excel Cell as date or time, you can see format types with an asterisk mark (*) at the left-side. This tutorial lesson explains what are these asterisk marks (*).

Right-click an Excel Cell and select "Format Cells" from the context menu, as shown below.

Click "Date" from the "Number" tab, "Category" list, as shown below.

See the asterisk marks (*) at the left-side for the first two date format entries in the above image. The meaning of these two asterisk marks (*) at the left-side is that the date and time settings for these two formats with asterisk symbols (*) will automatically get updated as the regional settings for the date and time is updated/changed in the Windows Operating System.

Explanation for the asterisk symbol (*) at date format setting is shown as below.

"Date formats display date and time serial numbers as date values. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified for the operating system. Formats without an asterisk are not affected by operating system settings."

To understand this, keep the date format as shown in above image for an Excel Cell (in this case, Cell "A1"), by clicking the "OK" button. Enter a date in that Cell. The format of the entered date will be as shown below

Let us change the short date settings from Region settings inside Windows Control Panel and see how the Region date settings effect Excel Cells formatted with date format with asterisk marks (*) at left-side.

Step 1 - Open "Control Panel" App by searching "control panel" at the Windows search box and then by clicking on "Control Panel" as shown below.

Step 2 - Click "Region" from the Control Panel, as shown below.

You can see that the current Windows Operating System short date format is "dd-MM-yyyy", as shown below.

Step 3 - Change to any other short date format. Example, "yyyy-MM-dd" format by selecting "yyyy-MM-dd" from short date drop-down menu, and then by clicking "Apply" and "OK" as shown below.

You can see that the Excel Cell with "dd-MM-yyyy" format (as shown in Cell A1 screen shot before) has changed automatically to "yyyy-MM-dd" format, when the Operating System date format is changed.