# If 'Submit Date' is before the 20th of current month, Set 'Payment Due' to last day of month

Options

Hello!

Creating a sheet to help with Accounts Payable process to external providers.

External providers will submit their invoice and payment details into a form that feeds into this sheet.

Would like the formula to determine when that invoice is set to be paid, depending on the submission date.

## The formula in words..

If Submit Date is on or before the 20th of current month, Set Payment Due to last day of current month.

But if Submit Date is after the 20th current month, set Payment Due to the last day of next month.

(Submit Date is the auto generated field of "Create Date")

😀

Tags:

«1

• Options

Wow... I didn't even think about December and the year issue, or that the 1st day of next month was going to be so complicated..

Spent a lot of time understanding the formula, and think I just leveled up after getting it!

🤩+1

Thanks!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Options

Oh no, I'm about to complicate it further...

Mostly understood how to use the DATE function year, month and day, but now need weeks!! And not sure how to work with WEEKDAY, or if this is even the one to use.

## If Type@row = "Supplier"

=IF(DAY([Submit Date]@row) <= 20, IFFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 1, 1), DATE(YEAR([Submit Date]@row) + 1, 1, 1)) - 1, IFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 2, 1), DATE(YEAR([Submit Date]@row) + 1, MONTH([Submit Date]@row) - 10, 1)) - 1)

## BUT IF Type@row = "Professional Services"

Payment Due is due biweekly on Fridays, so if

Submit Date is Week X of year, payment due is on Friday, X week of year.

Might need to make a reference sheet for this? Any recommendation? Or can this be solved with another tricky formula?

• ✭✭✭✭✭✭
edited 03/18/20
Options

So let's just say I make a payment today on Wednesday, 18 March. When is my next payment due? Are there further biweekly payments, or is it just once within a biweekly schedule?

EDIT:

Additionally... What if my payment is next Wednesday, 23 March? Is it on the same biweekly schedule as this weeks payment as in payments will always be due on the even numbered weeks, or is it always two weeks from the Friday of payment, or...?

Let's get the logic figured out before we stress over the complexity. It may or may not really be all that bad.

• Options

Sounds good, so here is the schedule as example, see image for reference

I'm going to charge for the work I did since Friday March 6th to Thursday March 19th.

I must be submit that invoice before Tuesday March 25th. I will receive Payment (Payment Due) on Friday 27th.

Next invoice should be received before april 7th, and Payment will be due on Friday 10th

The following invoice should be received before April 21st, and Payment will be due Friday 24th

So we are paying every Friday Biweekly for the work done from the Friday (2 weeks before current week) till Thursday (previous week).

• ✭✭✭✭✭✭
Options

And you just need the date for Payment Due which is [Work End] plus 8 days?

• Options

Hmm yes, but I don't have [Work End] as a property anywhere but if it helps, maybe I could add that column. But that column might also need a formula to set Work End every other Thursday..

Or I could just make it a field that the person that submits their invoice via form, always states to what work days it belongs to.

• ✭✭✭✭✭✭
Options

How about this... No matter who it is... Will Payment Due ALWAYS be the highlighted dates in your screenshot and continuing out bi-weekly from there, or is that based off of a moving variable?

Would [Work Start] + 21 work, or is there a variable I am missing?

• Options

Yes, Payment Due will always be those highlighted days cointinuing out bi-weekly, no moving variable!

Yes, I can add Work Start as a column and +21 works as well as Work End +8

• ✭✭✭✭✭✭
Options

Adding the columns may or may not be easier because we will still need to establish those dates to add 21 or 8 to so that the payment due is calculated correctly.

If you are going to add a column, you can just enter the first Friday that payment is due into the top row. Then in row two you would have

=[Helper Column]1 + 14

and dragfill that down. That gives you your every other Friday.

Then we use a MIN/COLLECT to pull the closest date that is also past the date that is in your already established column.

## If Type@row = "Supplier"

=IF(DAY([Submit Date]@row) <= 20, IFFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 1, 1), DATE(YEAR([Submit Date]@row) + 1, 1, 1)) - 1, IFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 2, 1), DATE(YEAR([Submit Date]@row) + 1, MONTH([Submit Date]@row) - 10, 1)) - 1)

## BUT IF Type@row = "Professional Services"

=MIN(COLLECT([Helper Column]:[Helper Column], [Helper Column]:[Helper Column], @cell >= [Submit Date]@row + 7))

Combined into an IF based on Type@row:

=IF(Type@row = "Supplier", supplier formula, professional services formula)

=IF(Type@row = "Supplier", IF(DAY([Submit Date]@row) <= 20, IFFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 1, 1), DATE(YEAR([Submit Date]@row) + 1, 1, 1)) - 1, IFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 2, 1), DATE(YEAR([Submit Date]@row) + 1, MONTH([Submit Date]@row) - 10, 1)) - 1), professional services formula)

FINAL:

=IF(Type@row = "Supplier", IF(DAY([Submit Date]@row) <= 20, IFFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 1, 1), DATE(YEAR([Submit Date]@row) + 1, 1, 1)) - 1, IFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 2, 1), DATE(YEAR([Submit Date]@row) + 1, MONTH([Submit Date]@row) - 10, 1)) - 1), MIN(COLLECT([Helper Column]:[Helper Column], [Helper Column]:[Helper Column], @cell >= [Submit Date]@row + 7)))

Does that seem like it might work for you?

• edited 03/19/20
Options

I think so, was playing around with it trying to figure it out, but still getting #unparseable.

Published the testing sheet here

https://app.smartsheet.com/b/publish?EQBCT=d2f6a7c24b7b493284c377f9286b5c7b

Edit: Just googled what Unparseable meant.. didn't know it just means a mistake in spelling or operators. So found the mistake! Testing for other dates now

• Options

Yea no problem! Noticed after verifying what the error meant 😅

This works great, just made an edit at the end to +3 instead of +7 for more wiggle time to submit invoice.

Thanks again for this, really appreciate the explanations as well.

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

I actually used +7 because if I submit an invoice on 23 March, then the next date on your table is going to be that Friday the 37th of March. +7 ensures that it will skip over that but not skip too far ahead as well. Looking again... I think +8 is actually going to be more accurate than +7.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!