Date Value Error

[Paula]

Hi,

I am trying to calculate the number of days between 2 dates, I am sure in
the past I have simply just did a formula to say date in cell 1 - date in
cell 2, however I get a value error. The dates are formatted i nthe following
way 18/11/2009 for example and I just need to calculate the number of days
between 2 dates.

Many thanks
Paula

 

[Eduardo]

Hi Paula,
check if you don't have any spaces, simple formula should work =B1-A1

 

[Otto Moehrbach]

Paula
Simply subtracting one date from the other date should do it. But that
doesn't work for you. Perhaps one (or both) date is not a date but is text.
Do this. Select each date cell, in turn, and format the cell as "General".
If it's a date (which is really a number), you should get a 5-digit number.
If you get the same "date", that entry is text. In that case, clear the
cell, format it as a date, and re-enter the date. Post back if you need
more. HTH Otto

 

[Paula]

Hi Eduardo

When I drag my formula it works sometimes and not others, how can I find out
what the error is? I don't have any spaces?

Regards

Paula

 

[Eduardo]

Hi,
let' say your dates are in column A and B, in another two columns enter

=trim(A1)
=trim(B1)

that will ride off any blank space

 

[Eduardo]

sorry I press send before finishing
then copy this two columns as values on top of your data

 

[Gord Dibben]

Try entering the dates as m/d/y rather than d/m/y

Date entry depends upon your Regional Settings


Gord Dibben MS Excel MVP