Calculating Week Number for Financial Years

[FabioP]

Dear All,

I'd like to use this solution but for 'Financial Year' where for example the first day of year is 2022-04-01.

Is it possible to change any part of the formula to start to count from April?

Ex.:

01/04/2022	52
02/04/2022	52
03/04/2022	52
04/04/2022	01
05/04/2022	01
06/04/2022	01
07/04/2022	01
08/04/2022	01
09/04/2022	01
10/04/2022	01
11/04/2022	02
12/04/2022	02
13/04/2022	02
14/04/2022	02
15/04/2022	02

[ShankerV]

Hi @FabioP 

I am not sure whether we can achieve this with the help of a formula.

But can be achieved with the help of a manually mocked up data set.

Then doing a join to get the Week easily.

Many thanks

Shanker V

[ShankerV]

Hi @FabioP 

Please find the expected output.

Many thanks

Shanker V

[ShankerV]

Hi @FabioP 

Step 1: Input

Step 2: 

Step 3: Creating the file for our reference.

Many thanks

Shanker  V

[Christina_H · Accepted solution]

Try this

Mod(ToNumber(DateTimeFormat([DateKey],"%W"))-ToNumber(DateTimeFormat(ToDate(ToString(DateTimeYear([DateKey]))+"-04-01"),"%W"))+51,52)+1

[ShankerV]

Hi @FabioP 

Step 4: Record ID tool

step 5:

IF [RecordID] = 1
THEN 1
ELSEIF MOD([RecordID],7)=1
THEN [Row-1:Week]+1
ELSE [Row-1:Week]
ENDIF

Step 6:

Expected output:

Many thanks

Shanker V

[ShankerV]

Hi @FabioP 

++One more information

Use the output tool and save the output in .xlsx format.

So that the same can be used as input file in future where you want to find the week for any dates.

Many thanks

Shanker V

[FabioP · Accepted solution]

I just put an IF to complete with 0 when the number is between 1 - 9 and create a new formula for who needs the week starting on Sunday:

Starting Mon

IIF(Length(ToString(Mod(ToNumber(DateTimeFormat([Date],"%W")) - ToNumber(DateTimeFormat(
ToString( DateTimeYear([Date])) + "-04-01","%W")) + 51, 52) +1)) = 1,

'0'+ ToString(Mod(ToNumber(DateTimeFormat([Date],"%W")) - ToNumber(DateTimeFormat(
ToString( DateTimeYear([Date])) + "-04-01","%W")) + 51, 52) +1),

ToString(Mod(ToNumber(DateTimeFormat([Date],"%W")) - ToNumber(DateTimeFormat(
ToString( DateTimeYear([Date])) + "-04-01","%W")) + 51, 52) +1))

Starting Sun

IIF(Length(ToString(Mod(ToNumber(PadLeft(ToString(FLOOR(DateTimeDiff(DateTimeAdd([Date],4 - Switch(DateTimeFormat([Date],"%a"),7,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6),"days"),DateTimeParse(ToString(ToNumber(DateTimeFormat(DateTimeAdd([Date],4 - Switch(DateTimeFormat([Date],"%a"),7,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6),"days"),"%Y")))+'-01-01',"%Y-%m-%d"),"days")/ 7)+ 1),2,"0"))-ToNumber(DateTimeFormat(ToDate(ToString(DateTimeYear([Date]))+"-04-01"),"%W"))+51,52)+1)) = 1,

'0'+ToString(Mod(ToNumber(PadLeft(ToString(FLOOR(DateTimeDiff(DateTimeAdd([Date],4 - Switch(DateTimeFormat([Date],"%a"),7,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6),"days"),DateTimeParse(ToString(ToNumber(DateTimeFormat(DateTimeAdd([Date],4 - Switch(DateTimeFormat([Date],"%a"),7,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6),"days"),"%Y")))+'-01-01',"%Y-%m-%d"),"days")/ 7)+ 1),2,"0"))-ToNumber(DateTimeFormat(ToDate(ToString(DateTimeYear([Date]))+"-04-01"),"%W"))+51,52)+1),

ToString(Mod(ToNumber(PadLeft(ToString(FLOOR(DateTimeDiff(DateTimeAdd([Date],4 - Switch(DateTimeFormat([Date],"%a"),7,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6),"days"),DateTimeParse(ToString(ToNumber(DateTimeFormat(DateTimeAdd([Date],4 - Switch(DateTimeFormat([Date],"%a"),7,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6),"days"),"%Y")))+'-01-01',"%Y-%m-%d"),"days")/ 7)+ 1),2,"0"))-ToNumber(DateTimeFormat(ToDate(ToString(DateTimeYear([Date]))+"-04-01"),"%W"))+51,52)+1))

[Christina_H]

@FabioP Take a look at this page for all the date-time specifiers:

DateTime Functions | Alteryx Help

You can substitute %U for %W to get weeks starting on a Sunday.