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
@star_EF I broke this up into 3 fields so you can see the logic in each step.
- 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.
- 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).
- 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.
@star_EF I broke this up into 3 fields so you can see the logic in each step.
- 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.
- 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).
- 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.
