how to add 6 months in a date ?
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.
Best Answers
-
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.
-
@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))), "")
Answers
-
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
-
do I need to change something in the formula or I can just copy and cut it?
-
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)))), "")
-
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.
-
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 )
-
@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.
-
Does this break after 23 months? I have not stepped through the logic, but based on the way you describe it, it makes sense.
-
Is it intentional that this results in the first day of the month, and not the exact date?
-
@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.
-
@Paul Newcome That makes sense. Thanks for the response.
-
@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?
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
AndrΓ©e StarΓ₯ | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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))), "")
-
Thanks, @Paul Newcome ! Worked perfectly!
Help Article Resources
Categories
Check out the Formula Handbook template!
