Help Please! Think IF Function is the best option but not sure how to word it?

[fyhah99]

Hi guys!

So I have a spreadsheet that I have to use to track documents in the company and..

Column D - Required Date (the date a product has to be delivered)
Column E - Payment Terms (eg 30 days payment required after the required date)
Column F - Payment Date (Column D + Column E)
Column G - Actual Payment Date (according to our schedule, which is the 10th or 25th of each month, which is the next 10th or 25th of the month from Column F)

So..

My question is.. Is there a formula that I could use to set on Column G?

 

[Andrew Poulsom]

Welcome to MrExcel.

What determines whether it's 10th or 25th?

 

[fyhah99]

Thanks for the welcome!

If the payment date is from the 11th - 25th, the actual payment date is the 25th
If the payment date is from the 26th till 10th of the following month, the actual payment is the 10th of the following month, eg if the paymetn date is the 26th Jan, the actual payment date is the 10th Feb.

 

[Andrew Poulsom]

How about?

=IF(AND(DAY(F1)>=11,DAY(F1)<=25),F1-DAY(F1)+25,DATE(YEAR(F1),MONTH(F1)+(DAY(F1)>=26),10))

 

[fyhah99]

How about?

=IF(AND(DAY(F1)>=11,DAY(F1)<=25),F1-DAY(F1)+25,DATE(YEAR(F1),MONTH(F1)+(DAY(F1)>=26),10))

Thank you! Been trying to work on it as well and ended up with the same result (but a much longer formula):

=IF(DAY(F1)<=10, DATE(YEAR(F1), MONTH(F1), 10), IF((DAY(F1)<26), DATE(YEAR(F1), MONTH(F1), 25), IF(DAY(F1)>25, DATE(YEAR(F1),(MONTH(F1)+1), 10))))