updating DATEDIFF arguments in formula

Hi.

I have a column of dates, and at the bottom, I want to calculate the
number of days between the first and last dates. I' using the formula

=(DATEDIF(B6,B15,"d"))

The problem is that as I add dates, I have to edit the formula to
change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
there any way to make this work like other formulas do when you add
rows? Alternatively, is there another way to accomplish this
calculation that will update automatically?

Thanks.

Rodney

Peo
Fri May 09 10:32:41 CDT 2008

If you are only going to calculate days you don't need a function at all,

=B15-B6

formatted as general will suffice

If you insert a row above this formula it will change to

=B16-B7



--


Regards,


Peo Sjoblom


<cyraxote@gmail.com> wrote in message
news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com...
> Hi.
>
> I have a column of dates, and at the bottom, I want to calculate the
> number of days between the first and last dates. I' using the formula
>
> =(DATEDIF(B6,B15,"d"))
>
> The problem is that as I add dates, I have to edit the formula to
> change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
> there any way to make this work like other formulas do when you add
> rows? Alternatively, is there another way to accomplish this
> calculation that will update automatically?
>
> Thanks.
>
> Rodney

GB
Fri May 09 14:07:35 CDT 2008

Datedif is an undocumented function in xl2003 - it does not even appear in
the fx list.

However, when I tried it, it worked, and the formula copied down
faultlessly. I cannot replicate the OP's problem.

Datedif might be useful in some circumstances, such as calculating complete
months. I agree that for days it's pretty pointless.



"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl...
> If you are only going to calculate days you don't need a function at all,
>
> =B15-B6
>
> formatted as general will suffice
>
> If you insert a row above this formula it will change to
>
> =B16-B7
>
>
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
> <cyraxote@gmail.com> wrote in message
> news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com...
>> Hi.
>>
>> I have a column of dates, and at the bottom, I want to calculate the
>> number of days between the first and last dates. I' using the formula
>>
>> =(DATEDIF(B6,B15,"d"))
>>
>> The problem is that as I add dates, I have to edit the formula to
>> change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
>> there any way to make this work like other formulas do when you add
>> rows? Alternatively, is there another way to accomplish this
>> calculation that will update automatically?
>>
>> Thanks.
>>
>> Rodney
>
>

Peo
Fri May 09 15:22:10 CDT 2008

It's probably undocumented because it gives some strange results at times.
It was documented in 2000.


--


Regards,


Peo Sjoblom


"GB" <NOTsomeone@microsoft.com> wrote in message
news:4824a0f9$0$26089$db0fefd9@news.zen.co.uk...
> Datedif is an undocumented function in xl2003 - it does not even appear in
> the fx list.
>
> However, when I tried it, it worked, and the formula copied down
> faultlessly. I cannot replicate the OP's problem.
>
> Datedif might be useful in some circumstances, such as calculating
> complete months. I agree that for days it's pretty pointless.
>
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl...
>> If you are only going to calculate days you don't need a function at all,
>>
>> =B15-B6
>>
>> formatted as general will suffice
>>
>> If you insert a row above this formula it will change to
>>
>> =B16-B7
>>
>>
>>
>> --
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>> <cyraxote@gmail.com> wrote in message
>> news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com...
>>> Hi.
>>>
>>> I have a column of dates, and at the bottom, I want to calculate the
>>> number of days between the first and last dates. I' using the formula
>>>
>>> =(DATEDIF(B6,B15,"d"))
>>>
>>> The problem is that as I add dates, I have to edit the formula to
>>> change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
>>> there any way to make this work like other formulas do when you add
>>> rows? Alternatively, is there another way to accomplish this
>>> calculation that will update automatically?
>>>
>>> Thanks.
>>>
>>> Rodney
>>
>>
>
>

Mais
Fri May 09 16:31:22 CDT 2008

Bonsour® cyraxote@gmail.com avec ferveur ;o))) vous nous disiez :

