Different dates in one week turned into the Monday of that week

[star_EF]

Hi,

Is there a way I can create a formula that will determine the Monday for all these dates and return just one date. For example, in this table it's looking up data from another table, in this case the Monday for this set of dates is the 12th. 

How con I accomplish this? I need it to create a "end of week".. which I already have the formula for that. Please help.

[Dan_Montoya · Accepted solution]

@star_EF I broke this up into 3 fields so you can see the logic in each step. 

1. created a field called earliest date which is a rollup of all the dates you have linked. It uses the MIN(values) to pick the earliest date.
2. Then use the day of the week field using WEEKDAY function to find out which day of the week the earliest date is (SUN - SAT, 0-6).
3. Then create your if field.  If the min day is Monday (WEEKDAY = 1), then use that Monday, if not subtract the weekday from 1 (to get a negative number to DATEADD to the earliest date). This subtracts the number of days the earliest date is to find the Monday.
4. 

[star_EF]

@Dan_Montoya Thank You!! I used your roll up field idea, and instead of using the other fields you showed I just used the formula I had already, which just gets me the Monday based on the date provided on "earliest day" field. Thanks again