Formula for Payment Terms "Net 10 EOM"

Jerry Alexander

I'm trying to write a formula to calculate a payment due date using the payment terms "Net 10 EOM". Meaning if invoice is submitted by the end of the month, its due by the 10th of the following month.

Genevieve P.

Hi @Jerry Alexander

You can use the DATE function to identify a day, month, and year to display in a date column. Then for the month portion, you can use the MONTH function to recognize the month in the Date Submitted date column and add 1 to it, like so:

=DATE(YEAR([Date Submitted]@row), MONTH([Date Submitted]@row) + 1, 10)

However, you will get into an issue if the month is December (since there is no 13th month). To mitigate this, you can add an IF statement that says if the Month in the Date Submitted column is December, add one Year and have the month be January. Otherwise, do the formula above:

=IF(MONTH([Date Submitted]@row) = 12, DATE(YEAR([Date Submitted]@row) + 1, 1, 10), DATE(YEAR([Date Submitted]@row), MONTH([Date Submitted]@row) + 1, 10))

Let me know if this works for you, and if I've understood what it is you're looking to do!

Cheers,

Genevieve

Genevieve P.

Hi @Jerry Alexander

Perfect, that's exactly what I needed to know.

Week Formula to return FRIDAY:

This will check what day of the week the Actual Completion date is, then adjust how many days to add on depending on that weekday, to ensure it always returns next Friday.

=IF([Payment Terms]@row = "Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 12, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 11, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 10, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 9, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 8, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 7, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 6))))))))

Bi-Weekly Formula to return FRIDAY 2 weeks from submission:

=IF([Payment Terms]@row = "Bi-Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 19, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 18, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 17, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 16, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 15, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 14, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 13))))))))

So now that we have each individual statement, we can simply add them all together:

FULL FORMULA:

=IF([Payment Terms]@row = "Net 30", [Actual Completion]@row + 30, IF([Payment Terms]@row = "Due on Receipt", [Actual Completion]@row, IF([Payment Terms]@row = "Net 10 EOM", IF(MONTH([Actual Completion]@row) = 12, DATE(YEAR([Actual Completion]@row) + 1, 1, 10), DATE(YEAR([Actual Completion]@row), MONTH([Actual Completion]@row) + 1, 10)), IF([Payment Terms]@row = "Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 12, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 11, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 10, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 9, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 8, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 7, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 6))))))), IF([Payment Terms]@row = "Bi-Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 19, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 18, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 17, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 16, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 15, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 14, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 13))))))))))))

I've bolded the start of each separate statement so you can see how it all fits together. Let me know if this returns the desired dates for each of your possible options!

Cheers,

Genevieve

Genevieve P.

Hi @Jerry Alexander

You can use the DATE function to identify a day, month, and year to display in a date column. Then for the month portion, you can use the MONTH function to recognize the month in the Date Submitted date column and add 1 to it, like so:

=DATE(YEAR([Date Submitted]@row), MONTH([Date Submitted]@row) + 1, 10)

However, you will get into an issue if the month is December (since there is no 13th month). To mitigate this, you can add an IF statement that says if the Month in the Date Submitted column is December, add one Year and have the month be January. Otherwise, do the formula above:

=IF(MONTH([Date Submitted]@row) = 12, DATE(YEAR([Date Submitted]@row) + 1, 1, 10), DATE(YEAR([Date Submitted]@row), MONTH([Date Submitted]@row) + 1, 10))

Let me know if this works for you, and if I've understood what it is you're looking to do!

Cheers,

Genevieve

Jerry Alexander

Yes, this works perfectly! Thank you! Now can I add one level of complexity? Since every row will not be Net 10 EOM, I will need to add a "Payment Terms" Column, and have Weekly, Net 30, Net 10 EOM, etc.

The weekly and net 30 I was just going to =30 days (respectively) to the "Actual Completion". But I assume I would need to add all of the mechanics for each payment term in the same formula?? Talk about overwhelming.... geez. Any help I would be eternally grateful. :)

Weekly

Bi Weekly

Net 10 EOM

Net 30

Genevieve P.

Hi @Jerry Alexander

