Need to add 6 months and 3 months to a date

Vanessa K

I have a date that other will populate, but I need to have two columns that calculated 3 months out from the date and 6 months out.

I have tried several formulas on the forum and nothing is working. I am completely stumped. If I was in excel this would have been done in a minute.

Paul Newcome

@Deanna DeCoursey Ok. Here we go. I think we may have it here...

Helper (text/number):

=ABS(MONTH([Previous Due Date]@row) - MONTH(IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row))))))

New Date (date):

=IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IF(OR(AND(Frequency@row = "Monthly", Helper@row <> 1, Helper@row <> 11), AND(Frequency@row = "Quarterly", Helper@row <> 9, Helper@row <> 3), AND(Frequency@row = "Semi-Annually (6 months)", Helper@row <> 6)), DATE(YEAR(IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))), MONTH(IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))), 1) - 1, IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))))

Paul Newcome

Try these...

3 months:

=IFERROR(DATE(YEAR([Inspection Date]@row), MONTH([Inspection Date]@row) + 3, DAY([Inspection Date]@row)), DATE(YEAR([Inspection Date]@row) + 1, MONTH([Inspection Date]@row) - 9, DAY([Inspection Date]@row)))

6 months:

=IFERROR(DATE(YEAR([Inspection Date]@row), MONTH([Inspection Date]@row) + 6, DAY([Inspection Date]@row)), DATE(YEAR([Inspection Date]@row) + 1, MONTH([Inspection Date]@row) - 6, DAY([Inspection Date]@row)))

Vanessa K

Hi Paul,

I am still running into errors. See screenshot.

I used the formula you provided but changed the header/rom to be 'New Hire Date of Hire'. What am I doing wrong?

=IFERROR(DATE(YEAR(New Hire Date of Hire]@row), MONTH([New Hire Date of Hire]@row) + 3, DAY([New Hire Date of Hire]@row)), DATE(YEAR([New Hire Date of Hire]@row) + 1, MONTH([New Hire Date of Hire]@row) - 9, DAY([New Hire Date of Hire]@row)))

Paul Newcome

You are missing the opening square bracket before the first cell reference.

Vanessa K

Still getting an error, now an '#invalid column value'

Paul Newcome

Did you double and triple check that the column you are putting the formula in is in fact set as a date type column?

Vanessa K

@Paul Newcome

I do have the New hire date set up as a date format

Paul Newcome

What about the column you have the formula in? The First Payout Date column?

Vanessa K

@Paul Newcome

Many thanks! If you give quick video demos on a video platform I'd definitely be interested. The videos out there by Smartsheet are over an hour long. I don't need an hour long explanation of SSt sum or vlook up or function vs formula. I just need to understand the basic syntax and start building on it.

Vanessa K

Hi @Paul Newcome

I noticed the formal is not quite doing 3 or 6 months, it seems to be adding a couple of days

versus when I do a formula in excel…

Any suggestions or adaptations?

Paul Newcome

That is because of the differences in how Excel works vs how Smartsheet works.

Using your 11/30 entry as the example...

Excel is registering that the 30th is the last day of Nov, so it is going out to the last day of Feb.

Smartsheet is registering that there are only 28 days in Feb which is 2 days less than the 30th. Smartsheet is outputting what could theoretically be considered the "30th of Feb".

I would be willing to bet that if you kick the date back to 30 Nov 2019, it would output 1 Mar since in 2020 there were 29 days in Feb (which is only 1 less than the 30th).

Vanessa K

@Paul Newcome Is there a way to have this be as accurate as Excel? This is sheet that will trigger a payout in a SOX environment. So, accuracy is important.

Thanks!

Paul Newcome

If the day number can vary then it would be extremely tricky to do it in a single formula. If you are only using something like the 15th and the last day of the month or the 1st and the 15th or something like that, then it would be much easier, but it doesn't seem like that is the case here.

I am going to have to keep thinking on how to make this one a little more accurate. We may have to build out a reference table, but I am not quite sure just yet.

Regarding the videos... Would you mind reaching out to me on LinkedIn?

www.linkedin.com/in/paul-newcome-8925aa188

Deanna DeCoursey

Hi @Paul Newcome ,

I am currently trying to create a similar formula and have come across the same issue. Did you ever have a resolution to this?

Thanks!

Paul Newcome

@Deanna DeCoursey What exactly are you wanting to accomplish?

Deanna DeCoursey

@Paul Newcome I am currently setting up a sheet where I need to make a formula to anticipate an upcoming due date based on frequency (monthly, quarterly, etc.), but as Vanessa mentioned back in 2021, the date formula is set up in a way where date+3 months sometimes ends up being in the next month, since the number of days do not match up. Example: 31Jan2023 +1 month would be 02Mar2023 instead of 28Feb2023. I can't figure out a workaround for this. I initially set up a formula that makes it month +3 and day -3 and then a different assisting formula in a new column to make the date the end of the month. but now we are having due dates in the middle of the month, so this no longer works. Back in 2021 you and Vanessa were discussing this and I was wondering if you ever came across a resolution?