David,
I think you are right about the Windows Regional Options setting, I am using
U.S settings.
Thanks again for the feedback
Juan


"David Biddulph" wrote:

The problem is not that the cell is *formatted* as text; the problem is
that the cell *contents* are text.

Cell formatting governs how a number is displayed, but the reason for the
#VALUE! error is that the contents are not a number but text.
The formula that gave the original #VALUE! error was given at the start of
the thread and was =(A2-A1+1)

There are a number of possible reasons for the content being text, and use
of the wrong Windows Regional Options could be one of them. If the options
are set to expect UK date and one types in 09/25/09, the cell contents will
be treated as text (and would thus give a #VALUE! error in the DATEDIF
situation which James reported, or in the original subtraction formula). If
the number typed in is 09/03/09, then it wouldn't result in text but would
merely be interpreted as 9th March instead of 3rd September.

Another possibility for the reason for the cell contents being text is the
cell being formatted as text before the number is typed in, as you mentioned
in your previous post. Another possibility is that the data (perhaps
imported from another application) includes spaces or non-breaking spaces.
--
David Biddulph

"Fred Smith" wrote in message
...
The Isnumber and Istext results have nothing to do with US or UK date
formats. The problem is the cell is formatted as text.

What formula did you use to get a #Value error in Datedif?

Regards,
Fred.

"James Silverton" wrote in message
...
Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600:

If you entered the date, then the likely problem is that your cell was
formatted as text beforehand.

Try this:
1. Format the cell as a date
2. Re-enter the date.

Now try your formula.

Regards,
Fred

"Juan Carlos" wrote in message
...
David,
I'm getting false for =ISNUMBER(A1)(A2) and True for
=ISTEXT(A1)(A2). I think my problem have to do with how the
dates are entered. I don't think that excel recognized the
format dd/mm/yy to calculate number of days between dates.

I'm going to convert the A1 and A2 to dates by adding 2 more columns
using the following formula
=DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then
will do the calculation from there.

Unless you have another solution for me

Juan

Juan

"David Biddulph" wrote:

If you are getting a #VALUE! error I'd be willing to bet
that you haven't changed them from text to dates. If they
are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE,
and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps
you'd care to tell us what those formulae do
return? -- David Biddulph
"Juan Carlos" wrote

Is the problem perhaps using British date format and US Excel. I just
tried it with two dates, one pair, British dd/mm/yy, gave #VALUE in
DATEDIF. Another pair, US format, mm/dd/yy, worked (EXCEL 2002).
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not