> I have a column of dates, and at the bottom, I want to calculate the
> number of days between the first and last dates. I' using the formula
>
> =(DATEDIF(B6,B15,"d"))
>
> The problem is that as I add dates, I have to edit the formula to
> change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
> there any way to make this work like other formulas do when you add
> rows? Alternatively, is there another way to accomplish this
> calculation that will update automatically?

B6 is named : FirstDate
Define name LastDate
refers to : =OFFSET(FirstDate,COUNT(FirstDate:B1000)-1,0)

then :
=LastDate-FirstDate
formatted as general

or for the fun ;o)))

=INT((lastdate-firstdate)/365.25)&" year(s)
"&INT(MOD((lastdate-firstdate)/(365.25/12),12))&" month(s)
"&INT(MOD((lastdate-firstdate),365.25/12))&" day(s)"






--
--
@+
;o)))

cyraxote
Thu May 15 10:21:35 CDT 2008

My problem is not that it won't copy down, but that ALL the terms copy
down.

For example,

=(DATEDIF(B6,B15,"d"))

becomes

=(DATEDIF(B7,B16,"d"))

but I want

=(DATEDIF(B6,B16,"d"))

I think one of the other posters talked about naming the cell and
using the defined name in the formula. Will try that.

Thanks.


On May 9, 3:07 pm, "GB" <NOTsome...@microsoft.com> wrote:
> Datedif is an undocumented function in xl2003 - it does not even appear in
> the fx list.
>
> However, when I tried it, it worked, and the formula copied down
> faultlessly. I cannot replicate the OP's problem.
>
> Datedif might be useful in some circumstances, such as calculating complete
> months. I agree that for days it's pretty pointless.
>
> "Peo Sjoblom" <terr...@mvps.org> wrote in message
>
> news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl...
>
> > If you are only going to calculate days you don't need a function at all,
>
> > =B15-B6
>
> > formatted as general will suffice
>
> > If you insert a row above this formula it will change to
>
> > =B16-B7
>
> > --
>
> > Regards,
>
> > Peo Sjoblom
>
> > <cyrax...@gmail.com> wrote in message
> >news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com...
> >> Hi.
>
> >> I have a column of dates, and at the bottom, I want to calculate the
> >> number of days between the first and last dates. I' using the formula
>
> >> =(DATEDIF(B6,B15,"d"))
>
> >> The problem is that as I add dates, I have to edit the formula to
> >> change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
> >> there any way to make this work like other formulas do when you add
> >> rows? Alternatively, is there another way to accomplish this
> >> calculation that will update automatically?
>
> >> Thanks.
>
> >> Rodney

cyraxote
Thu May 15 10:30:09 CDT 2008

OK, using defined labels for cell names didn't change anything.

I'm going to try to describe the problem again, because I think
there's a basic misunderstanding of my problem.

Let's say I have a column of 10 dates. The first date is in B1, and
the last is in B10.

In B12 is the following formula:

=3D(DATEDIF(B1,B10,"d"))

All works well.

A week later, I have a new date. I put the cursor in B11 and choose
Insert | Row. When I look at the formula, it still says

=3D(DATEDIF(B6,B15,"d"))

It did not change to =3D(DATEDIF(B2,B11,"d")), which would at least be
progress. It also did not change to =3D(DATEDIF(B1,B11,"d")), which is
what I want.

I need to anchor it to B1 somehow but keep the second reference
flexible. So far, I have to edit the formula every time I add data.

Does that make more sense now?

Thanks.








On May 9, 5:31 pm, "Mais qui est Paul" <Excel-add...@mpfe.fr> wrote:
> Bonsour=AE cyrax...@gmail.com avec ferveur ;o))) vous nous disiez :
>
> > I have a column of dates, and at the bottom, I want to calculate the
> > number of days between the first and last dates. I' using the formula
>
> > =3D(DATEDIF(B6,B15,"d"))
>
> > The problem is that as I add dates, I have to edit the formula to
> > change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
> > there any way to make this work like other formulas do when you add
> > rows? Alternatively, is there another way to accomplish this
> > calculation that will update automatically?
>
> B6 is named : FirstDate
> Define name LastDate
> refers to : =3DOFFSET(FirstDate,COUNT(FirstDate:B1000)-1,0)
>
> then :
> =3DLastDate-FirstDate
> formatted as general
>
> or for the fun ;o)))
>
> =3DINT((lastdate-firstdate)/365.25)&" year(s)
> "&INT(MOD((lastdate-firstdate)/(365.25/12),12))&" month(s)
> "&INT(MOD((lastdate-firstdate),365.25/12))&" day(s)"
>
> --
> --
> @+
> ;o)))

