Count specific day qty in date range.

Speigel

I'm trying to write a formula to look at the range of dates (start date column and finish date column), and count how many Mondays, or Tuesdays etc are within that date range. Specifically I have 3 columns to calculate. How many week days (2, 3, 4, 5 or 6) are in the range; how many Saturdays (7) and how many Sundays (1) are in the range. I had a solution, but had to make a separate change and it broke my original method. I feel like there has to be a combination of If( or contains( or weekdays( etc.... but I can't figure it out. Any help is appreciated!!!

Genevieve P.

Hi @Speigel

Work Days:

There's a really simple way to get the number of working days in a range - you can use the NETWORKDAYS function!

Try this for your Week Days column:

=NETWORKDAYS([Start Date]@row, [Finish Date]@row)

Weekend Total (combined):

Then if you wanted to count how many weekend days there are (as a general "weekend day" without specifying if it's Saturdays or Sundays), you can simply find the number of days between the two dates in total, then minus the previously calculated work days:

=NETDAYS([Start Date]@row, [Finish Date]@row) - [Week Days]@row

To find the individual weekend day, we'll need to write a Nested IF statement with three potential outcomes.

If the Weekend formula is an even number, divide it by 2 to find the number of Saturdays. This will be the same number as Sundays.

=NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2))

Odd Weekend Days:

If the weekend number is ODD, then when we divide by 2 it will return .5. We can check to see if that should be either rounded up or down in each Saturday and Sunday column.

The only time you would have an extra Saturday without a Sunday is if the task ended on a Saturday. So, we check to see if the End Date is a Saturday, and if it is, add that extra 1 to the Saturday column (but rounding up answer that was divided by 2).

IF(WEEKDAY([Finish Date]@row) = 7, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) + 0.5

However, that extra day could be a Sunday, if the task STARTED on a Sunday. In this instance, we would minus 0.5 from the Saturday column to round down:

IF(WEEKDAY([Start Date]@row) = 1, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) - 0.5

Saturday Column Full Formula:

=IF(WEEKDAY([Finish Date]@row) = 7, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) + 0.5, IF(WEEKDAY([Start Date]@row) = 1, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) - 0.5, (NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2))

Sunday Column Full Formula:

For Sunday, we just need to swap around the statements... adding 0.5 if the Start Date is a Sunday, and subtracting 0.5 if the End Date is a Saturday:

=IF(WEEKDAY([Start Date]@row) = 1, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) + 0.5, IF(WEEKDAY([Finish Date]@row) = 7, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) - 0.5, (NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2))

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Speigel

Work Days:

There's a really simple way to get the number of working days in a range - you can use the NETWORKDAYS function!

Try this for your Week Days column:

=NETWORKDAYS([Start Date]@row, [Finish Date]@row)

Weekend Total (combined):

Then if you wanted to count how many weekend days there are (as a general "weekend day" without specifying if it's Saturdays or Sundays), you can simply find the number of days between the two dates in total, then minus the previously calculated work days:

=NETDAYS([Start Date]@row, [Finish Date]@row) - [Week Days]@row

To find the individual weekend day, we'll need to write a Nested IF statement with three potential outcomes.

If the Weekend formula is an even number, divide it by 2 to find the number of Saturdays. This will be the same number as Sundays.

=NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2))

Odd Weekend Days:

If the weekend number is ODD, then when we divide by 2 it will return .5. We can check to see if that should be either rounded up or down in each Saturday and Sunday column.

The only time you would have an extra Saturday without a Sunday is if the task ended on a Saturday. So, we check to see if the End Date is a Saturday, and if it is, add that extra 1 to the Saturday column (but rounding up answer that was divided by 2).

IF(WEEKDAY([Finish Date]@row) = 7, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) + 0.5

However, that extra day could be a Sunday, if the task STARTED on a Sunday. In this instance, we would minus 0.5 from the Saturday column to round down:

IF(WEEKDAY([Start Date]@row) = 1, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) - 0.5

Saturday Column Full Formula:

=IF(WEEKDAY([Finish Date]@row) = 7, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) + 0.5, IF(WEEKDAY([Start Date]@row) = 1, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) - 0.5, (NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2))

Sunday Column Full Formula:

For Sunday, we just need to swap around the statements... adding 0.5 if the Start Date is a Sunday, and subtracting 0.5 if the End Date is a Saturday:

