Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Entering Dates in Excel.

Entering Dates in Excel

by Allen Wyatt
(last updated February 9, 2016)

11

Dates are a special case in Excel. If you enter information that can be translated as a date (by any stretch of the imagination), then Excel treats it as a date. It converts your data into a serial number that is internally used to represent dates and times. For instance, any of the following entries will be translated to a date by Excel:

  • 12/15
  • 12-15/12
  • 15 Dec
  • December 15, 2012

If you enter the first example, Excel will convert it to a date and display that date as best it can determine. If you leave off the year in your entry, Excel assumes you mean the current year. You could also use dashes instead of slashes and Excel will still figure out you are entering a date.

Regardless of how you type a date, it is converted to a special serial number by Excel and stored internally in that format. How you see the date on your screen is a consequence of how the cell is formatted. Even though Excel stores dates in a standard format internally, they can be displayed using any number of different formats.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12386) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Entering Dates in Excel.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Find and Replace in a Column or Row

Need to search for information in a table? Word allows you to easily limit your search to an entire column or row, as ...

Discover More

Running a Macro when a Worksheet is Activated

Want to run a macro when you first select a worksheet? You can do so by using one of the event handlers built into Excel, as ...

Discover More

Using Go To to Jump to a Chart Sheet

Create a chart on its own worksheet, and you can display it by simply clicking the tab at the bottom of the Excel work area. ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

MORE EXCELTIPS (RIBBON)

Forcing Editing to Be Done in a Cell

Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only be ...

Discover More

Removing Spaces

Need to get rid of spaces in a range of cells? There are two ways you can approach the task, as described here.

Discover More

Relative Worksheet References when Copying

Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 9?

2016-02-10 09:11:02

Shandor

Sorry Ihsan, it looks like many of us hijacked your question! But now you'll also have tactics to deal with unexpected date conversion in Excel.


2016-02-09 17:05:35

Lynn Martin

This is for Mick Hind:
Rename that .CSV file to a .TXT file. Then get into Excel & open the .TXT file. The Text Import Wizard will open up & you should be able to define the columns and format them the way you want to. Then save it as an Excel file (never save it as as your original .TXT/CSV file). When you get imported the way you want to, do it one more time & record it as a macro.


2016-02-09 14:01:02

Dean Bush

Ihsan Alrawi

If you want to keep the numbers as your date, you'll have to custom format the date the way you want it. Go to Format, Custom, then enter as a new format dd/mm/y. That will work.

If you want it to look like 09 Feb 15 then go to format, date, scroll down till you see the one you want to use.


2016-02-09 12:35:11

Karl Gregg

Sometimes I get so frustrated by the date entry that I revert to using the apostrophe (') first, then I type the date as I want it to appear in the cell. That works every time. ☺


2016-02-09 11:35:33

Liz Stone

Zip codes which start with zero are changed from 01234 to 1234. how to prevent this please? This would be when the ZIP is in a separate column.


2016-02-09 10:16:41

Scott Renz

Billie:

You enter them with a leading apostrophe:

'12/15 '12-15


2016-02-09 10:10:38

Stu Cram

One method to prevent entered data such as part numbers with hyphens or leading zeros from being treated as dates is to format the cells as text beforehand.


2016-02-09 10:01:50

Billie

If I enter the fraction 12/15 in a cell or a range of numbers 12-15, how can I prevent Excel from displaying these data as dates?


2016-02-09 09:25:07

Shandor

I'm in the same boat as Mick; working with serial numbers as well as dates, and needing a quick way to stop Excel's not so helpful defaults! We never leave the year out in dates, and often use dashes and leading zeros in serial numbers. Part numbers with numerals between 1 and 12 invariably become dates and it's burdensome to manually prepare a sheet formatted as text to paste these into. Database output can be programmed to do it right, but informal work with spreadsheets is frustrating. Thanks for any further ideas.


2016-02-09 07:44:22

Mick Hind

Hi Allen,
the really useful tip here would be how to stop Excel treating these as dates, that and dropping leading 0s are my biggest problem when importing serial number data.
I know you can add the single quote to tell Excel to treat as text but that isn't always possible e.g. opening a csv file.
Regards,
Mick


2013-11-13 06:04:54

Ihsan Alrawi

Dear Sir
how I can changing the format of writing date from mm/dd/y to dd/mm/y

with my best regards


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing