Certain Months not recognized

[tmcbeth80]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I created a new file in Excel 08 for Mac and I have a column of dates in the form, mm/dd/yyyy. I want to determine the number of days between Today's date and the date in the column so I entered the following formula in the next column:

=Today()-DATEVALUE(A1)

I dragged the formula down the column of some 200+ cells but only dates with the months 3,7,10, & 12 provide a result. The other months show a "#VALUE" error. And, ALL the cells that contained the working months 3, 7, 10, or 12 do work. Any ideas why certain months are not recognized?

I checked the cell formats so they're all the same...

 

[CyberTaz]

Well, formatting doesn't always tell the tale If the content was
originally entered as Text you can change the Number Formatting to anything
you want & you''ll still get an error when referring to that cell in a
formula... So just because it "looks" like a date doesn't mean it is. The
error suggests that it may not be.

Select one of the suspect date cells keeping in mind what that date should
be. Go to Edit> Clear> All, then retype the date [Do Not just cut & paste -
that defeats the purpose]. If the formula referring to that cell displays the
appropriate result you'll know that's what the problem is.

HTH |:>)
Bob Jones
MVP Office:Mac

 

[tmcbeth80]

Bob,

I "Cleared All" in a suspect cell and typed in the date. Still go the same result. I went through the list and I only have a problem on months 6, 9, 11. I know it sounds strange, but I can't figure it out. I've been using Excel for awhile and know a decent bit about the program but I'm at a loss with this one.

Actually, I just cleared a cell that WAS working before and typed in the Date manually (before it used a formula to combine a month and a day with a year to get the full date) and the result was the "#value" error. I think in order to describe the situation would take a lot of time so I'm just going to have to manually determine the few that aren't working.