Cell A1 says "27 Aug 10". Excel appears to know this is a date because =MONTH(A1) returns "8". However, when I apply the format dd/mm/yy to cell A1, it does not change to 27/08/10. Any idea why not?
Replies (19)
Please login or register to join the discussion.
Easier solution...
=IF(ISTEXT(A1),DATEVALUE(MID(A1,1,10)),DATEVALUE(TEXT(A1,"mm/dd/yyyy")))
Don't know why my simple formatting solution didn't work though.
Sounds like the 'date' is entered as text. The phrase to convert is correct and, 'though using it is understandable, it's a pretty complex solution to the issue.
In Excel 2007 there should be an error-checking warning in each cell where the data don't correspond with the format Excel thinks they should be. Clicking on the fix offered will normally resolve but, where that doesn't happen, try this:
If you put a '1' in any empty cell, then right click and select Copy, you can select the cell or cells with your data, right click & select Paste Special, then click on 'Multiply'. This will normally convert text entries to their respective formats (numbers or dates) and is a common workaround for this.
Hope this helps :)
Try
changing the format to number. If it REALLY is a date it should appear as the number 40417.00
It was originally formated as text
If you open a new workbook and enter 27 Aug 10 it will convert the entry into a date format it recognizes. However, if you open the cell and fomat it as text, enter 27 Aug 2010 it will keep the cell in that format no matter what subsequent format you try and use.
Excel does recognise the constituent parts as a date but will not reformat. I cannot imagine an easy way to convert it to a date once its been entered as text.
Type in '=date(2010,8,27)'
This will return the date number for that date which you can then save as value and format as date.
No probs
Yeah, but if you format the cell as a date, that's what you will get.No. Will have a go, out of interest, but I don't need a number; I need a dd/mm/yy format.
VALUE() explanation
Just to provide a bit of explanation. I think that the current entries are text. MONTH() returns the correct month number because MONTH() 'knows' that it needs a number as an argument and therefore 'coerces' the text 'date' to be a real date. VALUE() will also coerce the text to the date and, exactly as neileg says, it can then be formatted to whatever date format you want. A better test than MONTH() of whether the date is a real date or text would be to enter =A1 into another cell (where A1 is replaced with the reference to the suspect date entry). If this other cell is formatted as General, a real date will return the date serial number whereas a text date will just display the same text. If you use --A1(that's two minuses) that should coerce the text date to a number in the same way VALUE() does.
More tutorials from Simon
If you found Simon's comment helpful, there are plenty more like it in the Excel FAQ articles he's compiling for our ExcelZone.
Last year, there was an Excel FAQ on date functions, but it doesn't go into as much detail as this post. Thanks to all the contributors to this thread for adding to our knowledge.
What format is the cell?
Maybe the question is - how did the cell get to be "27 Aug 10"? Is it entered by typing or from a formula/code?
If I want to enter such a date by typing in Excel 2007, I type "27aug10" for speed and the cell then displays as "27-Aug-10" and the cell format shows as Custom, dd-mmm-yy.
If I then change the format to Custom, dd/mmm/yyyy, the cell displays as "27/08/2010".
I get exactly the same results if I type "27 Aug 10".
NB: I just type the characters that are between the quotes. If I preface them by typing a single apostrophe, I get a General format cell which will not change to date format as Chatman found - until I use one of the fixes offered in the error checking mentioned in a previous post.
What does Excel say if you look at the format of your cell?
@gsgordon
Excel is pretty good at spotting dates when entered directly. However, when you import or cut and paste data, dates can be rendered as text.
Cheers, Neil
Use DateValue(A1) ...
Where A1 = '27 Aug 10' (text)
DateValue(A1) into a new cell then just 'Format Cell' the new cell to the date you require