Formula to check a box if the date in another column is the current month

Options
NickBlocker
NickBlocker ✭✭✭
edited 06/03/24 in Smartsheet Basics

Looking for a formula to check a box if the date in another column is the current month. Needing this to set up a process in which I can filter to Month to Date

Nick Blocker - Analytics Adventurer

Tags:

Best Answers

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    =IF(MONTH(Created@row) = MONTH(TODAY()), 1, 0)

  • NickBlocker
    NickBlocker ✭✭✭
    Options

    @Devin Lee Thank you for replying so quickly! I truly appreciate your help. How can you modify the formula to check the box if its last month?

    Nick Blocker - Analytics Adventurer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    =IF(MONTH(Created@row) = MONTH(TODAY()) - 1, 1, 0)

  • NickBlocker
    NickBlocker ✭✭✭
    Options

    @Devin Lee thank you for being amazing and quick at replying to my question. If I could by you a coffee I would!😎☕️

    Nick Blocker - Analytics Adventurer

  • NickBlocker
    NickBlocker ✭✭✭
    edited 06/03/24
    Options

    @Devin Lee Any thoughts on why the formula is including dates in 2023? I did ensure that the date columns are formated as dates.

    Nick Blocker - Analytics Adventurer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    It's only checking the month. You will want to check both month and year.

    =IF(AND(MONTH(Created@row) = MONTH(TODAY()), YEAR(Created@row) = YEAR(TODAY())), 1, 0)

    =IF(AND(MONTH(Created@row) = MONTH(TODAY())-1, YEAR(Created@row) = YEAR(TODAY())), 1, 0)

  • NickBlocker
    NickBlocker ✭✭✭
    Options

    @Devin Lee I hope you can help me here and let me know where I am going wrong in my formula to extract the month "Name"

    =IF([Created Month]@row = "1", "JAN", IF([Created Month]@row = "2", "FEB", IF([Created Month]@row = "3", "MAR", IF([Created Month]@row = "4", "APR", IF([Created Month]@row = "5", "MAY", IF([Created Month]@row @row = "6", "JUN", IF([Created Month]@row = "7", "JUL", IF([Created Month]@row = "8", "AUG", IF([Created Month]@row = "9", "SEP", IF([Created Month]@row = "10", "OCT", IF([Created Month]@row = "11", "NOV", IF([Created Month]@row = "12", "DEC"))))))))))))

    Nick Blocker - Analytics Adventurer

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    @NickBlocker

    I use this one in a sheet for dates. Try this:

    =IF([Created Month]@row = "12", "DEC", IF([Created Month]@row = "11", "NOV", IF([Created Month]@row = "10", "OCT", IF(OR([Created Month]@row = "9", [Created Month]@row = "09"), "SEP", IF(OR([Created Month]@row = "8", [Created Month]@row = "08"), "AUG", IF(OR([Created Month]@row = "7", [Created Month]@row = "07"), "JUL", IF(OR([Created Month]@row = "6", [Created Month]@row = "06"), "JUN", IF(OR([Created Month]@row = "5", [Created Month]@row = "05"), "MAY", IF(OR([Created Month]@row = "4", [Created Month]@row = "04"), "APR", IF(OR([Created Month]@row = "3", [Created Month]@row = "03"), "MAR", IF(OR([Created Month]@row = "2", [Created Month]@row = "02"), "FEB", IF(OR([Created Month]@row = "1", [Created Month]@row = "01"), "JAN"))))))))))))

    Hope this helps.

    Peggy

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Options

    Looks like you have a second @row in your formula for June. Other than that, you are missing a return if the last IF statement if false.

    =IF([Created Month]@row = "1", "JAN", IF([Created Month]@row = "2", "FEB", IF([Created Month]@row = "3", "MAR", IF([Created Month]@row = "4", "APR", IF([Created Month]@row = "5", "MAY", IF([Created Month]@row = "6", "JUN", IF([Created Month]@row = "7", "JUL", IF([Created Month]@row = "8", "AUG", IF([Created Month]@row = "9", "SEP", IF([Created Month]@row = "10", "OCT", IF([Created Month]@row = "11", "NOV", IF([Created Month]@row = "12", "DEC", ""))))))))))))