Pulling weekly data

Desertbird

Hello,

I have a sheet that looks like the image below, where employees show a complete or denied status for the date (column 3 is a helper column referring to the parent).

On another sheet I'm trying to pull how many employees are "Complete" for the current and following week (there is only one date per week). I tried with the Today function but it is not working:

=COUNTIFS({Recruiting Range 3}, TODAY(0), {Recruiting Range 2}, "Complete")

However, using TODAY is only going to show the correct answer on the date listed, which is fine but I would prefer it to show the duration of the week. Any ideas how to fix this?

Edit: I have also tried

=COUNTIFS({Recruiting Range 3}, @cell = "8/23/2021")

and switched the date with "Today(-2)" but it keeps only pulling 0.

Kelly Moore

Hello @Desertbird

The WeekNumber Function will allow you to pull data for the week. Note that this function begins a week on Monday.

=COUNTIFS({Recruiting Range 3}, WeekNumber(@cell)=WeekNumber(TODAY()), {Recruiting Range 2}, "Complete")

If you are trying to find the following week ('Next Week') then you are looking for WeekNumber(@cell) = WeekNumber(TODAY())+1

https://help.smartsheet.com/function/weeknumber

cheers

Kelly Moore

@Desertbird

No. We are not filtering for the child/parent rows so they (indented rows) will all be included.

For trouble shooting purposes, remove the 'Complete' term from your COUNTIFS. Whenever a CountIfs is not providing the correct response, begin eliminating terms to determine which term is causing an issue.

=COUNTIFS({Recruiting Range 3},ISDATE(@cell), {Recruiting Range 3}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())).

This should pull any dates from Aug 16 - today.

Kelly Moore

Hello @Desertbird

The WeekNumber Function will allow you to pull data for the week. Note that this function begins a week on Monday.

=COUNTIFS({Recruiting Range 3}, WeekNumber(@cell)=WeekNumber(TODAY()), {Recruiting Range 2}, "Complete")

If you are trying to find the following week ('Next Week') then you are looking for WeekNumber(@cell) = WeekNumber(TODAY())+1

https://help.smartsheet.com/function/weeknumber

cheers

Desertbird

Thanks for your feedback, that definitely looks like what I'm looking for, but when I tried it I get "Invalid Data Type"

=COUNTIFS({Recruiting Range 3}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {Recruiting Range 2}, "complete")

Is there something wrong that needs to be fixed?

Kelly Moore

Hey @Desertbird

I believe the problem is with some non-date cells in the Date column. I've found in my formulas I can get around that if I put an ISDATE term at the beginning of my countifs. Also, in your first example above the word 'Complete' was capitalized. In your most recent post, the word 'Complete' is not capitalized. If there is variation in the column on how it is written, let me know and we can account for that.

=COUNTIFS({Recruiting Range 3},ISDATE(@cell), {Recruiting Range 3}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {Recruiting Range 2}, "Complete")

As you continue using cross sheet formulas, consider renaming the generic smartsheet range # with the actual column name in your source sheet. This will help you (and the smartsheet community) better understand your formulas. You can name the ranges before you click the Insert Reference button.

Let me know how this formula works. Check your date column for any cells with errors

Kelly

Desertbird

Thank you for your response, but I am only getting a value of 0 as the answer when there are rows for this week.

All "Complete" fields are capitalized (uses a dropdown to reduce errors). The only thing that I can think that's preventing it from pulling the data has to do with the indented rows (which I am not able to change). Do I have to refer to the parent again in this function in order for it to identify it?

The helper column is hidden and locked, could that also be contributing to the issue?

Thank you!

Kelly Moore

@Desertbird

No. We are not filtering for the child/parent rows so they (indented rows) will all be included.

For trouble shooting purposes, remove the 'Complete' term from your COUNTIFS. Whenever a CountIfs is not providing the correct response, begin eliminating terms to determine which term is causing an issue.

=COUNTIFS({Recruiting Range 3},ISDATE(@cell), {Recruiting Range 3}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())).

This should pull any dates from Aug 16 - today.

Desertbird

I think I found what was causing that, the column was not formatted as date, but once changed it fixed itself.

Thank you a ton for your help!