IF statement for date calculation

Question

I have been trying 50 different ways to get sharepoint to display a date based on other columns. I tried breaking it down into smaller parts figuring that my IF formulas were too long or sharepoint doesn't like using them - but the more likely thing is that i am messing up somewhere and i just don't see it.

Here are my columns that are involved and their formula

[ACA True] -> if column C = this or that then TRUE, FALSE

[Class End Date]- date column

[Date with Nesting] =IF([ACA True?]=TRUE,[Class End Date]+7,[Class End Date])

[Day of month for nesting] =DAY([Date with Nesting])

[Is Day of nesting 10 or less?] =IF([Day of month for nesting]<=10,"TRUE","FALSE")

[Beginning of Nesting Month] =DATE(YEAR([Date with Nesting]),MONTH([Date with Nesting]),1)

[end of nesting month] =DATE(YEAR([Date with Nesting]),MONTH([Date with Nesting])+1,1)-1+1

[30 Day Ramp] =IF([Is Day of nesting 10 or less?]=TRUE,[Beginning of Nesting Month],[end of nesting month])

Here is what is supposed to happen if the date of nesting is equal to or less than [Day of month for nesting] then set the 30 day ramp to the [Beginning of Nesting Month] else set it to [end of nesting month]

My eyes are crossed looking at sharepoint right now...I could use another set of eyes! thank you! so: if the nesting date is 9/5/2018 the 30 day ramp should be 9/1/2018 because the day of the nesting month is equal to or less than 10 if the nesting date is 9/18/2018 the 30 day ramp should be 10/1/2018 because the day of the nesting month of September is greater than 10

When I set [30 Day Ramp] =IF([Is Day of nesting 10 or less?]=TRUE,[Beginning of Nesting Month],[end of nesting month]) it always displays the END OF NESTING MONTH field

Answer 1

This is an interesting challenge, I think you were on the right track but maybe missing something in the syntax; there's a good amount of brackets to use.

I arrived to what I think is the result you wanted through this formula:

=IF((DAY([Date with nesting]))>=10;(DATE(YEAR([Date with nesting]);MONTH([Date with nesting])+1;1));(DATE(YEAR([Date with nesting]);MONTH([Date with nesting]);1)))

I'm not sure if it needs much explanation as it's virtually the same formula you were attempting, but if there's anything more to describe please let me know.

Mind that in my case I'm using a semicolon as separator, whereas in the documentation it recommends the use of a comma -it could be a language difference?-, I'm working with SharePoint 2013.

The result is: