Is it possible to get a weeks beginning/ending dates from weeknumber?

I have figured out a different set of formulas to get a beginning/ending date for a specific week and month. But have been asked if its possible to use the following:

=countif(range, Weeknumber(@cell) = Weeknumber(Today())

to meet a date range criteria (something similar for month).

I've tried the following with no results:

=COUNTIFS({Dugway: 7. Data Processing_Scan Date}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {Dugway: 7. Data Processing_Not Surveyable}, false)

I get #INVALID DATA TYPE

Thanks,

David

Find more posts tagged with

Sheets

IT & Operations

Comments

Paul Newcome

If there are any blanks within the range that you are using for the WEEKNUMBER criteria, it will throw that error. Try wrapping it in an IFERROR function like so...

=COUNTIFS({Dugway: 7. Data Processing_Scan Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), {Dugway: 7. Data Processing_Not Surveyable}, false)

davidroy

That worked. Thks.

What about using Month(date) to sum a quantity?

Thanks,

David

Paul Newcome

It would be the same principle.

IFERROR(MONTH(@cell), 0) = .........

The idea behind it is that the MONTH, YEAR, WEEKNUMBER, etc. functions all require an actual number to pull. If there is no number to pull, it will throw an error. So we use the IFERROR to pick up on that and replace it with an actual number. Typically you will see 0 used for the "filler" number because there is no month, year, week number, etc. that is 0, but really any number will work. All you have to keep in mind is to not use a number that can actually work so as to not skew your data.

davidroy

Having an issue with the following formula returning 0, when it should be returning 512349 for the month (02/04/19) seems to return for the day not the month.

=SUMIF({Dugway: 7. Data Processing_Processed SF}, IFERROR(MONTH(@cell), 0) = MONTH([1Date]43))

Thanks,

David

Paul Newcome

It seems like your syntax is incorrect. The SUMIF function needs numbers to add together. In your formula you are only referencing dates. If you are trying to SUM a range, you will need to specify that.

=SUMIF({Date Range}, IFERROR(MONTH(@cell), 0) = MONTH([1Date]43), {Range to sum})

If you are only trying to count then you need to use a COUNTIF instead of a SUMIF in which case your current formula's syntax is correct. You would just need to change "SUM" to "COUNT".

davidroy

In the above example "Dugway: 7. Data Processing_Processed SF" is a column of SQFT values that should be added together, but it appears to only be adding one day, not the month.

Thanks,

David

davidroy

You got it right. Found my issue. USER!

Thanks,

David

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

SUmmary in Report Widget
How can i create a Report widget that only shows a status with "red status" from my main data source? Do i need to create a separate data source on just my "red status"
Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…