In this article, we focus on date format in Excel and explain how to set default date and time formats, how to change date format in Excel, how to Create custom date formats and convert your dates elsewhere.

Along with numbers, dates, and times are the most common data types used in Excel. However, they can be quite confusing to work with because the same date can be displayed in Excel in different ways, and secondly, because Excel always internally stores daily in the same format. Regardless of how you have formatted the date in a particular cell.

Knowing Excel date formats in depth can save you a lot of time. And this is the purpose of this comprehensive guide to working with dates in Excel. In the first part, we will focus on the following features:

Set up Excel Date Format

Before you can take advantage of Excel’s powerful features, you must understand how Microsoft Excel stores dates and times, which is the primary source of confusion. While you would expect Excel to remember the day, month, and year for a date, that’s not how it works.

Excel saves dates in order, and it’s just the format of a cell that causes a number to be displayed as a date, time, or date and time.

Dates in Excel

All dates stored as numbers represent the number of days since January 1, 1900, are stored as 1, until December 31, 9999, are stored as 2958465.

In this system:

  • 2 is January 2, 1900
  • 3 is January 3, 1900
  • 42005 is from January 1, 2015 (because it was 42,005 days after January 1, 1900)

Time in Excel

Times are stored in Excel as decimal, between 0.0 and .99999, representing a density for the day, 0.00 is 00:00:00, and 0.99999 is 23:59:59.

For example:

  • 0.25 is 06:00 AM
  • 0.5 is 12:00 PM
  • 0.541655093 is 12:59:59 PM

Date and time in Excel

Excel stores dates and times as decimals that include integers for dates and decimal numbers representing time.

For example:

  • 1.25 is January 1, 1900, 6:00 AM
  • 42005.5 is January 1, 2015, 12:00 PM

How to convert date to number in Excel

If you want to know the serial number representing a specific date or time displayed in a cell, you can do this in two ways.

1. The Format Cells dialog box

Select cells with a date in Excel, press Ctrl + 1 to open the Format cells window, and switch to the General tab.

If you want to know the serial number after the date, without converting the date to number, jot down the number you see under Sample and click Cancel to close the window. If you want to replace the date with the number in the cell, click OK.

Sử dụng hộp thoại Định dạng các ô để nhận được con số đằng sau một ngày nhất định trong Excel.

2. Excel DATEVALUE and TIMEVALUE functions

Use the DATEVALUE () function to convert an Excel date to a serial number =DATEVALUE("1/1/2015").

Use the TIMEVALUE () function to get the decimal number representing the time; for example =TIMEVALUE("6:30 AM").

To see both date and time, concatenate these two functions in the following way:

=DATEVALUE("1/1/2015") & TIMEVALUE("6:00 AM")

Sử dụng các hàm DATEVALUE và TIMEVALUE để tìm số nằm sau một ngày tháng và thời gian nhất định trong Excel.

Note. Since Excel serial numbers started on January 1, 1900, and negative numbers are not recognized, dates before 1900 are not supported in Excel.

If you enter such a date in a worksheet, say 12/31/1899, it will be a text value rather than a date, which means you cannot perform the usual number of days calculation. To be sure, you can type the formula =DATEVALUE("12/31/1899") in some cells, and you will get the expected result – #VALUE! Error.

Default date format in Excel

When you work with dates in Excel, the short and long date formats are retrieved from your Windows Region settings. These default formats are marked with an asterisk (*) in the Format Cell dialog window :

cai dat dinh dang ngay thang nam trong excel

The default date and time formats in the Format Cell-cell change as soon as you change the date and time settings in Control Panel, which leads us to the next section.

How to change default date and time format in Excel

If you want to set a different default date or time format on your computer, for example, change the US date format to British style, go to Control Panel and click Region and Language. If your control panel open in Category mode, then click Clock, Language, and Region> Region and Language> Change the date, time, or number format.

On the Format tab, select the area under Format, then set the date and time format by clicking the arrow next to the format you want to change and selecting a desired item from the drop-down list:

Thiết lập các định dạng ngày tháng và thời gian mặc định trong bảng điều khiển

  If you are unsure of other codes (such as mm, ddd, yyy), click the ” What does the notation mean ” link in the Date and time formats section or check out custom Excel date formats in the tutorial.

