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.

    Return "Yes" if a date range includes a certain month

    ✭✭
    in Formulas and Functions

    I am trying to return either a "Yes" or "No" value in columns for the months of the year if a date range for a project or activity has dates in that month. For example, if a project starts on 2/15/2022 and ends 4/15/2022, I would want to have "Yes" in the February, March, and April columns and "No" in all other month columns for the year. I have tried multiple formulas to try and achieve this, but so far have been unsuccessful.

    Has anyone had success with something like this?

    I feel like I need several nested OR statements, but can't seem to get my head around it and the closest to success I've been was simply to get a "Yes" in the month for only the start and end dates (while all the months covered by the date range had an incorrect "No").

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

      Hi @O.Nash,

      Bit of a throwback, but it'd probably be easier to do something like this (again, using Jan 2023 as an example):

      =IF(AND([Start Date]@row < DATE(2023, 2, 1) - 1, [End Date]@row >= DATE(2023, 1, 1)), "Yes", "")

      Start Date should be less than the 1st of the following month and end date equal to/greater than date of the 1st of the current month.

      For December you'd also need to update the year:

      =IF(AND([Start Date]@row < DATE(2024, 1, 1) - 1, [End Date]@row >= DATE(2023, 12, 1)), "Yes", "")

      Example doing this (cutting out a lot of months just because changing the formula a lot for just a test is effort!):

      image.png

      Hope this helps, but if you encounter any problems/issues then just let us know! 😊

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      Hi @Victoria Curry,

      If your months are in the same year this is certainly doable.

      For your January column:

      =IF(OR(MONTH([Start Date]@row) = 1, MONTH([End Date]@row) = 1, OR(AND(1 - MONTH([Start Date]@row) > 0, MONTH([End Date]@row) - 1 > 0))), "Yes")

      In the following months, substitute the bold numbers in the formula for the relevant month number (2 for February, 3 for March etc.).

      Example (only using a few months):

      image.png

      If your project dates and columns spanned several years (e.g. Jan 2023) then you would have to make these statements into AND ones and include the year.

      Hope this helps!

      · Share on FacebookShare on Twitter
    • ✭✭

      Hi Nick,

      Thanks for a great response. Unfortunately the months are not always in the same year. The sheet is meant to cover a fiscal year, which for us starts in October and ends in September, which means it will always span 2 calendar years. That has been part of my major challenges with figuring out a solution.

      · Share on FacebookShare on Twitter
    • ✭
      edited 09/18/24

      @Nick Korna Could you elaborate on how you would convert to AND statements and add the Year please? and Thank you :-) - I've been trying and failing to adapt the formula to include the year.

      Nick Korna
      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      Hi @O.Nash,

      Bit of a throwback, but it'd probably be easier to do something like this (again, using Jan 2023 as an example):

      =IF(AND([Start Date]@row < DATE(2023, 2, 1) - 1, [End Date]@row >= DATE(2023, 1, 1)), "Yes", "")

      Start Date should be less than the 1st of the following month and end date equal to/greater than date of the 1st of the current month.

      For December you'd also need to update the year:

      =IF(AND([Start Date]@row < DATE(2024, 1, 1) - 1, [End Date]@row >= DATE(2023, 12, 1)), "Yes", "")

      Example doing this (cutting out a lot of months just because changing the formula a lot for just a test is effort!):

      image.png

      Hope this helps, but if you encounter any problems/issues then just let us know! 😊

      · Share on FacebookShare on Twitter
    • ✭

      @Nick Korna YOU ARE A LEGEND!!! Thank you so much. That worked for me absolutely brilliantly! I would never have got there on my own!

      · Share on FacebookShare on Twitter

    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