Need assistance with a formula

Need assistance with a formula

Hello,

I am using the below formula to count the number of rows where 140R was checked previous month. This formula was working perfectly fine until this month when the year changed. How can I fix this formula where previous month should be looked at December 2019 for January 2020, but it should look at January 2020 in February 2020?

=COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1)

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

    The above will give you the last day of the previous month. If you pull the year and month from this date for the year and month for your calculations, you should be good to go.


    =COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    I personally would use a slightly different approach, but that really is just personal preference. The only change I would actually suggest to your formula would be adding in the IFERROR portions in case there are issues in the Date column such as text entries or blanks. It isn't really NECESSARY, but it is an added level of security/accuracy.


    IFERROR(YEAR(@cell), 0)

    IFERROR(MONTH(@cell), 0)


    =COUNTIFS([140R]:[140R], 1, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Yes.


    DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

    will give you the first day of the current month.


    When you subtract 1 from that, it will go to the last day of the previous month regardless of whether it is a different year or not.

    DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1


    Using this to generate the date for the MONTH or YEAR function will pull the month or year accordingly for the last day of the previous month no matter what the previous month is.

    MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

    YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

Answers

Sign In or Register to comment.