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

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

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

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

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

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

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

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:

=(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

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

It did not change to =(DATEDIF(B2,B11,"d")), which would at least be
progress. It also did not change to =(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® 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
>
> > =(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)))

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 -

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

Thanks!

On May 9, 5:31*pm, "Mais qui est Paul" <Excel-add...@mpfe.fr> wrote:
> Bonsour® 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
>
> > * =(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)))