Blog

Formula - How to get the dates for the current week

Do you need to retrieve the days of the current week? If so, this post shows the formula to carry out this task.

There are scenarios where it's necessary to calculate the days of the current week. The use case scenarios might be to set default values or to validate input values.

Here are the snippets of formula to carry out this task.

Get Monday of the current week

The basis of the calculations depends on the formula below. This returns the Monday of the current week.

DateAdd(Today(),
         -(Weekday(Today(),StartOfWeek.MondayZero)),
         TimeUnit.Days
)
The screenshot below highlights the result. At the time of writing (Thursday 23rd March 23), the Monday for the current week is Monday 20th March.

Get Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday of the current week

Based on the logic above, we can simply add additional days to derive the dates for the remaining days of the week. For example, we would add 1 day to the above to find the date for the current Tuesday, 2 days for Wednesday, 3 days for Thursday, and so on. 

// Get Tuesday for the current week
DateAdd(Today(),
        -(Weekday(Today(),StartOfWeek.MondayZero)) + 1,
        TimeUnit.Days
)

// Get Wednesday for the current week
DateAdd(Today(),
        -(Weekday(Today(),StartOfWeek.MondayZero)) + 2,
        TimeUnit.Days
)

// Get Thursday for the current week
DateAdd(Today(),
        -(Weekday(Today(),StartOfWeek.MondayZero)) + 3,
        TimeUnit.Days
)

// Get Friday for the current week
DateAdd(Today(),
        -(Weekday(Today(),StartOfWeek.MondayZero)) + 4,
        TimeUnit.Days
)

// Get Saturday for the current week
DateAdd(Today(),
        -(Weekday(Today(),StartOfWeek.MondayZero)) + 5,
        TimeUnit.Days
)

// Get Sunday for the current week
DateAdd(Today(),
        -(Weekday(Today(),StartOfWeek.MondayZero)) + 6,
        TimeUnit.Days
)

Conclusion

In cases where it's necessary to retrieve the days of the current week, we can use the formula that's highlighted in this post. .
Previous
Next
Related posts