Calculate amount as per payment terms

Because we raising invoices on 07th of every month so that payment terms calculate from that day and invoice will be raise one month later.
example - June'17 month invoice will be raise on 07th July.

Hi,

Below formula working correctly but one changes require that I have not mentioned earlier. Sorry for that.

f4=SUMPRODUCT((TEXT(EOMONTH(DATEVALUE($A$3:$D$3),0)+$E$4,"MMMYY")=TEXT(F$3+0,"MMMYY"))*($A$4:$D$4))

The formula should calculate from the 7th of each month + payment terms and then amount comes under that particular month.

ex. June'17 invoice $100 will be raise on 07th July so its should comes under Aug'17. 07th July +30 Days = Aug'17.

Thank You sooooo much...

its work!!!!!!!!!

Try this

A3:D3 changed from text to date
A3=1/6/17, B3=1/7/17 etc

In F4, then drag acr

Please Login or Register  to view this content.

Hi Murthy,

Thanks for the reply.

In your formula when I put the payment days 180 the June amount comes under Dec'17 but it should be comes under Jan'18.

because June'17 invoice will raise on 07th July + 180 days = 03rd Jan'18.

I have attached the file for your reference.

Hi,

When I convert the in Month format for column A3 to D3 the formula showing "#VALUE!" error message.

Attached is the file for your reference.

Revised formula
In F4, then drag to right

Please Login or Register  to view this content.

Hi,

Thanks for the formula.

But for 15 days the formula not calculating correctly. ex - for June invoice I will post on 07th July + 15 days = 22nd July.

But as per formula its calculating under Aug'17

And for 30 days no amount calculating under Oct'17.

Please find attached file for your reference.

Pl give some more examples with starting date and expected result months.

Hi,

Column A to D is month and invoice amount so I need formula in Column F onward which calculate invoice receiving month as per there payment terms.

and we are posing invoice one month later on the 7th date every month. ex June invoice will post on 7th July and so on.

So out put of formula is like below.

June'17 invoice amount is 100 and payment term is 15 days therefore, it will appear under July'17 Month (column F)

07th July +15 days = 22nd July.

The end goal of formula is check invoice month, take 7th date of 1 month later and add payment terms & then whatever month is coming the amount goes under that particular month.

Hope I am able understand my query.

attached is the sample file for reference.

Sorry for confusion.

Your formula is working properly.

Thank you so much!