HI good guys...hope to participate in your money wise studies....i got a
workbook that may appear strange but the bottomline is there, it may work for
us...I am not fund using the indirect function because i go with direct
process to easily trace errors. Do you work out this type of worksheet under
a circular formulation, if you do i can share this to u and vis a vis....

"vezerid" wrote:

The #REF! error probably comes from INDIRECT. This means that these
cells did not really contain a date. Either they had been set to Text
format (unlikely, since retyping corrected the problem), or you thought
they contained a date and they actually did not.

Things to check: Are any dates left aligned istead of right-aligned? Is
there any chance someone changed the Regional Settings (Control Panel)
from european (dd-mm-yy) to US (mm-dd-yy) or vice versa? Were some of
these dates imported from another application?

Ordinarily you should not have this problem in the long run. If new
dates added are real dates it should keep working. The quick way to
correct text-date is to copy a blank cell, then select all dates and
Edit|Paste Special...|Add.

HTH
Kostis

Burt wrote:
I figured out the problem. For some reason if I retype the dates on the rows
that werent working, the formula suddenly works again.

Is there anyway of quickly updating all the dates without manually retyping
them? and why did this happen in the first place?


"Burt" wrote:

I thought it had worked but theres a slight problem. When I copy the formula
down, there is a secton in my list for which the formula doesnt work and
returns a #Ref! error. I cant see why it would do this as its worked
perfectly for all the others. The rows in question are exatly the same as
those for which the formula has worked.

Any suggestions?



"Burt" wrote:

...what can I say. Genius!!

Thank you so much!!




"vezerid" wrote:

Roger,

Thank you, I am flattered <s.
Yes, you are right, I had created an extra column because for a while I
had a discrepancy in the total number of days (forgot the +1). The OP
should paste your formula.

Regards,
Kostis


Roger Govier wrote:
Hi Kostis

Very nice solution!
There might be a typo though as I think the formula in D2 needs to start
with dates from Year D1 not E1

=$C2/($B2-$A2+1)*
SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))=DATE(D$1,8 ,1))*
(ROW(INDIRECT($A2&":"&$B2))<DATE(D$1+1,8,1)))

--
Regards

Roger Govier