I'm glad this works for you!

Do you have somewhere else in your sheet which indicates what the cadence should be? For example, is there a drop-down column which says "Weekly" or "Net 10 EOM"?

If so, we can use this as a reference for the formula to return a specific date based on what's said there.

However for your weekly and bi-weekly cadences, are you wanting this formula to change each week to show a different date, or is this a one-off week as in +7 days?

We may be able to use a "Record a Date" automation depending on how you're using this sheet to use as a reference point for when something specific happens. It would be helpful to see a screen capture of your sheet, but please block out any sensitive data.

Thanks,

Genevieve

Jerry Alexander

Here is a screenshot of the dropdown. The idea is the date would change based on the selected payment terms. As far as the weekly and biweekly, if possible, Id like it to calculate a due date as follows.

Weekly: If the actual completion date is Sunday - Saturday, the due date would be the following Friday.

Bi-Weekly would be the same, only adding a week in, so it would be due the second Friday.

Genevieve P.

Hi @Jerry Alexander

Great, thank you for confirming!

Essentially we need to have one logic statement per drop-down selection. It sounds like the column I had labeled as "Date Submitted" is "Actual Completion" in your sheet, so I've adjusted the formulas below.

We already have one built out, the "Net 10 EOM" formula, so all we have to do is add that into an IF statement like so:

=IF([Payment Terms]@row = "Net 10 EOM", IF(MONTH([Actual Completion]@row) = 12, DATE(YEAR([Actual Completion]@row) + 1, 1, 10), DATE(YEAR([Actual Completion]@row), MONTH([Actual Completion]@row) + 1, 10)))

Does that make sense? So now let's figure out each of your other options. Then once we know each formula we can change it to be one big Nested If.

Net 30

=IF([Payment Terms]@row = "Net 30", [Actual Completion]@row + 30)

Due on Receipt

=IF([Payment Terms]@row = "Due on Receipt", [Actual Completion]@row)

Now for Weekly and Bi-Weekly, can you clarify for each potential day which Friday you want to appear?

You say for "Sunday - Saturday" you want the following Friday... but what if the date is Thursday? Do you want the "weekly" to show that week's Friday or the following Friday?

Can you confirm if I've understood the following for Weekly:

Sunday - 5 days from now

Monday - 4 days from now

Tuesday - 3 days from now

Wednesday - 2 days from now

Thursday - tomorrow

Friday - 7 days, next Friday

Saturday - 6 days from now

Then can you confirm if I've understood the following for Bi-weekly:

Sunday - 12 days from now

Monday - 11 days from now

Tuesday - 10 days from now

Wednesday - 9 days from now

Thursday - 8 days from now

Friday - 14 days

Saturday - 13 days from now

Jerry Alexander

All looks good other than the weekly and biweekly. If actual completion date is anytime during a week (Sunday-Saturday) it would be due the following week.

Sunday - 12 days from now

Monday - 11 days from now

Tuesday - 10 days from now

Wednesday - 09 days from now

Thursday - 08 days from now

Friday - 07 days from now

Saturday - 06 days from now

Biweekly would be the same, just adding 7 days to each.

Genevieve P.

Hi @Jerry Alexander

Perfect, that's exactly what I needed to know.

Week Formula to return FRIDAY:

This will check what day of the week the Actual Completion date is, then adjust how many days to add on depending on that weekday, to ensure it always returns next Friday.

=IF([Payment Terms]@row = "Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 12, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 11, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 10, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 9, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 8, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 7, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 6))))))))

Bi-Weekly Formula to return FRIDAY 2 weeks from submission:

=IF([Payment Terms]@row = "Bi-Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 19, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 18, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 17, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 16, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 15, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 14, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 13))))))))

So now that we have each individual statement, we can simply add them all together:

FULL FORMULA:

=IF([Payment Terms]@row = "Net 30", [Actual Completion]@row + 30, IF([Payment Terms]@row = "Due on Receipt", [Actual Completion]@row, IF([Payment Terms]@row = "Net 10 EOM", IF(MONTH([Actual Completion]@row) = 12, DATE(YEAR([Actual Completion]@row) + 1, 1, 10), DATE(YEAR([Actual Completion]@row), MONTH([Actual Completion]@row) + 1, 10)), IF([Payment Terms]@row = "Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 12, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 11, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 10, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 9, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 8, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 7, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 6))))))), IF([Payment Terms]@row = "Bi-Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 19, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 18, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 17, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 16, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 15, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 14, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 13))))))))))))

