updating DATEDIFF arguments in formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cyraxote@gmail.com - 09 May 2008 16:14 GMT

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

Reply to this Message

Peo Sjoblom - 09 May 2008 16:32 GMT

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

Signature

Regards,

Peo Sjoblom

> Hi.
>
[quoted text clipped - 12 lines]
>
> Rodney

Reply to this Message

GB - 09 May 2008 20:07 GMT

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.

> If you are only going to calculate days you don't need a function at all,
>
[quoted text clipped - 22 lines]
>>
>> Rodney

Reply to this Message

Peo Sjoblom - 09 May 2008 21:22 GMT

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

Signature

Regards,

Peo Sjoblom

> Datedif is an undocumented function in xl2003 - it does not even appear in
> the fx list.
[quoted text clipped - 31 lines]
>>>
>>> Rodney

Reply to this Message

cyraxote@gmail.com - 15 May 2008 16:21 GMT

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.

> Datedif is an undocumented function in xl2003 - it does not even appear in
> the fx list.
[quoted text clipped - 39 lines]
>
> >> Rodney

Reply to this Message

Craig Schiller - 15 May 2008 16:27 GMT

Try

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

Craig

>My problem is not that it won't copy down, but that ALL the terms copy
>down.
[quoted text clipped - 81 lines]
>
>

Reply to this Message

cyraxote@gmail.com - 15 May 2008 16:38 GMT

Thanks, Craig, but no dice.

> Try
>
[quoted text clipped - 73 lines]
>
> - Show quoted text -

Reply to this Message

Dave Peterson - 15 May 2008 18:52 GMT

I'd try this suggestion once more.

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

> Thanks, Craig, but no dice.
>
[quoted text clipped - 75 lines]
> >
> > - Show quoted text -

Signature

Dave Peterson

Reply to this Message

Mais qui est Paul - 09 May 2008 22:31 GMT

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
[quoted text clipped - 6 lines]
> 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)"

Signature

--
@+
;o)))

Reply to this Message

cyraxote@gmail.com - 15 May 2008 16:30 GMT

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.

> Bonsour® cyrax...@gmail.com avec ferveur ;o))) vous nous disiez :
>
[quoted text clipped - 27 lines]
> @+
> ;o)))

Reply to this Message

cyraxote@gmail.com - 15 May 2008 17:15 GMT

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

Thanks!

> Bonsour® cyrax...@gmail.com avec ferveur ;o))) vous nous disiez :
>
[quoted text clipped - 27 lines]
> @+
> ;o)))

Reply to this Message

Rate this thread: