Number of Items Opened and Closed in a week.

FGCC Smartsheet

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. 

Paul Newcome

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.

Paul Newcome

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

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

Paul Newcome

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

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

FGCC Smartsheet

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

Are there any entries for the first week of 2023?

FGCC Smartsheet

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

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

FGCC Smartsheet

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

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

Example:

WEEKNUMBER(@cell)

FGCC Smartsheet

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

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.