Formula to identify the season of the year

Good afternoon; I would like to know if it is possible to create a formula to obtain the name of the season of the year related to a date; the idea is to be able to filter the seasons and set an anticipated schedule for the activities depending on the weather.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF(OR([Date Column]@row>= DATE(YEAR([Planned Start Date]@row), mm, dd), [Date Column]@row<= DATE(YEAR([Planned Start Date]@row), mm, dd)), "Winter", IF([Date Column]@row<= DATE(YEAR([Planned Start Date]@row), mm, dd), "Spring", IF([Date Column]@row<= DATE(YEAR([Planned Start Date]@row), mm, dd), "Summer", "Fall")))


    Just update each "mm" and "dd" to the appropriate month and day number for each season.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lorena
    Lorena ✭✭
    Answer ✓

    Awesome!!! This function is perfect; you are a rock star; thank you so much for your time in helping me with this.

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Here is something we used for a basic one that is based on assigning a season to a Month rather than a specific date each year. Season Date column needs to be updated to whatever your column date is that you are referencing.

    =IF(MONTH([Season Date]@row) >= 11, "Winter", IF(MONTH([Season Date]@row) >= 9, "Fall/Autumn", IF(MONTH([Season Date]@row) >= 6, "Summer", IF(MONTH([Season Date]@row) >= 4, "Spring", IF(MONTH([Season Date]@row) >= 1, "Winter")))))

  • Lorena
    Lorena ✭✭

    Hi, thank you for responding to my question; that formula is good, but it is essential to include the day because, for example, Mach 21 will start the spring season, June 21 will start the summer season, September 21 will start the fall season and December 21 will start the winter season.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot of how your sheet is set up?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lorena
    Lorena ✭✭

    Good morning; this is the sheet with which I work; you can see the highlighted cell the date 3/22/23 must be spring season, but with the formula posted, it shows like winter season; my goal is can filter the activities for the season; and to check the possible delay for the weather season.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try incorporating the DATE function.

    =IF(OR([Date Column]@row>= DATE(yyyy, mm, dd), [Date Column]@row<= DATE(yyyy, mm, dd)), "Winter", IF([Date Column]@row<= DATE(yyyy, dd, mm), "Spring", IF([Date Column]@row<= DATE(yyyy, mm, dd), "Summer", "Fall")))


    You can make the DATE functions dynamic by replacing the hard coded year with the year from the date column.

    DATE(YEAR([Date Column]@row), mm, dd)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lorena
    Lorena ✭✭

    I was proving the function you posted, but if you see the green highlight cell for this date, it is not winter season; I am trying to correct and incorporate another form; but I can't understand when you said, "You can make the DATE functions dynamic by replacing the hard coded year with the year from the date column." Could you please explain again? I appreciate your help.


    DATE(YEAR([Date Column]@row), mm, dd)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Which column are you using to determine which season it is in?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lorena
    Lorena ✭✭

    Planned Start Date

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF(OR([Date Column]@row>= DATE(YEAR([Planned Start Date]@row), mm, dd), [Date Column]@row<= DATE(YEAR([Planned Start Date]@row), mm, dd)), "Winter", IF([Date Column]@row<= DATE(YEAR([Planned Start Date]@row), mm, dd), "Spring", IF([Date Column]@row<= DATE(YEAR([Planned Start Date]@row), mm, dd), "Summer", "Fall")))


    Just update each "mm" and "dd" to the appropriate month and day number for each season.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lorena
    Lorena ✭✭
    Answer ✓

    Awesome!!! This function is perfect; you are a rock star; thank you so much for your time in helping me with this.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!