18
Views
1
Comments
Third day of the week

Hello,

Being that the days of the week are between 0 to 6 where 0 is Sunday and 6 Saturday, I need to know the third day of the week, starting from a certain date.


For example: date = 2020-12-04; the third day of the week, in this case it is 2020-12-02

Someone can help me?

Thanks a lot,

Neide

mvp_badge
MVP
Rank: #71

Hello Neide.

You said "starting from a certain date" but are moving backwards?


By your example it is simply:

AddDays(Date,TargetDay-DayOfWeek(Date))

AddDays(#2020-12-04#,3-5) = AddDays(#2020-12-04#,-2) = #2020-12-02#


Assuming you want "the first x after this date":

If(DayOfWeek(Date)<TargetDay,
    AddDays(Date,Target-DayOfWeek(Date)),
    AddDays(Date,Target-DayOfWeek(Date)+7)
)

The seven is to avoid dates in the past.

AddDays(#2020-12-04#,3-5+7) = AddDays(#2020-12-04#,5) = #2020-12-09#