Number of Items Opened and Closed in a week.

Options

I have a worksheet named "Rich Complaint" that holds the data. Another worksheet named "Calc Data" that holds the calculations for the Dashboard. I want to write a formula in "Calc Data" that looks at the columns in "Rich Complaint" named "Date Created" and column name "Date Closed" to determine how many items have been opened and closed in a given 7-day week.

This is a solution offered by AI. "=COUNTIFS({Rich Complaint}!Date Created:{Rich Complaint}!Date Created, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, {Rich Complaint}!Date Closed:{Rich Complaint}!Date Closed, ">=" & WEEKSTART(TODAY()) - 7, {Rich Complaint}!Date Closed:{Rich Complaint}!Date Closed, "<=" & WEEKEND(TODAY()) - 7)" but it returns the error "unparseable".

Thank you for reading my post. 

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You need those IFERROR statements how I have them in my example.


    If there are blanks and/or non-date values in the date column you are referencing, both the WEEKNUMBER and YEAR function will throw that error. If we use the IFERROR, we can avoid that issue and not worry about false counts because the IFERROR outputs a zero in those instances which you will never be searching for week number or year zero.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    That looks like a formula for Excel.


    Are you looking for a count of how many were both opened and closed within the week? So something that was opened on Monday and closed on Friday but not something that was opened on Friday and closed on Monday since it was not both opened and closed within the same week? If not, can you clarify exactly what you are looking for?

  • FGCC Smartsheet
    Options

    I'm looking for a metric on a dashboard that says on X week #of cases opened and # cases closed.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    In that case it sounds like you need two different formulas. One for open and one for closed. They would look something like this:

    =COUNTIFS({Source Sheet Open Date Column}, AND(IFERROR(WEEKNUMBER(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

  • FGCC Smartsheet
    Options

    Thanks! I'll let you know how it goes.

  • FGCC Smartsheet
    Options

    I feel like I'm getting close here. This formula works but it returns a result of "0". Thanks for your help!

    =COUNTIFS({Rich Complaint Range 2}, AND(IFERROR(WEEKNUMBER(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

    This is a screen shot of the data it's looking at.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are there any entries for the first week of 2023?

  • FGCC Smartsheet
    Options

    I think I get it now. The bold number 1 needs to change based on the week of the year. Correct?

    =COUNTIFS({Rich Complaint Range 2}, AND(IFERROR(WEEKNUMBER(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

  • FGCC Smartsheet
    Options

    That wasn't it. I changed that number to a week that I know has dates in it and it returned "0".

  • FGCC Smartsheet
    Options

    I've been working on this. According to research this formula should work. But it returns #UNPARSEABLE.

    =COUNTIFS({Rich Complaint Range 2}, AND(WEEKNUMBER([Date Created]@row) = 43, YEAR([Date Created]@row) = 2023, NOT(ISBLANK([Date Created]@row))))

    I need to look at something else for a few minutes.😒

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Replace each instance of [Date Created]@row with @cell.

    Example:

    WEEKNUMBER(@cell)

  • FGCC Smartsheet
    edited 10/12/23
    Options

    I'm getting invalid data type. Like a data type mismatch?

    =COUNTIFS({Rich Complaint Range 2}, AND(WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023), NOT(ISBLANK(@cell)))

    This is the different iterations of that formula I've tried.

    =COUNTIFS({Rich Complaint Range 2}, WEEKNUMBER(@cell) = 1, YEAR(@cell) = 2023)

    =COUNTIFS({Rich Complaint Range 2}, WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023)


    =COUNTIFS({Rich Complaint Range 2}, WEEKNUMBER(@cell) = 42, YEAR(@cell) = 2023)

    =COUNTIFS({Rich Complaint Range 2}, WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023)

    =COUNTIFS({Rich Complaint Range 2}, WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023)

    =COUNTIFS({Rich Complaint Range 2}, WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023)

    =COUNTIFS({Rich Complaint Range 2}, WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023)


    =COUNTIFS({Rich Complaint Range 2}, AND(WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023, NOT(ISBLANK(@cell))))

    =COUNTIFS({Rich Complaint Range 2}, AND(WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023, NOT(ISBLANK(@cell))))

    =COUNTIFS({Rich Complaint Range 2}, AND(WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023, NOT(ISBLANK(@cell))))


    =COUNTIFS({Rich Complaint Range 2}, AND(WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023, NOT(ISBLANK(@cell)))

    =COUNTIFS({Rich Complaint Range 2}, AND(WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023), NOT(ISBLANK(@cell)))


    This one I tried with a different date column to make sure it wasn't the column.

    =COUNTIFS({Rich Complaint Range 3}, AND(WEEKNUMBER(@cell) = 43, YEAR(@cell) = 2023), NOT(ISBLANK(@cell)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You need those IFERROR statements how I have them in my example.


    If there are blanks and/or non-date values in the date column you are referencing, both the WEEKNUMBER and YEAR function will throw that error. If we use the IFERROR, we can avoid that issue and not worry about false counts because the IFERROR outputs a zero in those instances which you will never be searching for week number or year zero.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!