QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Change the Date Format in Excel?

By Neha T Leave a Comment

Excel provides various formats to display a date. It even allows you to change date format in a way you are comfortable with. Excel has defined the code for displaying the days, months and years using a combination of which you can format or customize your dates as you wish.

In this section we will see how the dates are stored in excel, how can you change its format? How you can customize the date format if do not want to go with the available date formats? What is default date format and how you can change it?

The topics we will discuss are as follow:

  1. Dates in Excel
  2. Convert Date to Number
  3. Change the Date Format in Excel
  4. Customize the Date Format in Excel
  5. Change Default Date Format

Dates in Excel

Whenever we write a date in an Excel sheet, it stores it in the form of a number. Let us take an example of a date 1/01/2019. To check how it is stored in the number form in Excel. Type the example date in a cell in an excel sheet and press Control+1 or you can right-click on the cell and select Format Cells…. Both will lead you to a Format Cells dialog box.

Now here in the Number tab, select General under the Category at the right side in Sample box you can see the 43466.

Date in Number form

This date is numbered 43466 because the Excel has numbered the date 01-01-1900 as 1. So, from 01-01-1900 1/01/2019 is the 43466th day.

Formula to obtain today’s date/current date from the system:

=TODAY()            (press Enter)

Today formula

Code to display a date in various format:

Code for date format

Steps to Change Date Format in Excel 2016:

Convert Date to Number

Well we can convert the date into a number using the Format Cells dialog box or we can normally go to the Numbers group of Home tab and in the Format dropdown box select General for the selected cell where we have written the date.

Convert date to number 1

Or

We can use the formula to convert the date into the number. For that select a cell and type the formula:

=DATEVALUE(“01-01-2019”)

Convert date to number formula

Change the Date Format in Excel

To change the date format in Excel, select the cell which has the date, & of which you want to change the format. Now, pull up the Format Cells dialog box either by pressing the Ctrl+1 or by right click on the cell and select Format Cells.

Under Number tab in Category select Date, at the right side, you have the Type box under which you can select the date format among the various options.

You can also set the language in which you want to display the dates in Locale.

change Date format

Note: The date formats that start with an asterisk sign (*) are the default date format of the Excel. Whenever you write a date in a cell, it gets formatted in the default format of the excel.

Customize the Date Format in Excel

Now, here you can construct your own date format if you are not comfortable with the date formats available. For this pull out your Format Cells dialog box. Under Number tab in Category, select Custom.

At the right side in the Type box type your customized date format. For example, ddd, mmm/dd/yy this will display the date in the format, Tue, Jan/01/19.

Customize date format

Change Default Date Format

To change the default date format, we have to make changes to the operating system date format which we can do via Control Panel.

Open the Control Panel and click on Clock, Language and Region>Region.

Change default Date Format

A Region dialog box will open and in the Formats tab, you can change the language in Format drop-down box. In the Date and time formats, you can modify the current Date and time formats. If required you can also go to Additional settings.

Change default Date Format 1

The changes you implement here will be visible in the Excel default date format. So this is all about how you can change the format of the dates in Excel.

You Might Also Like:

How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? Lock Formulas or a Cell in Excel - feature ImageHow to Lock Formulas or Cells in Excel? Subtract feature image 1How to Subtract in Excel? how-to-split-text-of-cells-in-ms-excel-2016-featured-imageHow to Split Text in a Cell in MS Excel 2016? Calculate percentage Featured ImageHow to Calculate Percentage in MS Excel 2016? Auto Fill in MS Excel 2016How to use Autofill in MS Excel 2016? How to change the default file format in Microsoft WordHow to change the default file format in Microsoft Word How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Monitor your Network Usage in Windows 10
  • How to send a video through gmail
  • How to Close Apps on iPhone?
  • How to Hide the Unity Launcher In Ubuntu 14.04
  • How to Enter BIOS in Windows 10?

Copyright © 2023 · QueHow · Contact Us