• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating 2 payment dates each month(10th and 25th)

P

powerpivot beginner

Member
Company consolidate payment due 2 times each month, on the 10th and 25th.
For Invoices that come in from 26th of previous month to 10th of current month, company will pay on the 10th of current month.
For invoices that come in from 11th to 25th of the month, the company will pay on the 25th of the month.
Can someone correct my formula. Thanks

=IF(DAY(E10)>25,DATE(YEAR(E10),MONTH(E10)+1,10),DATE(YEAR(E10),MONTH(E10),25))
 
Hi Thanks for your suggestion but the formula did not work.
Invoice Date Jan 8th 2020 is supposed to return a payment date of Jan 10th 2020 but the suggested formula returned a date of Feb 10th 2020.
It seems only the dates that fall between 1st and 10th and have problems.
 

Attachments

  • Book2(ver 2).xlsx
    30.3 KB · Views: 9
Removed +1 added in month. Try now

=IF(AND(DAY(B8)>=11,DAY(B8)<=25),DATE(YEAR(B8),MONTH(B8),25),DATE(YEAR(B8),MONTH(B8),10))
 
Hi

Thanks for spending your time to help me solve the problem. Using "=IF(AND(DAY(B8)>=11,DAY(B8)<=25),DATE(YEAR(B8),MONTH(B8),25),DATE(YEAR(B8),MONTH(B8),10)) "
those that falls from 26th to end of month became wrong.


eg Invoice Date 26th Jan return a date of 10th Jan when it should return 10th Feb.

I think we need to create 2 if statement but I am not sure how.
 
powerpivot beginner, Good afternoon

Try this way

=0+IF(AND(DAY(B8)>=11,DAY(B8)<=25),25&"/"&0&(IF(DAY(B8)>=25,MONTH(B8),MONTH(B8)))&"/"&YEAR(B8),10&"/"&0&(IF(DAY(B8)>=25,MONTH(B8)+1,MONTH(B8)))&"/"&YEAR(B8))

Decio
 
Last edited:
Seems that Deciog's formula returns errors at a given point. Perhaps try

[F2]=IF(AND(DAY(B8)>10,DAY(B8)<=25), DATE(YEAR(B8),MONTH(B8),25),
IF(DAY(B8<=10 , DATE(YEAR(B8),MONTH(B8),10),
DATE(YEAR(B8),MONTH(B8)+1,10 ) ) )
 

Attachments

  • dates_10th_25th of the month.xlsx
    39.6 KB · Views: 15
Little different approach instead of IF.

=CHOOSE(MATCH(DAY(B361),{1,11,26},1),"10-"&TEXT(EOMONTH(B361,0),"MMM-YY"),"25-"&TEXT(EOMONTH(B361,0),"MMM-YY"),"10-"&TEXT(EOMONTH(B361,1),"MMM-YY"))+0
 
Hi,
If i am not mistaken, this should work perfectly as well as some formulas above: (enter in cell F28)

=IF(DAY(E28)>25;DATE(YEAR(E28);MONTH(E28)+1;10);IF(DAY(E28)<11;DATE(YEAR(E28);MONTH(E28);10);DATE(YEAR(E28);MONTH(E28);25)))

If so, please give us feedback

Bye
 
@GraH - Guido formulas worked . Thanks
@albanvanrijsewijk formula showed some errors. But Thanks.

Now I want to challenge the formula further.

Step 1 ( Thanks to everyone, already done)

Invoice Due Date: 11th-25th, pay on same month 25th
Invoice Due Date 26th to Following 10th, pay following month 10th.

Step 2 challenge.

For payment date 25th of the month, I want to be able to pay invoices Due from 11th - 25th of same months + X days ( eg 5 days)
eg On March 25th, company can pay invoices due from Mar 11th to 30th Mar ( 25th + 5 days)

For payment date 10th of April, invoices from 31st Mar to 10th of April + 5 days, ie 15th of Apr will be paid.

WIll this be too challenging?
 
Copied in F8, this formula works perfectly for step 1:
=IF(DAY(E8)>25,DATE(YEAR(E8),MONTH(E8)+1,10),IF(DAY(E8)<11,DATE(YEAR(E8),MONTH(E8),10),DATE(YEAR(E8),MONTH(E8),25)))
Just changed the ";" in "," (as in my excel version, i need to use ",") and realized that the sheet started at row 8 instead of 28... Sorry for that!

Then for step 2, you can use the exact same formula but change the conditions. For instance, if you want to be able to add 5 days, then enter 5 in cell F1 and change the formula in cell F8 for :
=IF(DAY(E8)>25+$F$1,DATE(YEAR(E8),MONTH(E8)+1,10),IF(DAY(E8)<11+$F$1,DATE(YEAR(E8),MONTH(E8),10),DATE(YEAR(E8),MONTH(E8),25)))

Otherwise, I maybe didn't understand what you want in step 2. If so, please upload in the sample file manually what do you want to achieve.

Thanks
 

Attachments

  • Book2 (1).xlsx
    29.8 KB · Views: 2
Hi @albanvanrijsewijk

Thanks for your help. Everything works as per logic but for Due date Mar 1st, 2020, payment date should return Feb 25th ( Feb 25th + 5 days = can cover till Mar 1st ) but maybe because Feb is a irregular month with only 29 days, the result shows Mar 10th 2020. I think this is the only wrong result. But not a bad result! Thanks
 

Attachments

  • Book2 (2).xlsx
    30.7 KB · Views: 8
Hi @powerpivot beginner

It's your logic which is wrong... You cover twice the same period of time. If X=5, then follow the logic below:

Month 1:
if payment date is between 1 to 10+5 -> Due date 10th of month 1
if payment date is between 11 to 25+5 -> Due date 25th of month 1

You can see here that 11,12,13,14,15 are in the two situations, no matter the software that you use (excel or whatever), it's impossible to make it work. (Same issue with 26,27,28,29,30 at the end of the month)

Correct me if I'm wrong :)

ps: In my formula, the result if the due date will be 10th or 25th depends of the order of the If test.
 
Thanks for comments . Due date will always be depended on payment terms . Actual payment date is on the 10th and 25th if each month . Is it possible to have a formula

1) for due date 1st to 10th + x (eg 5 )days , be paid on the 10th , and then for those due after 10th + x days , ie 16th to 25th + x (5 days) to be paid on 25th ... and so on .
 