If you are not satisfied with any of the available data and time formats on the Formats tab, click the Additional settings button on the region’s lower right-hand side and the Language dialog window. This will open the Customize dialog box, where you switch to the Date tab and enter a custom short and/or long date format in the corresponding box.

Thiết lập định dạng ngày tháng và thời gian tùy chỉnh

How to quickly apply default date and time formatting in Excel

Microsoft Excel has two default formats for dates and times – short and long, as explained in the default Excel date format.

To quickly change date format in Excel to default format, do the following:

  • Select the date you want to format.
  • On the Home tab, in the Number group, click the little arrow next to the Number Format box and choose the format you wish to – short date, long date, or time.

Áp dụng nhanh định dạng ngày tháng và thời gian mặc định trong Excel

If you want more date format options, choose More Forms Number from the drop-down list or click Dialog Box Launcher next to Number. This will open the Format Cells dialog, and you can change the date format there.

Nhấp vào Hộp thoại hộp thoại Hộp thoại để mở hộp thoại Định dạng ô.

Suppose you want to quickly format Excel date to dd-mmm-yy, press Ctrl + Shift + #. Just note that this shortcut always applies the dd-mmm-yy format, like 01-Jan-15, regardless of your Windows Region settings.

How to change date format in Excel

In Microsoft Excel, dates can be displayed in many different ways. When it comes to changing the date format of a cell or a range of cells, the easiest way is to open the Format Cells dialog and choose one of the predefined layouts.

  1. Select the date format you want to change, or leave the cells where you want to insert dates blank.
  2. Press Ctrl + 1 to open the Format Cells dialog box. Alternatively, you can right-click on the selected cell and choose Format Cells from the context menu.
  3. In the Format Cells window, switch to the Number tab, and select Date in the Category category.
  4. Under Type, choose the desired date format. Once you do this, the Sample box will display the format preview with the first date in the data you selected.

    Thay đổi định dạng ngày trong Excel

  5. If you are happy with the preview, click the OK button to save the format changes and close the window.

If date formats don’t change in your Excel spreadsheet, chances are your dates are formatted as text, and you have to convert them to date format first.

How to convert date format to another language

Once you’ve got a file full of foreign dates and chances, you want to change them to the date format used in your part of the world. Let’s say you want to convert the US date format (month/day/year) to a European style format (date/month/year).

The easiest way to change date format in Excel based on how another language displays dates is as follows:

  • Select the date column that you want to switch to another language.
  • Press Ctrl + 1 to open Format Cells
  • Select the language you wish to in Locale (location) and click OK to save the changes.

Chuyển ngày Excel sang một ngôn ngữ khác

If you want the date to be displayed in another language, you will have to create a custom date format with a geographic area code.

Create a custom date format in Excel

If there is no predefined Excel date format that is right for you, you are free to create one.

  1. In an Excel spreadsheet, select the cells you want to format.
  2. Press Ctrl + 1 to open the Format Cells dialog box.
  3. On the Number tab, choose Custom from the Type list and type the date format you want in the Type box.

    Tạo một định dạng ngày tùy chỉnh trong Excel

When setting up a custom date format in Excel, you can use the following codes.

Code Description Example (January 1, 2005)
m Number of months without a leading zero first
mm Number of months with a leading zero 01
mmm Month name, short-form Jan
mmmm Month name, full sample January
mmmmm The month is like the first letter J (stands for January, June, and July)
d Number of days without a leading zero first
d Number of days with a leading zero 01
ddd Day of the week, short-form Monday
dddd Day of the week, full form Monday
yy Year (last two digits) 05
yyyy Year (four digits) In 2005

When setting up a custom time format in Excel, you can use the following codes.

Code Description Show as
H Hours without a leading zero 0-23
hh Hours with a leading zero 00-23
m Minutes do not have a leading zero 0-59
mm Minutes with leading zeros 00-59
S The second has no leading 0 0-59
ss Seconds with leading zeros 00-59
AM / PM Periods of the day
(if omitted, the 24-hour time format is used)
AM or PM
Note. If you are setting up a custom format that includes the date and time values and you use ” m ” right after ” hh ” or ” h ” or just before “ss” or “s”, Microsoft Excel will display displays minutes instead of months.

