Find first Monday from a list of dates

[Grimace]

Good morning

I have a large report that I receive which contains dates over anywhere up to a 5-6 week period, and the dates will vary every time I receive it including the first date of any data.

I want to cut it so I am reporting from the first Monday included, and then moving forward for a 4 week period.

I am looking for a formula that can give the first Monday, regardless of which day of week is the minimum in the data (ie if the very first date of any data is a Thursday, it needs to return the following Monday. If the first date is a Monday it returns that value).

Any help would be greatly appreciated.

A sample sheet is attached.

[Phuocam]

Try this ...

=WORKDAY.INTL(E1+1,-1,"0111111")

[bebo021999]

First Monday of data range:

=MIN(A2:A140)+CHOOSE(WEEKDAY(MIN(A2:A140)),6,0,1,2,3,4,5)

First Monday of the very first date:

=A2+CHOOSE(WEEKDAY(A2),6,0,1,2,3,4,5)

[protonLeah]

If A2 = 5/25/2017 and somewhere below there is a Monday date:
=INDEX(A:A,MATCH(2,WEEKDAY(A:A),0))
if Monday is the 2nd day of the week.

[leelnich]

Or this:
=MIN(IF(WEEKDAY(A2:A140)=2,A2:A140))

OOPS, forgot to say "This is an ARRAY FORMULA". You must press CTRL + SHIFT + ENTER after pasting it in.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Grimace]

Thank you ... the 2nd option worked how I need it.

May I ask how the choose function works. I have not used it before and like to learn, not just ask for a solution.

I can see it relates to days of week, but not sure how it "chooses".

Thanks in advance
Darren

[protonLeah]

Weekday() returns the number 1 - 7 of the day of the week of a date, Sun = 1. Choose picks the item in that position in the following list. E.g., Wed = 4, so choose returns the number in the 4th position (i.e., 2)

[leelnich]

first monday of data range:

=MIN(A2:A140)+CHOOSE(WEEKDAY(MIN(A2:A140)),6,0,1,2,3,4,5)

first monday of the very first date:

=a2+choose(weekday(a2),6,0,1,2,3,4,5)

To get the first Monday following the earliest date, shouldn't this be?:
=MIN(A2:A140)+CHOOSE(WEEKDAY(MIN(A2:A140)),1,0,6,5,4,3,2)