Date range calculation, count month numbers

Options
LisaB:-)
LisaB:-) ✭✭
in Formulas and Functions

Hi All

Could I please ask for some help with a date formula?

I'm working on an online education project. Each module takes around 6+ months to complete, see below the date range that starts on 14 July 2020 and ends on 25 Jan 2021.

image.png

I need to count how many modules occur in January, how many in February, how many in March, etc (by year too). So just using the one row of the example below, would result in:

July 2020 = 1, August 2020 = 1, September 2020 = 1, October 2020= 1, November 2020 = 1, December 2020 = 1, January 2021 = 1.

The end purpose is to help resource the project, so if we can tell that we have 9 modules in March 2021, then we will need 9 producers. Any help would be greatly appreciated!

Tags:
· Share on FacebookShare on Twitter

Best Answers

«1

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @LisaB:-)

    No problem! I can help you with this. The MONTH function will be ideal here, as it associates a number with a month which you can then search for/between. You'll also want to use the YEAR function as well, since you span across both 2020 and 2021.

    Let's use July 2020 as the first example, then you can adjust it for each of the months from there. (Note: I'm building this presuming it's a Cross Sheet formula.)


    For July 2020, you'll want to search that the START is either before or equal to July. Then you'll need to ALSO make sure that the END is after or equal to July.

    ex - MONTH(@cell) >= 7


    But you'll also need to check that the year is equal to 2020

    ex - YEAR(@cell) = 2020


    Finally, with both of these functions, it's best practice to wrap an IFERROR statement around each of them in case it finds an error with blank date cells in the column range.

    ex - IFERROR(MONTH(@cell), 0) >= 7


    Then we can use a COUNTIFS (plural) function to count the rows that meet this criteria. You'll list each date range twice (once for the month and once for the year), and each criteria immediately after the range.


    Try this final formula:

    =COUNTIFS({START Date Column}, IFERROR(MONTH(@cell), 0) >= 7, {START Date Column}, IFERROR(YEAR(@cell), 0) = 2020, {END Date Column}, IFERROR(MONTH(@cell), 0) <= 7, {END Date Column}, IFERROR(YEAR(@cell), 0) = 2020)


    Then to look for August, you would just change the 7 to an 8:

    =COUNTIFS({START Date Column}, IFERROR(MONTH(@cell), 0) >= 8, {START Date Column}, IFERROR(YEAR(@cell), 0) = 2020, {END Date Column}, IFERROR(MONTH(@cell), 0) <= 8, {END Date Column}, IFERROR(YEAR(@cell), 0) = 2020)

    Does that make sense?


    Let me know if this works for you!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options

    Hi Genevieve, thanks for stepping in.

    I'm afraid that's not working. (It's not a cross-sheet formula.) This is how I've customised the formula to reference my columns:

    =COUNTIFS([Start], IFERROR(MONTH(@cell), 0) >= 7, [Start], IFERROR(YEAR(@cell), 0) = 2020, [Finish], IFERROR(MONTH(@cell), 0) <= 7, [Finish], IFERROR(YEAR(@cell), 0) = 2020)

    I tried replacing '@cell' with '@row', but it didn't recognise the reference. Should this formula be in a Colum Formula, which I can then total at the bottom of the sheet?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    edited 12/02/20
    Options

    Hi @LisaB:-)

    If you're referencing columns within the sheet, then you'll need to type them twice with a colon between... like so:

    Start:Start and Finish:Finish

    It should be @cell, since you're looking in the cells of the entire column, versus @row, which would only look in this current row where the formula is placed.

    Try this:

    =COUNTIFS(Start:Start, IFERROR(MONTH(@cell), 0) >= 7, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 7, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020)


    Here's an article on referencing columns that may help in your future formula building. 🙂

    This formula would either be placed in its own column in the sheet or in a Sheet Summary field. Let me know if this structure works for you, now!

    Cheers,

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options

    That works great, thank you! The second part of the problem is that I need to introduce another IF statement.

    I have a column called 'CD Module', which is a checkbox. If the box is checked, then I want to count the row, if it is blank, then no count. Would you be so kind as to help me with that?

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options

    Hi again, I'm trying this formula but with no luck so far:

    =COUNTIFS(AND(CD Module:CD Module = 1, (Start:Start, IFERROR(MONTH(@cell), 0) >= 12, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 12, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020))

    It would also be great if the formula could exclude results from [Status] "Complete" or "Not Required".

    Many thanks.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    edited 12/02/20
    Options

    Hi @LisaB:-)

    The COUNTIFS already implies AND so you don't need to add it in. Instead, just add it as an extra Range then Criteria.

    A COUNTIFS works like this:

    =COUNTIFS(Range:Range, Criteria, Range:Range, Criteria, Range:Range, Criteria, ... etc)


    So, adding in you checkbox criteria:

    [CD Module]:[CD Module] = 1

    (note, you have to have [These] around the column name because it contains a space. Only one-word column names without numbers can be referenced [without these])

    Formula:

    =COUNTIFS([CD Module]:[CD Module] = 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 7, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 7, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020)


    If you have other criteria, you can say stuff like is not This Criteria, or use <> (which means "not")

    ex.

    Status:Status, <> "Complete"


    Full Formula:

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


    Is there anything else you need added in?

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options

    Thank you, I really appreciate your prompt support. Unfortunately, this one returns #invalid operator

    =COUNTIFS([CD Module]:[CD Module] = 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 7, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 7, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020)

    and this one returns #unparseable

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

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @LisaB:-)

    Would it be possible to see a screen capture of your sheet? Column names need to be identical to what's in the formula (ex "CD Module" and "Status") in order for it to be read properly.


    I also noticed I put

    [CD Module]:[CD Module] = 1

    instead of

    [CD Module]:[CD Module], 1

    (the comma works as an =) My apologies!


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

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options
    image.png

    Thank you!

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options
    image.png


    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options

    Wait, I've just adjusted it, replacing the = with a , as you suggest, and something's working! Will post back shortly.

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options

    Hi again. I've used this formula to identify modules that are in production in January 2021.

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

    It returns a 0 result, but it should return 2, see the pink lines in this screenshot.

    image.png

    It should return 2, because there will be 2 modules in progress during January 2021. Hope you can help.

    · Share on FacebookShare on Twitter
  • LisaB:-)
    LisaB:-) ✭✭
    Options

    It's working perfectly thank you so much. It's going to be really useful, I'm so grateful for your help 😀

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem at all!! I'm so glad we could get there in the end.

    Cheers!

    Genevieve

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!