20th of the following month formula

I have a situation where a sale rep gets paid commission 20th of the following month. I need a formula to look at the "Invoiced Date" say 16/02/17 and automatically enter 20/03/17 into the "Commission Date" .

Then i need a cell (highlighted in blue in sheet) to calculate the commission when in rolls over into that month. Using the same type of formula that calculates (Peter Smith) sales he has done. I’m guessing its a similar formula to the one in the sheet ive attached ? MONTH(@cell) = MONTH(TODAY()), [Sold Date]2, YEAR(@cell) = YEAR(TODAY()))

Find more posts tagged with

Comments

Shaine Greenwood

Hi Graeme,

I came up with this when testing in my sheet:

=DATE(YEAR([End Date]1), MONTH([End Date]1 + 1), 20)

This formula MUST be placed in a Date type column, and CAN'T be used in Date columns being used with the Dependencies functionality in Smartsheet. Change the cell references to match what's in your sheet.

The DATE function generates a date value.

Heath Sanders

Hi Shaine,

I also needs this formula in my sheet with something extra..

=DATE(YEAR([Invoiced Date]2), MONTH([Invoiced Date]2 + 1), 20)

It returns 20/02/17 if you enter the last day of the previous month in this case 31/01/17, if you enter 01/01/17 in this example it returns 20/01/17. I need 20/02/17 if any day in January 17 is selected, 20/03/17 if any day in February 17 is selected and so on. All commissions are paid 20th of the follow month. Thanks Again

Heath Sanders

Hi Shaine,

Shared sheet, you will see where im heading with it. Appreacite the help my friend.

Shaine Greenwood

Looks like I just misplaced my end paren on the month.

I've tested this out:

=DATE(YEAR(Finish1), MONTH(Finish1) + 1, 20)

It returns 2/20/17 based on another date cell with 1/1/17 entered.

Heath Sanders

Hi Shaine,

You will see in the "Comms Due Date" what’s happening. As i mentioned i need every denomination 1-31 of that month or any other month (Feb has 28 days of course, same rule applys) to return 20th of the following month. We are missing something?

IFERROR need in there also.

Thanks Shaine

Shaine Greenwood

Hi Heath,

From looking at your sheet, you didn't adjust the syntax according to my previous comment. Please make this adjustment in your sheet and check the results.

I'm also noticing that you've altered the formula in a nother cell to add the number 30 to your month value, which may return unexpected results. I'd recommend sticking with the formula I provided, using the EXACT syntax in my latter comment. (Notice the paren placement on the month.) When I test this formula out in my sheet, I get 3/20/17 when the referenced date cell is 2/28/17. I get the 20th of the following month for every date I enter, and I've entered a dozen dates in my cell so far.

For syntax on using IFERROR, check out our Help Center: https://help.smartsheet.com/articles/775363-using-formulas#iferror

Heath Sanders

Hi Shaine,

I've followed your instructions so much so i copy and pasted formula into cell ( I entered all options into other rows) Still getting same answer ???

=DATE(YEAR(Finish1), MONTH(Finish1) + 1, 20) row 2 #UNPARSEABLE

=DATE(YEAR(Finish3), MONTH(Finish3) + 1, 20) row 3 #UNPARSEABLE

=DATE(YEAR(Invoiced Date6), MONTH(Invoiced Date6) + 1, 20) row 6 #UNPARSEABLE

=DATE(YEAR([Invoiced Date]5), MONTH([Invoiced Date]5 + 1), 20) row 5, Returns 20th in the same month if last day in month not entered

Shaine Greenwood

Hi Heath,

I'd recommend checking out our article on formula creation to get a grasp on how formulas work in Smartsheet. It differs a bit from excel: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

The syntax of the formulas in your comment is different from what I provided, the bottommost being the CLOSEST, but still having the end paren out of place on the MONTH function.

=DATE(YEAR([Invoiced Date]5), MONTH([Invoiced Date]5) + 1, 20)

Remember to reference your own columns as well (instead of my sheet's "finish" column).

Heath Sanders

Thanks again Shaine,

The service from You and the Smart Sheet community is impeccable. Inspite of my short commings and lack of understanding that could cause the most patient person to want to bang their head agenst a wall (Not for real of course) teaching me. I'll push forward in my understanding of formlars in Smart Sheet.

Heath