Determine the calendar date of a Monday in any given week?

1

Hey! I can’t seem to find a simple way to determine a date of a Monday in a given week. In this case, I’m trying to find the date of Monday in any given WeekNumber.

The closest I’ve come so far is Today().WeekDay() (which is 6 as today is a Friday, the 6th day of the week). I was hoping I could do something like Today().WorkWeek().WeekDay(2) and have 2/11/2019 returned, but that doesn’t work as WeekDay() expects a date, not a number.

Any ideas?

2

Would something like this work?
Today() - (Today.weekday() - 2 )

3

Never mind, read your question wrong - this will give you the Monday for this week…

4

So, I guess a bit convoluted, but check out the example in this doc:

Basically if we know what week day the first day of the year was, we can work out what the date of the Monday of the first week of the year was. We can then add multiples of 7 to find the Monday of any given week.

Joe

3 Likes
5

Hi folks, you solve this problem using the DatettimeTruncate() formula which, given any date or date time value, will round the date down to the given interval (year, month, day, hour, etc.). To find the 1st day of the week (Sunday by convention) you can write:
DateValue.DatetimeTruncate('week').

To find the Monday of that week, you add on day given you the formula:
DateValue.DatetimeTruncate('week') + 1.

You can validate your results using WeekdayName:
WeekdayName(DateValue.DatetimeTruncate('week') + 1)

So, given any week number, for a given year you need to find the first date of the year: Date(<year value>, 1, 1). You can then use DatetimeTruncate() to find the Monday date and then offset that date by the given number of weeks (7 days per week). I’ve attached a small example below.

Hope that helps,
Nigel.

9 Likes
6

Thanks for this.

I think it doesn’t work if we use ISOWeekNumber though, i.e. week starts on Monday.
DateTimeTruncate defaults to Sunday as first day of week so it will give the wrong result.

7

hi @Michael_RnW you might have look at the solution of @Federico.Stefanato

I hope it helps, Christiaan

1 Like
8

Thanks @Christiaan_Huizer !
I did another workaround for my case, but appreciate the link.