I'm using Excel 2000 and trying to use the datedif function. I've formated 2
columns as date m/dd/yyyy and left the formula column general I'm entering
dates
A1: 1/1/2002
B1: 1/1/2005
I'm entering the formula in C1
=datedif(b1,a1,"M")
I'm looking for the nmber of months between 2 dates
I get #NUM! for a result.
Thanks
John
The first date must be earlier than the second date. Try
=DATEDIF(A1,B1,"m")
See www.cpearson.com/excel/datedif.htm for more information.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"JohnH" <[email protected]> wrote in message
news:[email protected]...
> I'm using Excel 2000 and trying to use the datedif function.
> I've formated 2
> columns as date m/dd/yyyy and left the formula column general
> I'm entering
> dates
> A1: 1/1/2002
> B1: 1/1/2005
>
> I'm entering the formula in C1
> =datedif(b1,a1,"M")
>
> I'm looking for the nmber of months between 2 dates
>
> I get #NUM! for a result.
>
> Thanks
> John
>
From Chips' site http://www.cpearson.com/excel/datedif.htm
=DATEDIF(Date1,Date2,Interval)
Date1 must be less than (earlier) or equal to Date2. Otherwise, =DATEDIF
will return a #NUM! error. If either Date1 or Date2 is not a date, =DATEDIF
will return a #VALUE! error.
By the way:
You do not need to format cells before entering dates. Just type the data
as, for example, 1/1/2002 and Excel recognizes a date.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"JohnH" <[email protected]> wrote in message
news:[email protected]...
> I'm using Excel 2000 and trying to use the datedif function. I've formated
> 2
> columns as date m/dd/yyyy and left the formula column general I'm entering
> dates
> A1: 1/1/2002
> B1: 1/1/2005
>
> I'm entering the formula in C1
> =datedif(b1,a1,"M")
>
> I'm looking for the nmber of months between 2 dates
>
> I get #NUM! for a result.
>
> Thanks
> John
>
a1,b1
--
Don Guillett
SalesAid Software
[email protected]
"JohnH" <[email protected]> wrote in message
news:[email protected]...
> I'm using Excel 2000 and trying to use the datedif function. I've formated
> 2
> columns as date m/dd/yyyy and left the formula column general I'm entering
> dates
> A1: 1/1/2002
> B1: 1/1/2005
>
> I'm entering the formula in C1
> =datedif(b1,a1,"M")
>
> I'm looking for the nmber of months between 2 dates
>
> I get #NUM! for a result.
>
> Thanks
> John
>
Hi John, datedif calculates de difference of the two dates, but the formula
must be
DATEDIF(INITIAL DATE,LAST DATE,TYPE OF DIF)
Try to put Datedif(a1,ba,"m")
hope this helps
regards from Brazil
Marcelo
"JohnH" escreveu:
> I'm using Excel 2000 and trying to use the datedif function. I've formated 2
> columns as date m/dd/yyyy and left the formula column general I'm entering
> dates
> A1: 1/1/2002
> B1: 1/1/2005
>
> I'm entering the formula in C1
> =datedif(b1,a1,"M")
>
> I'm looking for the nmber of months between 2 dates
>
> I get #NUM! for a result.
>
> Thanks
> John
>
Hi
For case earlier/later dates aren't ordered column-wise
=DATEDIF(MIN(A1,B1),MAX(A1,B1),"m")
Arvi Laanemets
"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> The first date must be earlier than the second date. Try
> =DATEDIF(A1,B1,"m")
>
> See www.cpearson.com/excel/datedif.htm for more information.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "JohnH" <[email protected]> wrote in message
> news:[email protected]...
> > I'm using Excel 2000 and trying to use the datedif function.
> > I've formated 2
> > columns as date m/dd/yyyy and left the formula column general
> > I'm entering
> > dates
> > A1: 1/1/2002
> > B1: 1/1/2005
> >
> > I'm entering the formula in C1
> > =datedif(b1,a1,"M")
> >
> > I'm looking for the nmber of months between 2 dates
> >
> > I get #NUM! for a result.
> >
> > Thanks
> > John
> >
>
>
Thanks All. Reversing the formual was the solution.
Thanks Much!
John
"JohnH" wrote:
> I'm using Excel 2000 and trying to use the datedif function. I've formated 2
> columns as date m/dd/yyyy and left the formula column general I'm entering
> dates
> A1: 1/1/2002
> B1: 1/1/2005
>
> I'm entering the formula in C1
> =datedif(b1,a1,"M")
>
> I'm looking for the nmber of months between 2 dates
>
> I get #NUM! for a result.
>
> Thanks
> John
>
Try reversing the order of b1 and a1. In the test I tried, that's how I got
rid of the #num error.
Kevin Vaughn
"JohnH" wrote:
> I'm using Excel 2000 and trying to use the datedif function. I've formated 2
> columns as date m/dd/yyyy and left the formula column general I'm entering
> dates
> A1: 1/1/2002
> B1: 1/1/2005
>
> I'm entering the formula in C1
> =datedif(b1,a1,"M")
>
> I'm looking for the nmber of months between 2 dates
>
> I get #NUM! for a result.
>
> Thanks
> John
>
That's weird. I could have sworn when I looked at this question, there were
no replies. Then I replied and I see like 8 people have already answered.
--
Kevin Vaughn
"Kevin Vaughn" wrote:
> Try reversing the order of b1 and a1. In the test I tried, that's how I got
> rid of the #num error.
> Kevin Vaughn
>
>
> "JohnH" wrote:
>
> > I'm using Excel 2000 and trying to use the datedif function. I've formated 2
> > columns as date m/dd/yyyy and left the formula column general I'm entering
> > dates
> > A1: 1/1/2002
> > B1: 1/1/2005
> >
> > I'm entering the formula in C1
> > =datedif(b1,a1,"M")
> >
> > I'm looking for the nmber of months between 2 dates
> >
> > I get #NUM! for a result.
> >
> > Thanks
> > John
> >
It's the nature of the beast. You think you are the first reply and when you
come back there are several ahead of you. I also wish there wasn't the
delay. I think it has something to do with where you are but that's a guess.
--
Don Guillett
SalesAid Software
[email protected]
"Kevin Vaughn" <[email protected]> wrote in message
news:[email protected]...
> That's weird. I could have sworn when I looked at this question, there
> were
> no replies. Then I replied and I see like 8 people have already answered.
> --
> Kevin Vaughn
>
>
> "Kevin Vaughn" wrote:
>
>> Try reversing the order of b1 and a1. In the test I tried, that's how I
>> got
>> rid of the #num error.
>> Kevin Vaughn
>>
>>
>> "JohnH" wrote:
>>
>> > I'm using Excel 2000 and trying to use the datedif function. I've
>> > formated 2
>> > columns as date m/dd/yyyy and left the formula column general I'm
>> > entering
>> > dates
>> > A1: 1/1/2002
>> > B1: 1/1/2005
>> >
>> > I'm entering the formula in C1
>> > =datedif(b1,a1,"M")
>> >
>> > I'm looking for the nmber of months between 2 dates
>> >
>> > I get #NUM! for a result.
>> >
>> > Thanks
>> > John
>> >
BTW when I answered this there were NO other replies.
--
Don Guillett
SalesAid Software
[email protected]
"Kevin Vaughn" <[email protected]> wrote in message
news:[email protected]...
> That's weird. I could have sworn when I looked at this question, there
> were
> no replies. Then I replied and I see like 8 people have already answered.
> --
> Kevin Vaughn
>
>
> "Kevin Vaughn" wrote:
>
>> Try reversing the order of b1 and a1. In the test I tried, that's how I
>> got
>> rid of the #num error.
>> Kevin Vaughn
>>
>>
>> "JohnH" wrote:
>>
>> > I'm using Excel 2000 and trying to use the datedif function. I've
>> > formated 2
>> > columns as date m/dd/yyyy and left the formula column general I'm
>> > entering
>> > dates
>> > A1: 1/1/2002
>> > B1: 1/1/2005
>> >
>> > I'm entering the formula in C1
>> > =datedif(b1,a1,"M")
>> >
>> > I'm looking for the nmber of months between 2 dates
>> >
>> > I get #NUM! for a result.
>> >
>> > Thanks
>> > John
>> >
Thanks for letting me know it's not just me.
Originally Posted by Don Guillett
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks