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/20225202/04/20225203/04/20225204/04/20220105/04/20220106/04/20220107/04/20220108/04/20220109/04/20220110/04/20220111/04/20220212/04/20220213/04/20220214/04/20220215/04/202202

Calculating Week Number for Financial Years

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

Workflow

Accepted answers

Christina_H

Try this

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

FabioP

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))

All comments

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

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

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

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.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335