If date is last Month

Hi - I'm trying to workout how to do a formula where it generates an answer yes/no if the date was last month - I've managed to do a count ifs last month and tried to use similar logic for just if(date=last month,"Yes","No")

"=COUNTIFS(XXXXX, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()), YEAR(TODAY()))))" < This worked for count if


how do i adopt to be =if(xxxx="Last Month","Yes","No")


Any help would be much appreciated


thanks,

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 11/26/20

    Hi @TM123 ,

    Try:

    =IF(month( /insert cell you're examining/) =month(TODAY())-1),"Yes", "No")

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @TM123

    Try this:

    =IFERROR(IF(MONTH([Column41]@row) = MONTH(TODAY()), "Current Month", (IF(MONTH([Column41]@row) > MONTH(TODAY()), "Future", "Past"))), "No date")

    It should check for future, current, past months.

    Column 41 is the column with dates you want to check. It must be a DATE column!

    Cheers,

    Dale

  • TM123
    TM123 ✭✭✭

    thanks for these, I'll definitely give them a try.

    With both solutions, could I include the year as well so if previous month, in order to future proof the formula for example if the cell is October 2021, if I used just previous month, it would pick up October 21 even though I'm in November 20.


    thanks again.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Good morning @TM123 ,

    Absolutely, just nest another IF statement using Year()=Year(today()).

    Test your formula using January. The month prior is 12 and year is last year which may break the basic formulas above. You'll need to add an IF statement that looks for month=1 and then changes the year to =Year(today()-1) to get the previous year.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • @Mark Cronk

    Do you have a nested year example? Trying to flag the previous month but am confused about how to deal with the 12th plus month.

    =IFERROR(IF(MONTH(LAUNCH@row) = MONTH(TODAY()), "Current Mnth", (IF(MONTH(LAUNCH@row) = MONTH(TODAY()) - 1, "Last Mnth", "Future Mnth"))), "No date")

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Try:

    =IFERROR(IF(MONTH(LAUNCH@row) = MONTH(TODAY()), "Current Mnth",

    IF(AND(month(launch@row)=12, month(today())=1), "Last Month",

    IF(MONTH(LAUNCH@row) = MONTH(TODAY()) - 1, "Last Mnth", "Future Mnth")))), "No date")

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!