I am looking to add months onto a date based on what is shown in the column highlighted in the screen shot. It is simple in excel using "Edate" but I can not seem to get it working in Smartsheet
Thanks in advance
I'm making the assumption you'd want to come up with the same date, but 12 months later, right? (or whatever # of months is in the 2nd column?)
In the example above - you'd want to see 27/04/21 ?
Because months can have varying #s of days, it can be tricky, but here's what I've done that works for me....
Create a helper column that gives you a date in the middle of the month (same month/year as your original date) - I usually use the 15th.
Then, create a formula that adds 30 * the # of months (2nd column) to the Helper Date.
Then set the formula so that the DAY switches back to the date in the original date (not the 15th in the helper date).
So the actual formula is this:
=DATE(YEAR(Helper@row + (Months@row * 30)), MONTH((Helper@row + (Months@row * 30))), DAY(Date@row))
By forcing the calculation to use a date in the middle of the month, no matter whether you have a 28 day month or a 31 day month, the end result always gives you the month and year you're looking for, and all you have to do is plug the right day back in....
It seems like a complicated way to get there, but it works.
Thanks MCorbin, in regards to the Helper date, do you setup a formula that auto calculates this for you and if so what is that formula?
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
Hi there, I'm building out a new sheet where I utilizing the system's "Last Modified" date column. I tried to trigger a workflow off of this column that sends an update reminder if it hasn't been modified in 2 days, but the column is not showing up as an option (presumably because it's system generated). With that in mind,…
Hey All! Currently, I have a formula that if it is the Parent row (helper column shows 0) then Index multiple sheets using the SSI column. If it is a children row, use the parent SSI column to Index multiple sheets (pulling the same result). The only issue is if I have a child within a child, then the PARENT(SSI@row) no…
I am trying to write a formula based on two sheets. One sheet, Team Members, that has the following columns: Column 1 - Region Column 2 - Industry Column 3 - Contact The other sheet has many columns, but it has matching columns of the same Column 3 - Region Column 4 - Industry Column 10 - Contact I've tried a bunch of…
©2024. All Rights Reserved Smartsheet Inc.