I've bolded the start of each separate statement so you can see how it all fits together. Let me know if this returns the desired dates for each of your possible options!

Cheers,

Genevieve

Jerry Alexander

GENEVIEVE!! you are an amazing human being. Thank you it works perfectly!

Genevieve P.

Haha, no problem at all! I'm glad I could help. 🙂

doram_coastalcraft

@Genevieve P.

Id love to be able to implement this solution to an invoicing sheet problem we are having.

Every time I add the formula into the 'Due Date' Column though i get the following:

When 'Net 30' it adds a '30' onto the end of my due date cell as in '03/01/2230'

When 'Weekly' it says '#INVALID DATA TYPE', same with 'Bi-Weekly' and 'Net 10 EOM'.

Help appreciated as this is the exact solution we need.

Regards,

Genevieve P.

Hi @doram_coastalcraft

What type of column is the one that contains your date? '03/01/22'

The formula would add 30 to the end to make '03/01/2230' if the cell is seen as text and not a date value. Can you check to see if your date is in a Date Type of column? It would need to be a Date in order to add 30 days instead of the number 30.

doram_coastalcraft

@Genevieve P.

Thank you! I suspected it was something like that.

Perhaps it was just to late in the work day when i asked that :) it all works perfectly, thank you so much!

doram_coastalcraft

@Genevieve P.

How would i add 'Net 15 EOM' to the entire formula?

Thanks so much!!

Genevieve P.

Hi @doram_coastalcraft

You can use the same "Net 10 EOM" statement for "Net 15" but change out the 10 in each DATE function to say 15 instead:

IF([Payment Terms]@row = "Net 15 EOM", IF(MONTH([Actual Completion]@row) = 12, DATE(YEAR([Actual Completion]@row) + 1, 1, 15), DATE(YEAR([Actual Completion]@row), MONTH([Actual Completion]@row) + 1, 15))

Ex:

=IF([Payment Terms]@row = "Net 30", [Actual Completion]@row + 30, IF([Payment Terms]@row = "Due on Receipt", [Actual Completion]@row, IF([Payment Terms]@row = "Net 10 EOM", IF(MONTH([Actual Completion]@row) = 12, DATE(YEAR([Actual Completion]@row) + 1, 1, 10), DATE(YEAR([Actual Completion]@row), MONTH([Actual Completion]@row) + 1, 10)), IF([Payment Terms]@row = "Net 15 EOM", IF(MONTH([Actual Completion]@row) = 12, DATE(YEAR([Actual Completion]@row) + 1, 1, 15), DATE(YEAR([Actual Completion]@row), MONTH([Actual Completion]@row) + 1, 15)), IF([Payment Terms]@row = "Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 12, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 11, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 10, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 9, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 8, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 7, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 6))))))), IF([Payment Terms]@row = "Bi-Weekly", IF(WEEKDAY([Actual Completion]@row) = 1, [Actual Completion]@row + 19, IF(WEEKDAY([Actual Completion]@row) = 2, [Actual Completion]@row + 18, IF(WEEKDAY([Actual Completion]@row) = 3, [Actual Completion]@row + 17, IF(WEEKDAY([Actual Completion]@row) = 4, [Actual Completion]@row + 16, IF(WEEKDAY([Actual Completion]@row) = 5, [Actual Completion]@row + 15, IF(WEEKDAY([Actual Completion]@row) = 6, [Actual Completion]@row + 14, IF(WEEKDAY([Actual Completion]@row) = 7, [Actual Completion]@row + 13)))))))))))))

ksieverding

@Genevieve P.
How would you write the formula for "NET120 EOM"? I tried using the formulas above by changing the 15 to 120 but I receive #INVAILD VALUE.