Calculating Week from day and time

[Aditya Singh 3]

I want to calculate the week number for my date column from Sunday 5 AM to next Sunday 5 AM. The week function is giving me the week number but how to set the defined time in calculation.

Thank you!

[Fabian Duerr]

Could you please add an example with your input and the expected output Thx

[Aditya Singh 3]

I have a date column with below values and want to get the week number

 

Date

 

 

 

 

 

1/1/2022 7:35:23 AM

 

 

 

 

 

 

 

1/2/2022 10:35:23 AM

 

 

1

 

 

 

 

1/3/2022 5:35:23 AM

 

 

1

 

 

 

 

1/4/2022 7:35:23 AM

 

 

1

 

 

 

 

1/5/2022 7:35:23 AM

 

 

1

 

 

 

 

1/6/2022 7:35:23 AM

 

 

1

 

 

 

 

1/7/2022 7:35:23 AM

 

 

1

 

 

 

 

1/8/2022 7:35:23 AM

 

 

1

 

 

 

 

1/9/2022 7:35:23 AM

 

 

1

 

 

 

 

1/10/2022 4:35:23 AM

 

 

2

 

 

 

 

1/10/2022 7:35:23 AM

 

 

2

 

 

 

 

1/11/2022 7:35:23 AM

 

 

2

[Fabian Duerr]

You can still use the week() function. The return of this function will depend on your regional setting. Therefore you have to add a time offset to your original column. The following calculation works for me. You might have to adjust it for your regional settings:

Week(DateAdd('hour', -5, [your.column])) - 1