updating DATEDIFF arguments in formula

  • Thread starter Thread starter cyraxote
  • Start date Start date
C

cyraxote

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
 
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.
 
It's probably undocumented because it gives some strange results at times.
It was documented in 2000.


--


Regards,


Peo Sjoblom
 
Bonsour® (e-mail address removed) 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?
Click to expand...

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)"
 
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.
 
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.
 
OK, this appears to be working at the moment, or at least will require
less maintenance.

Thanks!
 
I'd try this suggestion once more.

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