Help with a formula

[paulsolar]

Hi All

I'm not sure if this is possible to do with a formula, but if it is I'm sure I have come to the right place.

I'm attempting to write a formula that can cope with this scenario.

I have an invoice template that is working fine but I need to add a payment due date to the invoice for our accounting system. I have done this for all our customers apart from one as their payment terms are quite unusual.

The customer pays us twice a month, one payment on the 7th of the month the second payment is on the 20th of the month. (unless the payment date falls on a weekend, in this case they pay us on the Monday, but I can manage without this being corrected)

Basically all the invoices we raise up until the 14th of the month are paid on the 20th of the next month, all invoice raised from the 15th until the end of the month are paid on the 7th of the of the next but one month.

An example would be 1st Jan invoice would be paid 20th Feb, 15th Jan invoice would be paid 7th March

The cell that contains the date is G12. and the date format is dd/mm/yyyy

I've been trying to come up with an answer to this myself since 7:45 and I have to say I have failed dismally. Any help would be really appreciated.

Cheers

Paul

 

[Sergius]

Why don't you give a small fragment of your data using XL2BB? As it is, as it should. I think then help will be faster.

 

[paulsolar]

Why don't you give a small fragment of your data using XL2BB? As it is, as it should. I think then help will be faste

 

[paulsolar]

There is a lot of confidential information in the workbook, but I have created an example

 

[paulsolar]

There is a lot of confidential information in the workbook, but I have created an example

sorry pressed send to soon, I have created an example but cant seem to upload it

 

[abhishekxl]

Let's say you have invoice date mentioned in cell E6. Then below formula will work.

=IF(
DAY(E6) > 15,
E6 + DAYS(EOMONTH(E6, 0), E6) + 1
+ DAYS(EOMONTH(E6, 1), EOMONTH(E6, 0) + 1) + 7,
E6 + EOMONTH(E6, 0) - E6 + 20
)

 

[paulsolar]

sorry pressed send to soon, I have created an example but cant seem to upload it

Problem solved, i used a crib sheet.

I created a sheet and sorted the the payment dates against the actual date any just used a vlookup

Let's say you have invoice date mentioned in cell E6. Then below formula will work.

=IF(
DAY(E6) > 15,
E6 + DAYS(EOMONTH(E6, 0), E6) + 1
+ DAYS(EOMONTH(E6, 1), EOMONTH(E6, 0) + 1) + 7,
E6 + EOMONTH(E6, 0) - E6 + 20
)

Hi, Many thanks for your help. I think this is black magic, it worked perfectly!!! I never knew EOMONTH existed. Absolute eye opener for me.

Cheers and thanks again. Paul