Excel 2010 – Display Dates Correctly In MS Excel

by Abhishek Bhatnagar on April 16, 2010

Advertisement

You might have used Microsoft Excel sometime if you ever created excel sheets etc. The MS Excel is a powerful tool and a part of Microsoft Office Suite. I use MS Excel for creating sheets, reports and maintain financial data also.

MS-excel

I usually use one or two columns to enter dates associated with events. One problem I used to face when I started using MS Excel was the display of Dates in Excel sheets. The dates appeared weird, sometimes different rows displayed dates in different formatting, it makes the sheets hard to understand. In this article, I will tell you a simple way to display the dates in your excel sheets correctly and neatly.

The way dates appear In MS Excel largely depends on the format you enter them in the cell, for example, if I enter date as “15 April 2010”, it will show like “15-APR-10” in the destination cell. See the example snapshot below:

date-fromatting-in-ms-excel

To change the way dates look in MS Excel, right click on any of the date entry and click the option called “Format Cells…

format-cells

This will open a Format Cells window as seen below. In my case, the format selected was a custom format which shows Date-Month-Year format of date because I entered it that way. To change the way or format to show dates in MS Excel, just click the Category “Date” on the left hand pane on this window and you can see the formats of the dates supported with examples and then select the one which you want to keep.

default-date-setting-in-ms-excel

When I click date, the formats available include :

  • month/date/year
  • Day, Month Date, Year
  • month/year

and lot more. You just need to try and few to figure out what suits you the best. Once you find out which format of date you need in your excel sheet, click OK.

Advertisement

select-standard-date-fromats

Below is the changed format I applied to the cell c3 with entry April 14 2010 to show Day,Month Day , Year format. To apply this format to remaining cells in this column, I can either select all of them together and use format cells, which is a quick way. Other way is just to replicate the above style for all the entries.

date-format-updates   

To replicate the style or format of the cell c3 to all the remaining cells, just click to select the cell C3 and click on “Format Painter”  option which looks like a paint brush. This copies the style of this cell and allows you to apply this style to other cells.

select-format-painter

After you have clicked format painter once, select the cells on which you want to apply using the mouse cursor.apply-format-painter

Here it goes, the same format applied to all the cells below it by using the format painter tool.

after-applying-format-painter

Hope you will find this tip easy to follow. In case you have any specific queries about MS Office, you can ask your query at answers.troublefixers.com or mail us as admin[at]troublefixers[dot]com. You can check out more tips on Microsoft Excel and Microsoft Word

If you like this article or this article helped you, you can +1 to recommend this article on google plus.

You can follow us on Twitter or join our Facebook Fan Page. If you have a question to ask us, submit your question at Answers By Trouble Fixers.

Looking for something else? Search here :

{ 11 comments… read them below or add one }

Aaron Davis September 27, 2011 at 6:59 pm

in excel 2010 on an invoice that i use to bill customers the date appears as “###########” and i am unable to manually enter it as well. wondered if you knew of anything to try to fix my problem. thank you

Rohit September 28, 2011 at 2:01 pm

@Aaron, it shows like that when the width of that cell is insufficient, just click on the top of that column, and stretch it to increase the width of the cell to increase the width.

Bart Chapman December 12, 2011 at 11:53 pm

I have found dates that will not cell-format correctly. Although formatted as 1/27/12 3:30 PM, it still shows up as 40935 0.645833333333333. Format paint, cell format, nothing works….

Tommy January 14, 2012 at 12:01 pm

Thank you…so much more useful than the actual “Help” Excel provides.

Elaine March 23, 2012 at 7:57 pm

How I can permanently have my date in date format instead of general format (as number)?
I use Excel 2o1o. Thank you!

Harry Wood May 23, 2012 at 7:27 pm

I am using the Excel Date format June 23, 2012. I input dates as 6/23/12 andthey convert the requested format. fo the first 8 rows. The 9th row date displays as ************ no matter how I enter it. What’s wrong?

Rohit May 25, 2012 at 11:22 am

the width of the cell maybe insufficient to display the date. drag to increase the column widths and the date will be displayed fine.

Wendy May 30, 2012 at 12:24 am

in Word, the pick date field calendar is defaulting to one year prior than the actual date.
Example: if I click on the down arrow, the calendar pops up, but it says May 2011 instead of May 2012. The time on the computer says 2012 so not sure why it is defaulting to 2011. This is happening on every computer. Any ideas?

Milan Tuladhar November 5, 2012 at 12:32 pm

I am facing a date format problem in MS-Excel 20007. One of the column in my worksheet have some date value. It was OK until first 15 rows, date is displayed as I wanted. On the sixteen rows I typed some textual notes on one of a cell in the same column. After that row, date I typed is displayed as a number. I tried every formats, but it could not get displayed in date format. What may be its’ solution?

Steph Batt February 7, 2013 at 3:51 am

I have a number of Excel 2010 sheets that have macros that prompt users to enter a date. Being in Australia the date needs to be in the format of dd/mm/yyyy. However, if the date is less than the 12th of the month, it is automaticly changed to mm/dd/yyyy format when written to the cell. I have checked the Regional steeings & they are set to dd/mm/yyyy. It doesn’t matter what format I have on the cell. Is there anywhere else that I should be looking?

Carol McAlonis February 26, 2013 at 11:20 pm

I am using Excel 2010 for a large spreadsheet. One of the columns is for an expiration date in the format xx/xx/xxxx. When I type a date as 12/31/29, it automatically populates as 12/31/2029. However, as soon as I get into the 2030′s and above and type 12/31/30, it populates as 12/31/1930. Any ideas how to correct this so that my future expiration dates are in the 21st century?

Leave a Comment

Previous post:

Next post:

© TroubleFixers – All about fixing computer troubles2007-2014 . All Rights Reserved.