How to change the date format in Microsoft Excel?

Read Time:1 Minute, 39 Second

There are many ways on how to change the date format in your Microsoft Excel sheets.

Basically, by default Excel follows the date formats according to your date settings in the Control Panel.

If you change the date and format settings, the default date format in your Excel sheets will follow accordingly. 

How to change the date format in your Excel sheets?

Option 1.

  1. Select the cell or a series of rows/columns with a date.
  2. Right-click and select Format Cells.
  3. In the pop-dashboard, select the Number Tab. 
  4. Select Date format from the drop-down menu, or.
  5. Select Custom settings and choose your desired format (e.g. mm/dd/yyyy).
  6. Click OK.

Option 2.

  1. You can use the DATEVALUE() function to convert a specific date to a serial number. For example =DATEVALUE(“2/2/2020”).

Option 3.

  1. Set a different default date and time on your computer. 
  2. Open Control Panel, and select Region and Language.
  3. In the pop-up panel, click Clock, Language, and Region.
  4. Next, change the date and time format. 
  5. You will be able to customize based on your preferences.

Option 4.

  1. You may quickly change the date format in the default formatting area. 
  2. Select the cell you wanted to change the format, and go to Home tab.
  3. Select the Number group, and click the drop-down menu next to the Number Format box.
  4. Click the desired format for a short date and long date or time.

Custom date format in Excel.

CodeDescriptionExample (January 1, 2020)
mMonth number without a leading zero1
mmMonth number with a leading zero01
mmmMonth name, short formJan
mmmmMonth name, full formJanuary
mmmmmMonth as the first letterJ (stands for January, June and July)
dDay number without a leading zero1
ddDay number with a leading zero01
dddDay of the week, short formMon
ddddDay of the week, full formMonday
yyYear (last 2 digits)05
yyyyYear (4 digits)2020

This is a simple guide for those who wanted to change the date format in Microsoft Excel sheets.

0 %
0 %
0 %
0 %
0 %
0 %

Leave a Reply

Your email address will not be published.

Share via
Copy link
Powered by Social Snap