Finding specific days of a week within date range

reporting_95

New Member
Joined
May 7, 2019
Messages
30
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I have a number of date ranges and what I would like to do is find specific days of the week within the range. For example, I have the date range 6/1/19-8/1/19 and I'd like to know the dates for the Mondays and Wednesdays within that date range. I'm looking to associate the name of the day and date to a list of events that meet on specific days of the week, but only supply the date range. Once I have the names of the specific days I'm looking for, I would be able to quickly filter by that day and see what event is taking place on the day and date. I tried the =Networkdays.intl formula but that gives me a count as opposed to the actual day and date. Is there such a formula that can help me accomplish this?

Please kindly advise.

Thanks!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi,

Assuming that your date range is specified in cells A1 & A2, e.g.
A1 = 01/06/2019
A2 = 01/07/2019

You can use the following Array formula (Ctrl+Shift+Enter) in cell C1:
Code:
=IFERROR(SUMPRODUCT(SMALL(IF(WEEKDAY($A$1+ROW(INDEX(A:A,1):INDEX(A:A,$A$2-$A$1+1))-1,2)=[COLOR=#ff0000]{1,3}[/COLOR],$A$1+ROW(INDEX(A:A,1):INDEX(A:A,$A$2-$A$1+1))-1),ROW())),"")

This should show you the first Monday/Wednesday in this date range. Afterwards you need to simply drag the formula down a few cells to see the remaining dates.
I have highlighted the part which is responsible for selecting Monday (1) and Wednesday (3).

I hope it helps.
 
Upvote 0
My apologies for the late response. Looks like this works. Thanks again!!
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top