How to subtract two date Problem
 

Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me €œvalue€� which is an error
Can any body help me solving this problem?
Thanks!!

How to subtract two date Problem
 

=(b1-a1)/365*12 and format C1 as number not a date
--
paul

remove nospam for email addy!
c1=b1-a1


"ghost" wrote:

Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me €œvalue€� which is an error
Can any body help me solving this problem?
Thanks!!

How to subtract two date Problem
 

DATEVALUE() converts a text representation of a date into a real date.

To get the number of days between the two dates simply subtract them:

=B1-A1

To get the number of years use DATEDIF() as in:

=DATEDIF(A1,B1,"y")

or for the number of months left over use:

=DATEDIF(A1,B1,"ym")

and for thr odd days use:

=DATEDIF(A1,B1,"md")

to combine them all together:

=DATEDIF(A1,B1,"y") & " Years "&DATEDIF(A1,B1,"ym")&" Months
"&DATEDIF(A1,B1,"md")&" Days"


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ghost" wrote in message
...
Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me "value" which is an error
Can any body help me solving this problem?
Thanks!!

How to subtract two date Problem
 

Try,

=MONTH(B1)-MONTH(A1)

Format as general.

Mike
"ghost" wrote:

Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me €œvalue€� which is an error
Can any body help me solving this problem?
Thanks!!

How to subtract two date Problem
 

http://www.cpearson.com/excel/datedif.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ghost" wrote in message ...
| Hi
| I have a sheet to calculate the number of months between to date.
| in A1 = 05/26/2007
| B1 =06/26/2006
| C1 = =DATEVALUE (B1-A1)
| After that C1 shows me "value" which is an error
| Can any body help me solving this problem?
| Thanks!!
|

How to subtract two date Problem
 

Because the dates are in different years your formula returns 1 for dates 13
months apart.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mike H" wrote in message
...
Try,

=MONTH(B1)-MONTH(A1)

Format as general.

Mike
"ghost" wrote:

Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me "value" which is an error
Can any body help me solving this problem?
Thanks!!

How to subtract two date Problem
 

I just noticed when replying to Mike H that you want the answer in the
number of months so use:

=DATEDIF(A1,B1,"m")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
DATEVALUE() converts a text representation of a date into a real date.

To get the number of days between the two dates simply subtract them:

=B1-A1

To get the number of years use DATEDIF() as in:

=DATEDIF(A1,B1,"y")

or for the number of months left over use:

=DATEDIF(A1,B1,"ym")

and for thr odd days use:

=DATEDIF(A1,B1,"md")

to combine them all together:

=DATEDIF(A1,B1,"y") & " Years "&DATEDIF(A1,B1,"ym")&" Months
"&DATEDIF(A1,B1,"md")&" Days"


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ghost" wrote in message
...
Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me "value" which is an error
Can any body help me solving this problem?
Thanks!!

How to subtract two date Problem
 

thanks, it works

"paul" wrote:

=(b1-a1)/365*12 and format C1 as number not a date
--
paul

remove nospam for email addy!
c1=b1-a1


"ghost" wrote:

Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me €œvalue€� which is an error
Can any body help me solving this problem?
Thanks!!

How to subtract two date Problem
 

thanks, it works

"paul" wrote:

=(b1-a1)/365*12 and format C1 as number not a date
--
paul

remove nospam for email addy!
c1=b1-a1


"ghost" wrote:

Hi
I have a sheet to calculate the number of months between to date.
in A1 = 05/26/2007
B1 =06/26/2006
C1 = =DATEVALUE (B1-A1)
After that C1 shows me €œvalue€� which is an error
Can any body help me solving this problem?
Thanks!!