Month Formula

[vgarrim]

I created a Excel Spreadsheet in (2003) a while back which worked beautifully, however, now that I have transferred it to (2007) I keep getting a #VALUE! from Row 556 on. I am attaching a spreadsheet with the copied formula which works fine, and the one that does not. I have not done anything to the spreadsheet. I simply opened and started typing. The purpose of the column for the month is that I want to be able to sort all the January, February, etc. whenever I want without having to, after typing the date, having to make sure that I type the corresponding month.

I did clear the contents of the formula and then copied a good formula into it. Same result, #VALUE!.

I chose a random cell after Row 556 and the result is the same #VALUE! So, all the formulas that were copied to Row 556 on has this particular "whatever error". Obviously something is in there, however, I have been unable to see it. Can somebody check the enclosed spreadsheet and see if there is something in the cell that is causing the problem. I cannot send the whole spreadsheet because it has sensitive information in it. But the spreadsheet consists of first name, last name, address, etc., including the birth date of the person.

It does not matter if I go back to the machine that has Vista (old version) and the new machine that has Vista 7 (newer version). It happens on both machines.

I just noticed that the dates in the formulas display differently, 3/14/14 and 3/14/1914, however, when I check the cell format both that the same format chosen, Date *3/14/2001.

Thank you in advance for your help.

[Jezza]

I converted your file to a CSV and it appears there are some rogue characters in the data string

3/?5/?58,#VALUE!,,,,,,,,,,,,,, 
20/09/1941,9,,,,,,,,,,,,,,

Note the question marks, so it looks like there has been some data corruption.

[mavericktse]

The MS Excel Help file stated that if the date parameter is entered as string, there may be problem for the MONTH() function. Try converting your dates into formulas "=DATE(year,month,day)"
There may be some problem during conversion from xls to xlsx.

[dvdweir1]

Try formating the date column as mm/dd/yyyy. That way all entries into those cells display the date and the Month() function recognizes the numeric month.

[stans]

The format seems to be the problem. I opened the workbook and re-entered the date and the formula returned a 3.
I am using 2007.
The original date may have been entered as text.

[Jezza]

Formatting will not help using the various formulas, I have tried before my post, I have a feeling some of this data may have been imported

[HansV]

The original date may have been entered as text.

That is indeed the problem. Cell A1 contains text that looks like a date, cell A3 contains a real date. You can see this by setting the horizontal alignment to General. A1 will remain left-aligned while A3 will become right-aligned (the default for dates).

But the usual methods for converting text to dates don't work - there appear to be hidden characters in the text string, and Jezza's experiment confirms that.