The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

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

Topic Labels: Data Formulas
Solved
652 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

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.

1 Solution

Accepted Solutions
Community Manager

@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.
2 Replies 2
Community Manager

@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 - Data Explorer

@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