EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Excel Date Format

By Madhuri ThakurMadhuri Thakur

Home » Excel » Blog » Date and Time Function in Excel » Excel Date Format

date format in excel

Excel Date Format (Table of Contents)

  • Date Format in Excel
  • How Excel stores Dates?
  • How to Change Date Format in Excel?

Date Format in Excel

A date is one of the data types that are available in excel, which we use mostly in our day to day excel data works. A date can be displayed in several ways in excel as per requirement. A date has multiple numbers of formats based on geographical regions. Because different geographical regions use a date in different ways, Excel comes with multiple numbers of formats to display dates.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How Excel Stores Dates?

Before getting into the data formats, try to understand how Excel stores dates. Excel stores the date in an integer format. To make you understand better, we will look at the following example. Consider today’s date, 11 Feb 2019.

format date example 1-1

If we observe the above date, it is in the format of Month-Day-Year. Select this date and convert this to general or number format, then we will find a number. Let’s see how to convert. Select the date and choose the drop-down list from the Number segment under the Home tab. From the drop-down, select the option General and observe how the date will be converted.

format date example 1-2

Once you convert, it will change as an integer value, as shown in the below picture.

format date example 1-3

Now we will understand what that number is and what calculation is used by Excel to convert the data into an integer. Excel gives the number series for the dates starting from 1 Jan 1900 to 31 Dec 9999, which means 1 Jan 1900 will store as 1, and 2 Jan 1900 will store as 2. Now just try to check for the date 2 Jan 1900.

format date example 1-4

When we select the general option, it converted to 2, as shown below.

Popular Course in this category
Sale
Excel Advanced Training (16 Courses, 23+ Projects)16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (11,434 ratings)
Course Price

View Course

Related Courses
Excel for HR Training (8 Courses, 10+ Projects)

format date example 1-5

I hope you understand how Excel stores the date.

How to Change Date Format in Excel?

Let’s understand how to change the date format in Excel by using some examples.

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

Example #1

First, we will see a short date and a Long date. Then, we will find the formats Short date and Long date from the same drop list of numbers.

Short date: As the name itself speaks, how it looks like. It will display a date in a simple way that is 2/11/2019. We can observe in the drop-down itself how it will display.

format date example 2-1

Long date: It will display a date in the long format. We can observe in the below image how it will display.

format date example 2-2

Example #2

A system has one default format to display the date whenever the user inputs the date. We will see how to check the default format in excel. Then, select the date and right-click.

format date example 2-3

The above pop up will appear; from that pop-up menu, select the Format Cells. Then, another screen will appear, which is the “Format cells” screen in which we can apply different kinds of formats like Number, Alignment, Font, Border, fill and protection.

Select Number and select the Date from the Category box. When we select the “Date”, the right side box will show the different formats available for the different locations.

If we observe, the first two date formats, which are highlighted in the red box, have a * (asterisk) mark, which shows that those are default date formats.

format date example 2-4

If we want to change the default date settings, we should go to the control panel and select Region and Language, then select Formats and change the date format as per your requirement.

format date example 2-5

Example #3

If we observe the below screenshot and the dates with ‘ * ‘, there are also different formats. We can select the required date format to change the current date format.

When we select the required format, we can observe a preview of how it will display in excel under the Sample box. There are different formats available like M/D, M/D/Y, MM/DD/YY etc.

Select the M-D format, as shown below.

format date example 3-1

Then the date will look like 2-11 if we observe in the formula bar, which is highlighted. The formula bar shows as 2-11-2019; however, in Excel, it is displaying as 2-11.

format date example 3-2

We can also change the location by selecting the required location from the selected dropdown. Observe the below image how the drop-down looks like.

format date example 3-3

When we select a particular location from the drop-down, then different data formats will appear in the box “Type”. Currently, it is English (United States), now select the English (United Kingdom). Click OK. Then the date formats in the Type will change. Observe the below screenshot.

format date example 3-4

Observe the formats in the location United Kingdom are different from the United States. Still, if you are not happy with the date formats, we can create our customized format.

Example #4

To create a customized format, select the Custom option from the Category box.

Once we select the Custom category, we can input the required format in Type. To make it more clear, suppose we want the format like DD:MMMM: YYYY, then type this format in the “Type” box.

Example 4-1

Observe the above image; whatever input is in Type, the same format is showing in Sample. If we click OK, then it will apply to the date in excel.

Example 4-2

Example #5

Date format in Other languages

We can display dates in other languages too. We will be able to do this using the “custom” format under the category. For doing this, we should know that particular language code; once we know that code, we just need to add the country code before our date format.

The language code should be in square brackets preceded with $ followed by a “- “in the format of [$-xxx].

Example 5-1

In the below example, we created the date format of the German language similarly; we can give date formats for Chinese, Spanish, Japanese, French, Italian, Greek, etc. Use Google for language codes as per your requirement.

Example 5-2

Things to Remember

  • Dates before 1 Jan 1900 cannot convert to text in excel as it will not read negative numbers; hence it remains in the same format.
  • Dates can display in short and long formats from the drop-down number under the “Home” tab.
  • To convert the data into number format with the formula “Date value”.
  • Default date represents with “*” symbol, to change the default selection, need to go to “Control panel”.
  • CTRL + 1 is the shortcut for the “Format cell”. CTRL + ; is used to display the current date.

Recommended Articles

This has been a guide to Date Format in Excel. Here we discussed How to change the Date Format in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel DATEDIF Function
  2. VBA Date Format
  3. Excel Date Function
  4. Date Formula in Excel

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

2 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Date and Time Function in Excel
    • Excel DATEVALUE Function
    • Compare Dates in Excel
    • EOMONTH Formula
    • Date Formula in Excel
    • Excel Subtract Time
    • Excel Insert Date
    • Excel Date Format
    • Excel Week Numbers
    • Excel Formula for Weekday
    • Add Months to Dates in Excel
    • Excel DATEDIF Function
    • EOMONTH Function Excel
    • WEEKDAY Function Excel
    • NETWORKDAYS Excel Function
    • Excel Month Function
    • EDATE Excel Function
    • WORKDAY Excel Function
    • DAY Excel Function
    • YEAR in Excel
    • NOW Excel Function
    • Excel Date Function
    • TODAY Excel Function
    • Time Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online Training
  • Online Excel for HR Certification
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Date Format in Excel Template

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

डाउनलोड Date Format in Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Independence Day Offer - EXCEL ADVANCED Online Training Learn More