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.
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")
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 :
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:
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.
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.
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.
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.
- Select the date format you want to change, or leave the cells where you want to insert dates blank.
- 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.
- In the Format Cells window, switch to the Number tab, and select Date in the Category category.
- 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.
- 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.
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.
- In an Excel spreadsheet, select the cells you want to format.
- Press Ctrl + 1 to open the Format Cells dialog box.
- On the Number tab, choose Custom from the Type list and type the date format you want in the Type box.
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 |
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:
The figure below shows a few examples of the same date formatted with different locale codes in the traditional way for the respective languages:
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.
In this system, 0 is January 1, 1904; 1 is January 2, 1904; and -1 is displayed as a negative date -2-Jan-1904.
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")
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.
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!