Craig
Thu May 15 10:27:58 CDT 2008

This is a multi-part message in MIME format.
--------------090006060501050608080006
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Try

=(DATEDIF($B$6,B15,"d"))


Craig


cyraxote@gmail.com wrote:

>My problem is not that it won't copy down, but that ALL the terms copy
>down.
>
>For example,
>
> =(DATEDIF(B6,B15,"d"))
>
>becomes
>
> =(DATEDIF(B7,B16,"d"))
>
>but I want
>
> =(DATEDIF(B6,B16,"d"))
>
>I think one of the other posters talked about naming the cell and
>using the defined name in the formula. Will try that.
>
>Thanks.
>
>
>On May 9, 3:07 pm, "GB" <NOTsome...@microsoft.com> wrote:
>
>
>>Datedif is an undocumented function in xl2003 - it does not even appear in
>>the fx list.
>>
>>However, when I tried it, it worked, and the formula copied down
>>faultlessly. I cannot replicate the OP's problem.
>>
>>Datedif might be useful in some circumstances, such as calculating complete
>>months. I agree that for days it's pretty pointless.
>>
>>"Peo Sjoblom" <terr...@mvps.org> wrote in message
>>
>>news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl...
>>
>>
>>
>>>If you are only going to calculate days you don't need a function at all,
>>>
>>>
>>>=B15-B6
>>>
>>>
>>>formatted as general will suffice
>>>
>>>
>>>If you insert a row above this formula it will change to
>>>
>>>
>>>=B16-B7
>>>
>>>
>>>--
>>>
>>>
>>>Regards,
>>>
>>>
>>>Peo Sjoblom
>>>
>>>
>>><cyrax...@gmail.com> wrote in message
>>>news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com...
>>>
>>>
>>>>Hi.
>>>>
>>>>
>>>>I have a column of dates, and at the bottom, I want to calculate the
>>>>number of days between the first and last dates. I' using the formula
>>>>
>>>>
>>>> =(DATEDIF(B6,B15,"d"))
>>>>
>>>>
>>>>The problem is that as I add dates, I have to edit the formula to
>>>>change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
>>>>there any way to make this work like other formulas do when you add
>>>>rows? Alternatively, is there another way to accomplish this
>>>>calculation that will update automatically?
>>>>
>>>>
>>>>Thanks.
>>>>
>>>>
>>>>Rodney
>>>>
>>>>
>
>
>

--------------090006060501050608080006
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Try<br>
<br>
<pre wrap=""> =(DATEDIF($B$6,B15,"d"))</pre>
<br>
Craig<br>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="mailto:cyraxote@gmail.com">cyraxote@gmail.com</a> wrote:
<blockquote
cite="mid119a47e7-77bf-4e02-92e3-d02f838bbfee@e39g2000hsf.googlegroups.com"
type="cite">
<pre wrap="">My problem is not that it won't copy down, but that ALL the terms copy
down.

For example,

=(DATEDIF(B6,B15,"d"))

becomes

=(DATEDIF(B7,B16,"d"))

but I want

=(DATEDIF(B6,B16,"d"))

I think one of the other posters talked about naming the cell and
using the defined name in the formula. Will try that.

Thanks.


On May 9, 3:07 pm, "GB" <a class="moz-txt-link-rfc2396E" href="mailto:NOTsome...@microsoft.com">&lt;NOTsome...@microsoft.com&gt;</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Datedif is an undocumented function in xl2003 - it does not even appear in
the fx list.

However, when I tried it, it worked, and the formula copied down
faultlessly. I cannot replicate the OP's problem.