Hi to all!

@powerpivot beginner... check file with this formula:

=EDATE(E8-F$1-DAY(E8-F$1)+F$2+(F$3-F$2)*(ABS(DAY(E8-F$1)-(1+F$3+F$2)/2)<(F$3-F$2)/2),--(DAY(E8-F$1)>F$3))

If:
[F1] : X (days)
[F2] : 10 (first day payment)
[F3] : 25 (second day payment)

Blessings!
 

Attachments

  • CalculatePayment.xlsx
    29.5 KB · Views: 17
Hi All Experts

Previously @ john jairo was able to help me solve the below when I want to pay twice a month with the below formula.

=EDATE(E8-F$1-DAY(E8-F$1)+F$2+(F$3-F$2)*(ABS(DAY(E8-F$1)-(1+F$3+F$2)/2)<(F$3-F$2)/2),--(DAY(E8-F$1)>F$3))
[F1] : X (days)
[F2] : 10 (first day payment)
[F3] : 25 (second day payment)


Now if I want to add another payment date to each month, how should I change the formula?

[F1] : X (days) eg. 9
[F2] : 5 (first day payment) ( Invoice due from 5th to 14th will be paid on 5th
[F3] : 15 (second day payment) (Invoice due from 15th to 24th will be paid on 15th)
[F4] : 25 (third payment date payment) ( Invoice due from 25th to following month 4th, will be paid on 25th)
 

Attachments

  • CalculatePayment.xlsx
    29.5 KB · Views: 7
Hi, again!

Last time you didn't comment on whether the formula I proposed worked for you (only until you asked this new question). Next time come back to comment if it worked.

Check this formula:
[F1] : X (days) eg. 9
[F2] : 5 (first day payment) ( Invoice due from 5th to 14th will be paid on 5th
[F3] : 15 (second day payment) (Invoice due from 15th to 24th will be paid on 15th)
[F4] : 25 (third payment date payment) ( Invoice due from 25th to following month 4th, will be paid on 25th)

=LOOKUP(E8-F$1,E8-F$1-DAY(E8-F$1)+CHOOSE({1;2;3;4},1,F$2,F$3,F$4),EDATE(E8-F$1-DAY(E8-F$1)+CHOOSE({1;2;3;4},F$4,F$2,F$3,F$4),-(DAY(E8-F$1)<F$2)))

Blessings!
 

Attachments

  • CalculatePayment (1).xlsx
    38.7 KB · Views: 10
@John Jairo V

My bad for not making feedback and thanking you for the last solution you provided on 2/26. That worked well. Thanks.
Regarding the latest solutions provided on 4/10, the formula did not work perfectly when I wanted to have 3 payment dates in a month.
Some minor adjustment is required so your help is appreciated.

Due date on 18th is supposed to be paid on 15th but formula created results where payment date is set as 5th of the month.
ie Payment date on 5th should only capture payment date + X days so only invoices due from 5th to 14th should be paid on 5th.
Invoices due on 15th + x days( 9) = 24th should be paid on the 15th and Invoices due on 25th + X days(9) will be paid on the 25th


[F1] : X (days) eg. 9
[F2] : 5 (first day payment) ( Invoice due from 5th to 14th will be paid on 5th
[F3] : 15 (second day payment) (Invoice due from 15th to 24th will be paid on 15th)
[F4] : 25 (third payment date payment) ( Invoice due from 25th to following month 4th, will be paid on 25th)
 

Attachments

  • CalculatePayment (1).xlsx
    38.9 KB · Views: 6
Hi @John Jairo V,

Thanks for your comment but X is a variation factor so X cannot be 0.
eg when X is 9, Due Date + 9 days . I want to be able to see the impact of changing X.

F1] : X (days) eg. 9
[F2] : 5 (first day payment) ( Invoice due from 5th + X(9) = 14th will be paid on 5th
[F3] : 15 (second day payment) (Invoice due from 15th + X(9) = 24th will be paid on 15th)
[F4] : 25 (third payment date payment) ( Invoice due from 25th + X(9) = following month 4th, will be paid on 25th.
 

Attachments

  • CalculatePayment (1) (1).xlsx
    38.9 KB · Views: 5
Hi, again!

Try this new approach:
=EDATE(E8-DAY(E8)+IFERROR(LOOKUP(DAY(E8),F$2:F$4),F$4),-(DAY(E8)<F$2))

Check file. Blessings!
 

Attachments

  • CalculatePayment.xlsx
    30.4 KB · Views: 12
You must log in or register to reply here.
Back
Top