formula question

[Fiona]

if I have a date in colum A and a date in colum B, what would the formula be
to find out how many months inbetween the two dates?

--
fiona05

[Jon Quixley]

Morning Fiona

Set up the formula so (eg) =B1-A1 and format the cell using FORMAT/CELLS/NUMBER select CUSTOM and enter in the TYPE box this: mm

This gives you whole months between dates and might not be as accurate as you might need

Jon

[David McRitchie]

Hi Fiona,
The correct answer is going to depend on how well you want to define
what constitutes a months worth of days. Not all months have the same
number of days. If you start on the the 31st and end on the last day
of the next month with 30 days was that a month, if you go to the
1st day of the next month do you then have one month. Likewise
if you start at the end of a 30 day month and end at the 30th of a 31 day
month do you have a month or are you a day short of month.

Take a look at DATEDIF it will probably do what most people
would expect as an answer. If you have Excel 2000 (and I think Excel 2003) you
will find it in HELP otherwise you won't (don't know about MACs). In any case
it has always been in Excel and was once documented in the MS KB as the
"Undocumented Function" then they removed the article..

Anyway whether or not it is in your Excel HELP, you should look at
Chip Pearson's page, which has some additional explanations, and examples:
http://www.cpearson.com/excel/datedif.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Fiona" <[email protected]> wrote in message news:[email protected]...
> if I have a date in column A and a date in column B, what would the formula be
> to find out how many months inbetween the two dates?
>
> --
> fiona05

[Sandy Mann]

One caveat, =B1-A1 will contain the number of days between the two dates
even although you have formatted it to look like months. For example with
today's date in B1 and January 1st 2005 in A1 the cell will be holding 271
formatted to show 9. If Fiona used this to say 9 months at $15 she will get
$4065 instead of the expected $135.

Out of interest DATEDIF as David McRitchie suggests, returns 8 as a real
(usable) number for the above dates.
--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Jon Quixley" <[email protected]>
wrote in message
news:[email protected]...
>
> Morning Fiona
>
> Set up the formula so (eg) =B1-A1 and format the cell using
> FORMAT/CELLS/NUMBER select CUSTOM and enter in the TYPE box this: mm
>
> This gives you whole months between dates and might not be as accurate
> as you might need
>
> Jon
>
>
> --
> Jon Quixley
> ------------------------------------------------------------------------
> Jon Quixley's Profile:
> http://www.excelforum.com/member.php...o&userid=25803
> View this thread: http://www.excelforum.com/showthread...hreadid=471660
>