Datedif might be useful in some circumstances, such as calculating complete
months. I agree that for days it's pretty pointless.

"Peo Sjoblom" <a class="moz-txt-link-rfc2396E" href="mailto:terr...@mvps.org">&lt;terr...@mvps.org&gt;</a> wrote in message

<a class="moz-txt-link-freetext" href="news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl">news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl</a>...

</pre>
<blockquote type="cite">
<pre wrap="">If you are only going to calculate days you don't need a function at all,
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">=B15-B6
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">formatted as general will suffice
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">If you insert a row above this formula it will change to
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">=B16-B7
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">--
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">Regards,
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">Peo Sjoblom
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap=""><a class="moz-txt-link-rfc2396E" href="mailto:cyrax...@gmail.com">&lt;cyrax...@gmail.com&gt;</a> wrote in message
<a class="moz-txt-link-freetext" href="news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com">news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi.
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">I have a column of dates, and at the bottom, I want to calculate the
number of days between the first and last dates. I' using the formula
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap=""> =(DATEDIF(B6,B15,"d"))
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">The problem is that as I add dates, I have to edit the formula to
change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
there any way to make this work like other formulas do when you add
rows? Alternatively, is there another way to accomplish this
calculation that will update automatically?
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">Thanks.
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">Rodney
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!---->
</pre>
</blockquote>
</body>
</html>

--------------090006060501050608080006--

cyraxote
Thu May 15 10:38:57 CDT 2008

Thanks, Craig, but no dice.

On May 15, 11:27=A0am, Craig Schiller <cschill...@earthlink.net> wrote:
> Try
>
> =A0=3D(DATEDIF($B$6,B15,"d"))
>
> Craig
>
>
>
> cyrax...@gmail.com wrote:
> >My problem is not that it won't copy down, but that ALL the terms copy
> >down.
>
> >For example,
>
> > =3D(DATEDIF(B6,B15,"d"))
>
> >becomes
>
> > =3D(DATEDIF(B7,B16,"d"))
>
> >but I want
>
> > =3D(DATEDIF(B6,B16,"d"))
>
> >I think one of the other posters talked about naming the cell and
> >using the defined name in the formula. Will try that.
>
> >Thanks.
>
> >On May 9, 3:07 pm, "GB" <NOTsome...@microsoft.com> wrote:
>
> >>Datedif is an undocumented function in xl2003 - it does not even appear =
in
> >>the fx list.
>
> >>However, when I tried it, it worked, and the formula copied down
> >>faultlessly. I cannot replicate the OP's problem.
>
> >>Datedif might be useful in some circumstances, such as calculating compl=
ete
> >>months. I agree that for days it's pretty pointless.
>
> >>"Peo Sjoblom" <terr...@mvps.org> wrote in message
>
> >>news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl...
>
> >>>If you are only going to calculate days you don't need a function at al=
l,
>
> >>>=3DB15-B6
>
> >>>formatted as general will suffice
>
> >>>If you insert a row above this formula it will change to
>
> >>>=3DB16-B7
>
> >>>--
>
> >>>Regards,
>
> >>>Peo Sjoblom
>
> >>><cyrax...@gmail.com> wrote in message
> >>>news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com..=
.
>
> >>>>Hi.
>
> >>>>I have a column of dates, and at the bottom, I want to calculate the
> >>>>number of days between the first and last dates. I' using the formula
>
> >>>> =A0=3D(DATEDIF(B6,B15,"d"))
>
> >>>>The problem is that as I add dates, I have to edit the formula to
> >>>>change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
> >>>>there any way to make this work like other formulas do when you add
> >>>>rows? Alternatively, is there another way to accomplish this
> >>>>calculation that will update automatically?
>
> >>>>Thanks.
>
> >>>>Rodney- Hide quoted text -
>
> - Show quoted text -

cyraxote
Thu May 15 11:15:58 CDT 2008

OK, this appears to be working at the moment, or at least will require
less maintenance.

Thanks!

