Display Current Week Date Range for particular date

[thead]

Hi,

We have a date field, and would like to show in Calculated Field from which date to which date this week is. For example, if the date is in 1st week of February 2022, we want to show '01.02.2022 - 06.02.2022'.
Another example:
date: '8 March 2022', calculated value: '07.03.2022 - 13.03.2022'

Is this possible?

[April]

Hi Thead,

Yes that is possible. First you will need to use the function "DATEPART(dw, [@field:DateField])" which will return integer from 1 to 7, based on which day of the week it is. 1 is Monday, 7 is Sunday.

When you have this value, you can use it in a formula in the next way, "[@calcfield:1]" will be the result of "DATEPART(dw, [@field:DateField])":

Calculate Monday in a given week: "DATEADD(day, -[@calcfield:1]+1, [@field:DateField])"
Calculate Sunday in a given week: "DATEADD(day, 7-[@calcfield:1], [@field:DateField])"

Altogether, without using additional fields, everything can be summed up in one Calculated Field with formula:

'Week from' + space(1) +
CONVERT(VARCHAR, (Dateadd(day, -Datepart(dw, [@field:DateField])+1, [@field:DateField])),101)
+ space(1) +
'to' + space(1) +
CONVERT(VARCHAR, (Dateadd(day, 7-Datepart(dw, [@field:DateField]), [@field:DateField])),101)

 

Hope this helps.

[SunakoChan]

Hi @thead,

This article guide me on my formulas:

- https://howto.caspio.com/function-reference/
- https://www.w3schools.com/sql/func_sqlserver_convert.asp

I hope this help you too.