If date is last Month

11/26/20
Answered - Pending Review

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 CronkMark 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 MurphyDale 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

  • TM123TM123 ✭✭✭✭✭

    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 CronkMark 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([email protected]) = MONTH(TODAY()), "Current Mnth", (IF(MONTH([email protected]) = MONTH(TODAY()) - 1, "Last Mnth", "Future Mnth"))), "No date")

  • Mark CronkMark Cronk ✭✭✭✭✭

    Try:

    =IFERROR(IF(MONTH([email protected]) = MONTH(TODAY()), "Current Mnth",

    IF(AND(month([email protected])=12, month(today())=1), "Last Month",

    IF(MONTH([email protected]) = 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.

Sign In or Register to comment.