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

davidroy

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

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!