Week number for start of 2021

[NurulScare]

Hi, I have table and need to calculate WEEK NUMBER based on date. however when enter year 2021, My week 1 doesn't start with 27/12/2020.
date 27/12/2020 become week 53. I want my week 1 start with 27/12/2020 (Sunday is 1st day)
my formula is:

IIf([Rejection Year]=2019,Int((([Rejection Date]-DateSerial(2018,12,30))+6)/7)+Abs(Weekday([Rejection Date],1)=Weekday(DateSerial(2018,12,30),1)),Int((([Rejection Date]-DateSerial(2019,12,29))+6)/7)+Abs(Weekday([Rejection Date],1)=Weekday(DateSerial(2019,12,29),1)))

 

[theDBguy]

Hi. Can't test this right now, but what do you get if you use the Format() or DatePart() function?

Sent from phone...

 

[NurulScare]

Hi. Can't test this right now, but what do you get if you use the Format() or DatePart() function?

Sent from phone...

hi.. but format and datepart() cannot use at table. thats y i have to put it long wayyyyy..

 

[gemma-the-husky]

there is a syntax in the date functions to change the default week numbers to a different calendar, based on the start date of your week, and I think the start date of week 1. You need to look at the help carefully.