Help

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

Solved
Jump to Solution
292 0
cancel
Showing results for 
Search instead for 
Did you mean: 
star_EF
4 - Data Explorer
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.

Screenshot 2024-02-12 at 9.49.51 PM.png

1 Solution

Accepted Solutions
Dan_Montoya
Community Manager
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. Screenshot 2024-02-12 at 8.32.18 PM.pngScreenshot 2024-02-12 at 8.32.34 PM.pngScreenshot 2024-02-12 at 8.32.26 PM.png

See Solution in Thread

2 Replies 2
Dan_Montoya
Community Manager
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. Screenshot 2024-02-12 at 8.32.18 PM.pngScreenshot 2024-02-12 at 8.32.34 PM.pngScreenshot 2024-02-12 at 8.32.26 PM.png

@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