COUNTIF Formula for Week Of

Zach Alexander

I am trying to do a COUNTIF formula of a list of cells that contain dates to determine if any of those dates end up on a given week.

 

I think I need to use COUNTIF and then have a > and < on the date but am not sure how to do this. I could also reference the Monday DATE() +4.

 

Anyone have any ideas?

Jim Hook

Here's a two step process that will work but it's not elegant. Each row has the following cells:

Milestone Date (a Date column)

Current Week Ending Date (another Date column)

Milestone in Current Week with the following formula in Row 2 (thru 5) that determines if the  milestone is in the current week.

 

=IF([Milestone Date]2 < [Current Week Ending Date]2 - 7, "No", IF([Milestone Date]2 > [Current Week Ending Date]2, "No", "Yes"))

 

Then the following COUNTIF formula counts the number of "Yes" values in rows 2 - 5 where the formula above resides in each row with a milestone date.

 

=COUNTIF([Test for Milestone in Current Week]2:[Test for Milestone in Current Week]5, "Yes")

 

The IF() formula could be revised to look for milestone dates inbetween any two given dates if needed rather than simply the current week. The current week ending date could just be in one cell somewhere and referenced with an absolute reference instead of being in each row.

 

I tried to use the COUNTIF formula without already testing the date with the IF function but that seemed to be more complex than this way.

 

 

 

 

J. Craig Williams

The part that gave me pause was 

 

"... dates end up on a given week"

 

 

where i did not know what you meant by 'given' week

1. this week

2. user entered or somehow determined week

 

Craig

Jim Hook

My current systems track projects by week with each week starting on a Monday and ending on the next Sunday. I identify the week by its end date. When I say "given" week it could be this week, next week or some week in the past or future that I need milestone data for and I would manually enter the week ending date in a cell for the foumulas to use.

J. Craig Williams

Thanks Jim.

That clarifies it.

Craig

Zach Alexander

To elaborate we are scheduling meetings and I wanted to do a count of all meetings for say the "week of 4/25".

Jim Hook

That makes sense. The "week of 4/25" starts on a Monday so you would need to modify my IF() formula to see if the meeting date is equal to or greater than 4/25 but less than 5/2 which would include Saturday 4/30 and Sunday 5/1.

J. Craig Williams

Here is a perhaps simpler way.

 

I'm using the (relatively new) WEEKNUMBER function.

The function returns a value associated with calendar week numbers. It is not configurable and returns the wrong value for a large part of the world, but I don't care in this example for what the value is, only that it is consistent.

 

 

Hope that helps.

 

Craig

J. Craig Williams

Here's the thread I was referring to:

 

https://community.smartsheet.com/discussion/change-first-day-week-monday-instead-sunday

Zach Alexander

Thanks your help, the WEEKNUMBER method was fairly painless and worked!