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

Maricarmen Vargas

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")

😀

Paul Newcome

Ok. So the last day of the current month can be found by subtracting 1 from the first of next month.

=IFFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 1, 1), DATE(YEAR([Submit Date]@row) + 1, 1, 1)) - 1

The last day of next month is only slightly trickier. In the above, we were able to account for December going into the next year using the IFERROR to just add 1 to the year and make the month number 1. To go out to the first day three months from now (to then subtract one from to get the last day of two months out), we will need to add 1 to the year and then we will actually subtract 10 from the current month which would give us January for November dates and February for December dates (after adding 1 to the year) in our IFERROR portion.

=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

NOw that we have our two dates, we can drop them into the two output sections of our IF statement.

=IF(DAY([Submit Date]@row) <= 20, last day of current month, last day of next month)

=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)

Paul Newcome

That's my fault. I fat fingered an extra "F" into the first IFERROR. I removed the extra letter on your published sheet, and it is giving a date.

Here it is for the thread (bold portion is where the typo was fixed)...

=IF(Type@row = "Supplier", IF(DAY([Submit Date]@row) <= 20, IFERROR(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)))

Paul Newcome

Ok. So the last day of the current month can be found by subtracting 1 from the first of next month.

=IFFERROR(DATE(YEAR([Submit Date]@row), MONTH([Submit Date]@row) + 1, 1), DATE(YEAR([Submit Date]@row) + 1, 1, 1)) - 1

The last day of next month is only slightly trickier. In the above, we were able to account for December going into the next year using the IFERROR to just add 1 to the year and make the month number 1. To go out to the first day three months from now (to then subtract one from to get the last day of two months out), we will need to add 1 to the year and then we will actually subtract 10 from the current month which would give us January for November dates and February for December dates (after adding 1 to the year) in our IFERROR portion.

=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

NOw that we have our two dates, we can drop them into the two output sections of our IF statement.

=IF(DAY([Submit Date]@row) <= 20, last day of current month, last day of next month)

=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)

Maricarmen Vargas

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!

Paul Newcome

Happy to help! 👍️

Maricarmen Vargas

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?

Paul Newcome

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.

Maricarmen Vargas

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).

Paul Newcome

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

Maricarmen Vargas

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.

Paul Newcome

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?

Maricarmen Vargas

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

Paul Newcome

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?

Maricarmen Vargas

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

Paul Newcome

That's my fault. I fat fingered an extra "F" into the first IFERROR. I removed the extra letter on your published sheet, and it is giving a date.

Here it is for the thread (bold portion is where the typo was fixed)...

=IF(Type@row = "Supplier", IF(DAY([Submit Date]@row) <= 20, IFERROR(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)))

Maricarmen Vargas

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.

Paul Newcome

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.