You want to get ISO weeknumber? I.e. the 1st week of year is the one with 1st Thursday of this year in it, a week starts with Monday, and it is always 7 days long.
One way is to go like explained here https://stackoverflow.com/questions/...rd-week-number
Then you can try to port this Excel formula into Access:
Code:
=1+INT((SomeDate-DATE(YEAR(SomeDate+4-WEEKDAY(SomeDate+6));1;5)+WEEKDAY(DATE(YEAR(SomeDate+4-WEEKDAY(SomeDate+6));1;3)))/7)
, or the same formula ported into MS SQL Server as an UDF (I havent ported it into Access, as for years now I use Access only as FrontEnd app):
Code:
USE [utilities]
GO
/****** Object: UserDefinedFunction [dbo].[udf_IsoYYYYWW] Script Date: 18.12.2020 9:36:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Arvi Laanemets
-- Create date: 07.06.2012
-- Description: Returns the ISO week of a date in format yyyyww
-- =============================================
ALTER FUNCTION [dbo].[udf_IsoYYYYWW]
(
-- Add the parameters for the function here
@Date datetime
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @IsoWeek int
-- Add the T-SQL statements to compute the return value here
SET @ISOWeek =
(
1 +
Datediff(
day,
0,
@Date -
utilities.dbo.udf_Date(
Year(@Date+4-utilities.dbo.udf_WeekWd(@Date+6,7)),
1,
5
) +
dbo.udf_WeekWd(
utilities.dbo.udf_Date(
Year(@Date+4-utilities.dbo.udf_WeekWd(@Date+6,7)),
1,
3
),
7
)
)/7
)
SET @ISOWeek =
(
YEAR(@Date) +
(CASE WHEN Day(@Date)<4 AND @ISOWeek>=52 THEN -1 WHEN Day(@Date)>=28 AND @ISOWeek=1 THEN 1 ELSE 0 END)
)*100 +
@ISOWeek
-- Return the result of the function
RETURN @IsoWeek
END