=IF(WEEKDAY([Start Date]@row) = 1, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) + 0.5, IF(WEEKDAY([Finish Date]@row) = 7, ((NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2) - 0.5, (NETDAYS([Start Date]@row, [Finish Date]@row) - WeekDays@row) / 2))

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve

Speigel

Thank you @Genevieve P. for your detailed response... it did help confirm my thoughts. One thing I failed to mention I suppose is that I can't (or need to avoid) using the networkday function. This is for two reasons. 1) This build is for a template that will be used by groups some of which will use it for 5 working days, or 6 or even 7. 2.) The standard rule (in other formulas on this build) pertains to the possibility of 7 days working where ST, OT and DT will be calculated. Short version... switching the settings from project to project to adjust the working days isn't the greatest plan in this case. So I was trying to build the formula relevance of that function so that no day was treated differently other than it being Monday-Friday or Sat or Sun. I ended up doing much of what you suggested. My Saturday column formula is:

(Note I also have a Shift Days column to ignore Sat, Sun or entire weekends. This would keep the duration of schedule (start and stop dates) the same, but not calculate hours at all for those days if set.)

(Note I also have a Weekday column that basically just list what days are in the range. So if it's a 3 day duration starting with Friday it would result with "6, 7, 1". I use this to help calculate if Sat or Sun have values as shown below.)

=IF(Children@row = 0, IF(CONTAINS("7", Days@row), IF([Shift Days]@row = "No Sat", 0, IF([Shift Days]@row = "No Weekends", 0, (IF(AND(WEEKDAY(Start@row) <> 7, WEEKDAY(Finish@row) <> 7, WEEKNUMBER(Start@row) <> WEEKNUMBER(Finish@row)), ROUNDDOWN(NETDAYS(Start@row, Finish@row) / 7), ROUNDUP(NETDAYS(Start@row, Finish@row) / 7))))), 0), "")

My Sunday is like this but obviously changing the day from 7 to 1.

Then my M-F formula is basically ( =Duration - Sat column - Sun column)

-- Rather than actually referencing the Sat and Sun column in my M-F column I'm copying/pasting that formula minus the shift days column if statement. If the shift days ignore the weekends or one of the days, it wouldn't calculate the hours, but also wouldn't mess up the math for calculating the M-F values.

Genevieve P.

Hi @Speigel

Thanks for clarifying, and for posting your solution! It sounds like you've figured out what will work... if you're still having issues please feel free to post again and I'd be happy to help further.

Cheers,

Genevieve

SkiPatrolScott

@Genevieve P. What if you have multiple entries with the same date in your column [control date], but will have breaks between entries,

11/15/22, 11/15/22, 11/15/22

11/21/22, 11/21/22...

12/01/22, 12/01/22...

01/02/23, 01/02/23, 01/02/23 ;;

I want my summary sheet to count the number of control days, so the answer would be "4"

Genevieve P.

Hi @SkiPatrolScott

It sounds like this may be a different question than the current thread. If I'm understanding you correctly, you want to count the distinct days in one specific column, is that right?

If so, you can use COUNT(DISTINCT(COLLECT, like so:

=COUNT(DISTINCT(COLLECT([control date]:[control date], [control date]:[control date], <> "")))

If I've misunderstood, please feel free to post a new Question in the Formulas category with screen captures of your sheet set-up (blocking out sensitive data).

Cheers,

Genevieve

Debbie Sawyer

@Speigel & @Genevieve P.

Hi Both, dredging this thread up from a few years ago!

I would be really interested in seeing the formula you used to list the weekdays from a date range if you'd be willing to share?

I have a situation similar to the initial thread where I need to calculate #days holiday due within a range for people that work part time. ie from 7th Nov - 21st Nov how many Tues, Wed and Thurs are in the range.

I believe with the formula that lists the weekdays between the start and end dates, I might be able to create some sort of look up to work this out! :D

Any suggestions welcome! Thank you!

Debbie

Debbie Sawyer

Don't worry @Speigel & @Genevieve P. I have formed a workaround!

Would be cool to know it though for future reference maybe? Thank you!

Genevieve P.

Hey @Debbie Sawyer !
Apologies for missing this - I'm glad to hear you found a workaround 🙂