Frequency

[lky2k23]

Hi,

I am trying to populate cells O-BJ with the invoice amount based on the period and frequency.

So Cell A5 would appear every 11 months for 100, and A8 every month from November11 onwards.

Help

[brokenbiscuits]

Not 100% sure I know exactly what you mean, but is the attached about right?

[lky2k23]

Not really no sorry.

I will try to be clear :-/

The value in D5 needs to appear in the month J5 says (January 2012) and then again 11months (H5).

The value in D8 would appear in every month from November 2010

Hope this helps - thanks for trying.

[daddylonglegs]

Try this formula in O5 copied across and down

=IF($J5-DAY($J5)+1>DATE(O$3,O$2,1),"",IF(MOD(DATEDIF($J5-DAY($J5)+1,DATE(O$3,O$2,1),"M"),$H5)=0,$D5,""))

[lky2k23]

perfect - thank you. Wow that seems in depth. Will I ever not need help?

[daddylonglegs]

It's probably not as complex as it first seems. The idea is to find the number of months between J5 and the current column header.

To get a difference in months between two dates, in general, you can use DATEDIF like this

=DATEDIF(Y1,Z1,"M")

where Y1 and Z1 are dates and Y1 is the earlier date of the two,

So here you can get the first of the month from the data in O2 and O3 with

=DATE(O$3,O$2,1)

and, because the column J dates are not always 1st of the month you can convert a date in J5 to the first of that month by using

=$J5-DAY($J5)+1

Note that in the DATE function the $ signs are before the row numbers, in the latter part ($J5-DAY($J5)+1) they are before the column letters. This allows the formula to be dragged across and down with the relevant part only changing ($J5 changes to $J6 when dragged down but stays as $J5 when dragged across whereas O$3 stays as O$3 when dragged down but changes to P$3 etc when dragged across)

....so now we have the difference in months with

=DATEDIF($J5-DAY($J5)+1,DATE(O$3,O$2,1),"M")

Now you want the payment triggered every n months with n being the number in H5 so if we use MOD like this

=MOD(DATEDIF($J5-DAY($J5)+1,DATE(O$3,O$2,1),"M"),$H5)

that returns the remainder when the month difference is divided by the number in H5, so if H5 is 11 that will always return zero as the remainder when the month difference is divisble by 11, so we get zero on the months that you want the payment paid, month 11, 22, 33 etc......so we check if the above returns zero and if it does the figure from D5 is returned, otherwise a blank, so that becomes

=IF(MOD(DATEDIF($J5-DAY($J5)+1,DATE(O$3,O$2,1),"M"),$H5)=0,$D5,"")

That would be sufficient except sometimes the first date in DATEDIF, $J5-DAY($J5)+1 is a later date than DATE(O$3,O$2,1)....in which case DATEDIF returns a #NUM! error so we have an extra IF to suppress that error. If $J5-DAY($J5)+1 is > DATE(O$3,O$2,1) we also want a blank so we add the additional IF function to check that

QED