Thread: If dates return value

Hi again,

I think I lost my post. But anyway, sorry to be back again but I have a tedious work that I think can be done with the right formula. I have a start & end date and I need to post an amount every let's say 60 days from start to finish. I have already the formula for calculating the days of each month but I just need to insert the condition in the formula. I've attached the file which explains better what I need.

Thanks once again for all your help.

If (big IF) I've understood:

E7: =IF(OR($C7>E$6,$D7<E$6),0,$C$3*OR($C7=E$6-DAY(E$6)+1,MOD(FLOOR(E$6-$C7,30),$C$4)=0))
copied across

For your second example - the same formula as above but with an additional test in the 2nd OR of $D7=E$6
(references altered of course)

Correct - the prior suggestion was based on 75 days.

Perhaps the below might work though in truth I've not really tested in full and it has limited scope (ie assumes days always >=1 month - ie result never a multiple of C4, C21)

Code:

F7:
=IF(OR($C7>F$6,$D7<(F$6-DAY(F$6)+1)),0,$C$3*(MOD(F$6-$C7,$C$4)<DAY(F$6)))
copied across

F21:
=IF(OR($C21>F$20,$D21<(F$20-DAY(F$20)+1)),0,$C$17*OR(DATE(YEAR($D21),MONTH($D21)+1,0)=F$20,MOD(F$20-$C21,$C$18)<DAY(F$20)))
copied across

To reiterate though - I don't think the above is ideal.

It can be more difficult than you expect when you use days rather than months - eg every 30 days from 31 Jan would mean no collection in Feb - is this desired etc etc ?

Regards commencement in E - simply copy the formula provided from F7 to E7 (F21 to E21).

Please provide an example illustrating errors... the formulae worked for me.

On an aside what happens on a 21 day rotation - presumably in certain months you would need to duplicate the C3 value, no ?

I've not looked at this in depth as yet but at first glance I would say we need to establish something fairly fundamental...

Should the day upon which an amount is invoked be included or excluded from the rolling days ?

eg your file shows 31 days in E9 for Jan 2010 as opposed to 30 ... given 34k is invoked on the 1st of the month should that date not be discounted - ie 30 days used up of the next 90 days.

OK by my reckoning the below should work for the first scenario irrespective of interval period - and to reiterate the "rolling days" commences the day after last payment date.

Code:

E7:
=IF(OR($C7>E6,$D7<(E6-DAY(E6)+1)),0,$C3*IF(DATE(YEAR($C7),MONTH($C7)+1,0)=E6,1+INT((E6-$C7)/$C4),INT((MOD(MAX(0,(E6-DAY(E6))-$C7),$C4)+DAY(MIN($D7,E6)))/$C4)))
copied across to AB7

E7:AB7 can be copied directly to E20:AB20

I will put together another variant for the second scenario which incorporates the need / potential for multiple amount in final period and will post later.

ricyilas, suspect we may have scared you off

FWIW - I had a quick look at this again just now re: scenario 2 and I *believe* the below should work:

Code:

E38:
=IF(OR($C38>E37,$D38<(E37-DAY(E37)+1)),0,$C34*IF(DATE(YEAR($C38),MONTH($C38)+1,0)=E37,1+INT((E37-$C38)/$C35),MAX(DATE(YEAR($D38),MONTH($D38)+1,0)=E37,INT((MOD(MAX(0,(E37-DAY(E37))-$C38),$C35)+DAY(MIN($D38,E37)))/$C35))))

the assumption in the above is that the multiple for the final month will be the greater of 1 and the standard multiple (ie should 2 payments be required in the final month).