Date range calculation, count month numbers

2»

Answers

  • LisaB:-)
    LisaB:-) ✭✭
    edited 12/02/20

    I've hit a problem, in that the modules that fall into 2022 aren't being counted. The calculation is returning 5 modules for November 2021, but there are in fact 15 modules.

    How can we solve this? Would a 'third' calculation need to be added to the 'two' there already? Many thanks. @Genevieve P

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @LisaB:-)

    Yes, you could add in a third statement to look for 2022, but specifying that the ending might be in 2022.

    So for June 2021 where you may have some starting in 2020 or some other rows ending in 2022, then you could do something like so:


    =COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) <= 6, Start:Start, IFERROR(YEAR(@cell), 0) = 2021, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 6, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)

    +

    COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 6, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 6, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)

    +

    COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 6, Start:Start, IFERROR(YEAR(@cell), 0) = 2021, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 6, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2022)



    Since your rows seem to only span around 6 months or so, it sounds like once you get to October 2021 then you won't need the first COUNTIF part of the formula, since nothing will be starting in 2020 and still going on through October. Does that make sense?

  • Hi @Genevieve P

    I'm afraid that's not working. For February 2021, it's returning a total of 10, when a manual count reveals a total of 8.

    I'm stumped.

  • LisaB:-)
    LisaB:-) ✭✭
    edited 12/03/20

    Thanks @Genevieve P I have found a solution by downloading it to excel and manipulating the data there, it doesn't provide a 'live' report but it will meet my current deadline. I'm going to try your new v2. formula over the next couple of days, thank you for sticking with it.

  • That's got it! Thank you - that's a much better formula, much easier to keep updated. Thanks again 😍

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!