Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Smartsheet formula syntax help

John Kulie
John Kulie ✭
edited 12/09/19 in Archived 2015 Posts

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

Tags:
· Share on FacebookShare on Twitter

Comments

  • Justin Philips
    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

     

    · Share on FacebookShare on Twitter
  • Terry Frank
    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

    · Share on FacebookShare on Twitter
  • John Kulie
    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?

    · Share on FacebookShare on Twitter
  • John Kulie
    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.

    · Share on FacebookShare on Twitter
  • Terry Frank
    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

    · Share on FacebookShare on Twitter
  • John Kulie
    John Kulie ✭

    Terry, yes that worked!

     

    Thanks so much for your help!

     

    John

    · Share on FacebookShare on Twitter
  • sdzierzega24461
    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

    · Share on FacebookShare on Twitter
  • sdzierzega24461
    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

    · Share on FacebookShare on Twitter
  • sdzierzega24461
    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)))

    · Share on FacebookShare on Twitter
  • sdzierzega24461
    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

    · Share on FacebookShare on Twitter
  • Ryan G
    Ryan G ✭

    Is Smartsheet considering implementing the EDATE function?  

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories