How to subtract two date Problem

[Guest]

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!!

 

[Guest]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Try,

=MONTH(B1)-MONTH(A1)

Format as general.

Mike

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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!!
|

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

thanks, it works

=(b1-a1)/365*12 and format C1 as number not a date
--
paul
(e-mail address removed)
remove nospam for email addy!
c1=b1-a1

 

[Guest]

thanks, it works

=(b1-a1)/365*12 and format C1 as number not a date
--
paul
(e-mail address removed)
remove nospam for email addy!
c1=b1-a1