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. Any idea why not?

Cell A1 says "27 Aug 10". Excel appears to know...

Didn't find your answer?

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.

avatar
By chatman
25th Dec 2011 15:22

This seems to put the date in the right format

=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.

Thanks (0)
Replying to johngroganjga:
avatar
By TaxNoob
27th Dec 2011 22:34

Easier solution...

chatman wrote:

=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 :)

Thanks (1)
Replying to AndyC555:
avatar
By chatman
28th Dec 2011 16:39

@TaxNoob

TaxNoob wrote:

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. 

I must say, this is normally a pretty good solution to the number/text problem. This is the only time I have found it not to work.

Thanks (0)
avatar
By chatman
27th Dec 2011 23:14

Thanks TaxNoob - I had already tried the "x1" solution and it didn't work, but then, as I pointed out in my OP, Excel already appeared to recognise the data as a date to some extent, as it was able to recognise the month correctly. I didn't really want to click each cell individually as there were a lot of them.  The cumbersome formula I used was the only solution that seemed to work.

Thanks (0)
avatar
By Richard Willis
28th Dec 2011 09:51

Try

changing the format to number.  If it REALLY is a date it should appear as the number 40417.00

Thanks (1)
avatar
By chatman
28th Dec 2011 12:37

Thanks Richard Willis. Strangely, that doesn't work either.

Thanks (0)
avatar
By DHarris
28th Dec 2011 14:09

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.

Thanks (1)
avatar
By Richard Willis
28th Dec 2011 14:13

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.

Thanks (1)
avatar
By chatman
28th Dec 2011 15:04

@Richard

Thanks Richard. Assuming  I had time to do that for hundreds of entries, wouldn't it simply be quicker to type '27/08/10' into the relevant cell?

Thanks (0)
avatar
By Richard Willis
28th Dec 2011 16:24

Sorry

Lost the plot; I overlooked that you had loads of entries!

Thanks (1)
avatar
By chatman
28th Dec 2011 16:36

@Richard

LOL

Thanks (0)
avatar
By neileg
29th Dec 2011 15:33

VALUE

Have you tried VALUE(your text date)?

Thanks (0)
avatar
By chatman
03rd Jan 2012 04:08

VALUE(your text date)

No. Will have a go, out of interest, but I don't need a number; I need a dd/mm/yy format.

Thanks (0)
Replying to Tornado:
avatar
By neileg
03rd Jan 2012 09:12

No probs

chatman wrote:

No. Will have a go, out of interest, but I don't need a number; I need a dd/mm/yy format.

Yeah, but if you format the cell as a date, that's what you will get.
Thanks (0)
Simon Hurst
By Simon Hurst
03rd Jan 2012 15:28

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.

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
04th Jan 2012 12:45

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.

Thanks (0)
avatar
By gsgordon
05th Jan 2012 11:13

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? 

Thanks (0)
avatar
By neileg
06th Jan 2012 09:10

@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

Thanks (0)
avatar
By User deleted
06th Jan 2012 16:19

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

 

Thanks (0)