Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Date formula

    ✭✭✭✭✭✭
    in Formulas and Functions

    Hello!

    I currently have 2 columns on my sheet, one where I enter date processed and date expired which has a formula to calculate 1 year expiration from date processed. I have been asked that no matter what date I process, it needs to expire the last date of that month. So for example,

    • date processed = 9/5/23
    • date expired should be 9/30/23

    How would I write this formula AND how would this formula know the actual last day of the month, especially leap year?

    Any assistance is appreciated as I do not want to enter this in manually each time.

    Thanks!

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭
      Answer ✓
      Deric

      @jgneely72151 I forgot to deal with December. The formula should be:

      =IF(MONTH([Date Processed]@row) = 12, DATE(YEAR([Date Processed]@row) + 1, 12, 31), DATE(YEAR([Date Processed]@row) + 1, MONTH([Date Processed]@row) + 1, 1) - 1)

      · Share on FacebookShare on Twitter

    Answers

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions