how to add 6 months in a date ?

gourav

Hi, I have a date column and I want to generate a new date by adding 6 months to the date. I have used this formula-

=DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 1, DAY([Go Live Date in V2]@row))

but its only working within the year but if the date comes under next year it gave me an error so can you help me with that thanks.

Paul Newcome

Here is what I usually go with...

=IFERROR(DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 6, DAY([Go Live Date in V2]@row)), DATE(YEAR([Go Live Date in V2]@row) + 1, MONTH([Go Live Date in V2]@row) - 6, DAY([Go Live Date in V2]@row)))

First DATE function adds 6 months. If that causes an error, then it will add 1 year and subtract 6 months.

Paul Newcome

@Cara Graf I would use another IFERROR like so:

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

Kelly Moore

Hey

I typically add a helper column for the months being added in case the number of months varies from row to row. I called this helper column [Months til Expiration]. If you don't want the helper column, you can replace this reference with the number 6 (delete all references to [Months til Expiration]@row and replace with number 6 )

Try this

=IFERROR(IF(MONTH([Go Live Date in V2]@row) + [Months til Expiration]@row > 12, DATE(YEAR[Go Live Date in V2]@row) + 1, MONTH([Go Live Date in V2]@row) + [Months til Expiration]@row - 12, DAY([Go Live Date in V2]@row)), IF(MONTH([Go Live Date in V2]@row) + [Months til Expiration]@row <= 12, DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + [Months til Expiration]@row, DAY([Go Live Date in V2]@row)))), "")

If interested, you can see an explanation here

cheers

Kelly

gourav

do I need to change something in the formula or I can just copy and cut it?

Kelly Moore

You can just paste this formula below in - I modified it to remove the reference to a helper column. If you change from 6 months you'll have to paste over the 6's with the new number.

=IFERROR(IF(MONTH([Go Live Date in V2]@row) + 6 > 12, DATE(YEAR[Go Live Date in V2]@row) + 1, MONTH([Go Live Date in V2]@row) + 6 - 12, DAY([Go Live Date in V2]@row)), IF(MONTH([Go Live Date in V2]@row) + 6 <= 12, DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 6, DAY([Go Live Date in V2]@row)))), "")

Paul Newcome

Here is what I usually go with...

=IFERROR(DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 6, DAY([Go Live Date in V2]@row)), DATE(YEAR([Go Live Date in V2]@row) + 1, MONTH([Go Live Date in V2]@row) - 6, DAY([Go Live Date in V2]@row)))

First DATE function adds 6 months. If that causes an error, then it will add 1 year and subtract 6 months.

deepx3

Here is the formula I made for adding arbitrary number of months to a date.

This formula assumes the date you want to modify is in a column called Your Date Field and the number of months you want to add is in a column called Additional Months.

You can modify for your date field, and replace the INT([Additional Months]@row) part with 6 if you want to hardcode adding 6 months every time.

=DATE(
   YEAR([Your Date Field]@row) + INT((MONTH([Your Date Field]@row) + INT([Additional Months]@row)) / 12.01)
   ,ROUND(MOD(MONTH([Your Date Field]@row) + INT([Additional Months]@row), 12.01), 0)
   ,1
)

Paul Newcome

@deepx3 Yes. There are a couple of other solutions for a separately specified number of months including those that can cover multiple years (like yours). I can't remember those exact solutions off the top of my head at the moment, but I do believe they are pretty similar to yours using ROUND/INT/MOD functions.

James Keuning

https://community.smartsheet.com/discussion/comment/285139#Comment_285139

Does this break after 23 months? I have not stepped through the logic, but based on the way you describe it, it makes sense.

James Keuning

https://community.smartsheet.com/discussion/comment/303762#Comment_303762

Is it intentional that this results in the first day of the month, and not the exact date?

Paul Newcome

@James Keuning You may want to look up EDATE here in the community. The above is written specifically for 6 months. There are a couple of solutions out here somewhere that are a fix to the lack of the EDATE function that allows you to enter a specific number of months in one column and it will automatically calculate the remaining.

It should not always output the 1st of the month, but there may be an issue if you have August 31st because six months later is February which definitely does not have 31 days in it.

James Keuning

@Paul Newcome That makes sense. Thanks for the response.

Cara Graf

@Paul Newcome Hey Paul! I've copied your suggestion above and tweaked it to fit my column names:

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

It works great, however, I'm getting the #INVALID DATA TYPE error for the rows that don't have a Course Start Date yet. I've tried inserting , " " before my last ) in the formula with no success. How can I get the empty cells to appear empty until a Course Start Date is populated?

Andrée Starå

Hi @Cara Graf

I hope you're well and safe!

Try something like this.

=IF([Course Start Date]@row = "", "", (IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]1)))))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Cara Graf I would use another IFERROR like so:

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

Cara Graf

Thanks, @Paul Newcome ! Worked perfectly!

Paul Newcome

Happy to help. 👍️