When creating custom date formats in Excel, you can use a comma (,) dash (-), forward-slash (/), colon (:), and other characters.

For example, the same date and time, January 13, 2015, 13:03, can be displayed in a variety of ways:

Format Show as
dd-mmm-yy 13-Jan-15
mm / dd / yyyy 01/13/2015
m / rn / yy 1/13/15
dddd, m / d / yy h: mm AM / PM Tuesday, 1/13/15 1:03 PM
ddd, mmmm dd, yyyy hh: mm: ss Tue, January 13, 2015, 13:03:00

How to Create a Custom Excel Date Format for Another Language |

If you want to display dates in another language, you must create a custom format and pre-set a date with the corresponding geographic area code. The locale code should be included in [square brackets] and preceded with a dollar sign ($) and dash (-). Here are a few examples:

  • [$ -409] – English, Untitled countries
  • [$ -1009] – English, Canada
  • [$ -407] – German, German
  • [$ -807] – Germany, Switzerland
  • [$ -804] – Bengali, India
  • [$ -804] – Chinese, Chinese
  • [$ -404] – China, Taiwan

You can find a complete list of local codes on this blog.

For example, here’s how you set up a custom Excel date format for a Chinese locale in the year-month-day (day of the week) time format:

Tạo một định dạng ngày Excel tùy chỉnh cho các ngôn ngữ Trung Quốc

The figure below shows a few examples of the same date formatted with different locale codes in the traditional way for the respective languages:

Định dạng ngày tùy chỉnh cho các ngôn ngữ khác nhau

Excel date formats not working – fixes and solutions

Usually, Microsoft Excel understands days well, and you are unlikely to hit any barriers when working with them. If you have Excel date formatting problems, please check out the following troubleshooting tips.

A cell is not large enough to fit a day

If you see some pound signs (#####) instead of dates in your Excel spreadsheet, chances are your cells aren’t wide enough to fit the entire date.

Solution. Double-click the right boundary of the column to resize it to match the date automatically. Alternatively, you can drag the right bracket to set the column width you want.

Negative numbers are formatted as dates

In all modern versions of Excel 2013, 2010, and 2007, hash marks (#####) are also displayed when a cell formatted as a date or time contains a negative value. Usually, this is the result returned by some formula, but it can also happen when you enter a negative value in a cell and then Format Cells that as the date.

If you want to display negative numbers as negative dates, you can choose from two options:

Solution 1. Switch to the 1904 date system.

Go to File> Options> Advanced, scroll down to the When calculating this workbook section, select the Use 1904 date system check box, and click OK.

Chuyển sang hệ thống ngày 1904

In this system, 0 is January 1, 1904; 1 is January 2, 1904; and -1 is displayed as a negative date -2-Jan-1904.

Số âm được hiển thị dưới dạng ngày phủ định

Of course, such an expression is very unusual and takes time to get used to, but this is the right way to go if you want to do calculations in the early days.

Solution 2. Use the Excel TEXT function.

Another possible way to display negative numbers as negative dates in Excel is by using the TEXT function. For example, if you are subtracting C1 from B1 and a value in C1 is more significant than in B1, you can use the following formula to output the result in date format:

=TEXT(ABS(B1-C1),"-d-mmm-yyyy")

Sử dụng hàm Excel TEXT để hiển thị số âm như ngày.

You may want to change the cell association to the right exactly, and naturally, you can use any other custom date format in a TEXT formula.

Note. Unlike the previous solution, the TEXT function returns a text value, which is why you won’t be able to use the result in other calculations.

Dates are imported into Excel as text values

When you import data into Excel from a .csv file or some other external database, dates are usually imported as text values. They may look like regular dates to you, but Excel perceives them as text and treats them accordingly.

You can convert “text dates” to date format using the Excel DATEVALUE function or Text to Columns.

Here’s how you format dates in Excel. In the next part of our tutorial, we will discuss how you can insert dates and times into your Excel spreadsheets. Thank you for reading and seeing you next week!