Search

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.

format-excel-cell.jpg

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

date-format-excel-cell-asterisk.jpg

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."

date-format-begin-with-asterisk.jpg

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

date-format-excel-cell-asterisk-short-date.jpg

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.

search-for-control-panel.jpg

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

click-region-settings-control-panel.jpg

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

region-settings-date-and-time.jpg

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.

change-region-settings-short-date.jpg

change-region-settings-short-date-apply-ok.jpg

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.

date-format-changed-to-yyyy-mm-dd.jpg

Related Tutorials
Cell reference (Cell address)
Range reference
Data types in Excel
Difference between Text and Number in Excel
1900 and 1904 date systems in Excel
How to change Excel date system
How to add or subtract dates in Excel
Negative date in Excel
How does Excel stores time values
How to add or subtract time in Excel
How Excel stores date and time values together
Negative time in Excel
Difference between Excel date systems
Copying dates between 1900 and 1904 date system workbooks
Date format with asterisk (*) in Excel
How to insert current date in Excel
How to insert current time in Excel
How to insert current date and time in Excel