Smartsheet formula syntax help

John Kulie

I am trying to write a formula to add 9 months to date from another cell in my worksheet and am struggling with the formula syntax.

 

Adding days to a date is very straightforward, but I have not been able to figure out how to identify how to add months to  a date.

 

Can anyone help with this formula?

 

Thanks, John

Justin Philips

John,

 

As you probably know the Excel function for that problem is =EDATE(start_date,months)

It doesn't seem like Smartsheet supports the =EDATE formula.

I even tried to import an Excel workbook with that formula in it and I get the same error:  #UNRECOGNIZED FUNCTION

 

Terry Frank

This could be accomplished in a few steps with an IF statement.  Starting with a formula like this: =DATE(YEAR(Date1), MONTH(Date1) + 9, DAY(Date1)) , 9 months are added to "Date1" date.  The problem comes in when MONTH(Date1)+9 exceeds 12 months in a year.  To account for this incorporate an IF statement that first checks in MONTH(Date1)+9 exceeds 12, if it doesn't use the formula above, else use the formula =DATE(YEAR(Date1) +1, MONTH(Date1) + 9 - 12, DAY(Date1)), which adds a year (YEAR(Date1) +1, and for the result of MONTH (Date1)+9-12) for the month. 

 

Hope this helps!

Terry

John Kulie

Justin, you are correct in that the EDATE function does not work in Smartsheet.

 

Terry, you gave me a good thought process to try the nested IF function; but I am off somewhere... here is what I wrote =IF(MONTH([Purchase Date]1)+9 > 12), (DATE(YEAR([Purchase Date]1)+1, MONTH([Purchase Date]1), DAY([Purchase Date]1)), (DATE(YEAR([Purchase Date]1), MONTH([Purchase Date]1)+9, DAY([Purchase Date]1)))... IF the condition is true this would just add a 1 year and leave the month/day alone, IF the condition is false is just addes 9 months and leaves the year/day alone... it is not working as written though... have I made any glaring errors? Can I separate the two forumlas with the comma delimiter?

John Kulie

I forgot, I would have to subtract 3 from the MONTH value in the first argument, if I can get this formula to work.

Terry Frank

Hi John, I thing just a set of parenthesis are out of place...  also ENSURE that the formula is place in a column with the column property set to Date.  And this formula seems to be working for me.

 

=IF((MONTH([Purchase Date]1) + 9) > 12, DATE(YEAR([Purchase Date]1) + 1, MONTH([Purchase Date]1) + 9 - 12, DAY([Purchase Date]1)), DATE(YEAR([Purchase Date]1), MONTH([Purchase Date]1) + 9, DAY([Purchase Date]1)))

 

Good Luck.

Terry

John Kulie

Terry, yes that worked!

 

Thanks so much for your help!

 

John

sdzierzega24461

This is a great solution and helping me move in the right direction, really nice work and thank you!!

1) Would the inverse logic (-9 months ) be as easy as changing the first argument value from ">12" to "<12", the second argument from "+1" to "-1", the third argument from "+9 -12" to "-9 + 12", and the last argument from "+9" to "-9."  This seems to make sense.

2)Thinking ahead how would you guys recommending moving from a hard-coded (value of 9 months) to a cell reference where a numeric month entered may be a positive or negative value?

Again, this has been extremely helpful, and thanks for helping me out!

Stan

sdzierzega24461

So I tried the logic change for my first question, but was unsuccessful assuming because instead of using a hard coded value such as 9 months, I reference a cell to obtain that value (since this number is variable for my need but always negative at least) and my formula was Unparseable.  I recall that the cell with my numeric month value is not a "Date" format column that could be causing the problem.

=if((Month([End date]2)-[period of notice(months)]2<12,Date(Year([End date]2)-1,Month([End date]2)-[period of notice(months)]2+12,Day([End date]2)),Date(Year([End date]2),Month([End date]2)-[period of notice(months)]2,Day([End date]2)))

Any suggestions would be appreciated.

Thanks,

Stan

sdzierzega24461

Oops, meant "Invalid Value" as the error message and the formula had a missing parenthesis

=IF((MONTH([End date]2) - [period of notice(months)]2) < 12, DATE(YEAR([End date]2) - 1, MONTH([End date]2) - [period of notice(months)]2 + 12, DAY([End date]2)), DATE(YEAR([End date]2), MONTH([End date]2) - [period of notice(months)]2, DAY([End date]2)))

sdzierzega24461

Sorry for multiple posts.  Got it to work and tested it with hardcoding -9 months instead of + 9 months, but still need a solution for referencing a cell with a numeric value of the month as the variable to use.

Here is the functioning (hardcoded) formula for reference:

=IF((MONTH([End date]21) - 9) <= 0, DATE(YEAR([End date]21) - 1, MONTH([End date]21) - 9 + 12, DAY([End date]21)), DATE(YEAR([End date]21), MONTH([End date]21) - 9, DAY([End date]21)))

Thanks again,

Stan

Ryan G

Is Smartsheet considering implementing the EDATE function?