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
Comments
-
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)
-
That worked. Thks.
What about using Month(date) to sum a quantity?
Thanks,
David
-
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.
-
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
-
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".
-
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
-
You got it right. Found my issue. USER!
Thanks,
David
-