On May 9, 5:31=A0pm, "Mais qui est Paul" <Excel-add...@mpfe.fr> wrote:
> Bonsour=AE cyrax...@gmail.com =A0avec ferveur =A0;o))) vous nous disiez :
>
> > I have a column of dates, and at the bottom, I want to calculate the
> > number of days between the first and last dates. I' using the formula
>
> > =A0 =3D(DATEDIF(B6,B15,"d"))
>
> > The problem is that as I add dates, I have to edit the formula to
> > change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
> > there any way to make this work like other formulas do when you add
> > rows? Alternatively, is there another way to accomplish this
> > calculation that will update automatically?
>
> B6 is named : FirstDate
> Define name LastDate
> refers to : =3DOFFSET(FirstDate,COUNT(FirstDate:B1000)-1,0)
>
> then :
> =3DLastDate-FirstDate
> formatted as general
>
> or for the fun ;o)))
>
> =3DINT((lastdate-firstdate)/365.25)&" year(s)
> "&INT(MOD((lastdate-firstdate)/(365.25/12),12))&" month(s)
> "&INT(MOD((lastdate-firstdate),365.25/12))&" day(s)"
>
> --
> --
> @+
> ;o)))

Dave
Thu May 15 12:52:45 CDT 2008

I'd try this suggestion once more.

If it doesn't work, what happened when you tried it?

"cyraxote@gmail.com" wrote:
>
> Thanks, Craig, but no dice.
>
> On May 15, 11:27 am, Craig Schiller <cschill...@earthlink.net> wrote:
> > Try
> >
> > =(DATEDIF($B$6,B15,"d"))
> >
> > Craig
> >
> >
> >
> > cyrax...@gmail.com wrote:
> > >My problem is not that it won't copy down, but that ALL the terms copy
> > >down.
> >
> > >For example,
> >
> > > =(DATEDIF(B6,B15,"d"))
> >
> > >becomes
> >
> > > =(DATEDIF(B7,B16,"d"))
> >
> > >but I want
> >
> > > =(DATEDIF(B6,B16,"d"))
> >
> > >I think one of the other posters talked about naming the cell and
> > >using the defined name in the formula. Will try that.
> >
> > >Thanks.
> >
> > >On May 9, 3:07 pm, "GB" <NOTsome...@microsoft.com> wrote:
> >
> > >>Datedif is an undocumented function in xl2003 - it does not even appear in
> > >>the fx list.
> >
> > >>However, when I tried it, it worked, and the formula copied down
> > >>faultlessly. I cannot replicate the OP's problem.
> >
> > >>Datedif might be useful in some circumstances, such as calculating complete
> > >>months. I agree that for days it's pretty pointless.
> >
> > >>"Peo Sjoblom" <terr...@mvps.org> wrote in message
> >
> > >>news:OMWbsnesIHA.5580@TK2MSFTNGP04.phx.gbl...
> >
> > >>>If you are only going to calculate days you don't need a function at all,
> >
> > >>>=B15-B6
> >
> > >>>formatted as general will suffice
> >
> > >>>If you insert a row above this formula it will change to
> >
> > >>>=B16-B7
> >
> > >>>--
> >
> > >>>Regards,
> >
> > >>>Peo Sjoblom
> >
> > >>><cyrax...@gmail.com> wrote in message
> > >>>news:71da349f-65f5-4631-a534-1b702d58a7c2@59g2000hsb.googlegroups.com...
> >
> > >>>>Hi.
> >
> > >>>>I have a column of dates, and at the bottom, I want to calculate the
> > >>>>number of days between the first and last dates. I' using the formula
> >
> > >>>> =(DATEDIF(B6,B15,"d"))
> >
> > >>>>The problem is that as I add dates, I have to edit the formula to
> > >>>>change B15 to the reference of the new row (e.g., B16, B17, etc.). Is
> > >>>>there any way to make this work like other formulas do when you add
> > >>>>rows? Alternatively, is there another way to accomplish this
> > >>>>calculation that will update automatically?
> >
> > >>>>Thanks.
> >
> > >>>>Rodney- Hide quoted text -
> >
> > - Show quoted text -

--

Dave Peterson