Get week number within a quarter

[Suppu]

Hi,

How can I get a week number within a quarter.

For example

Date	Week in Quarter
4/3/2021	1
4/30/2021	5
5/7/2021	6
12/20/2021	13
6/20/2021	13
6/30/2021	14
9/14/2021	12

[jdunkerley79 · Accepted solution]

Slightly twisted but:

ToNumber(DateTimeFormat([Date],"%U"))
- ToNumber(DateTimeFormat(
   Left([Date],5) +
   PadLeft(ToString(Floor((DateTimeMonth([Date])-1)/3)*3+1),2,'0')
   + '-01', "%U"))
+ 1

should do it.

DateTimeFormat will get the week number. The second one works out the week number at the start of the quarter. Finally adding a 1 to ensure first week is 1

[mceleavey]

Hi @Suppu ,

I'm not sure I totally understand, but I think you need the week number from the beginning of the fiscal year, correct?

So, for example, your first date provided is 3rd April 2021 (I assume it's in the US format), and you've provided the week as 1, so I assume the first quarter starts 1st April.

I've built it on this assumption:

I converted the date then worked out the week number off-set by 12 (the first 12 weeks of the calendar year:

(if tonumber(datetimeformat([DateTime_Out],"%W"))<=12 then tonumber(datetimeformat([DateTime_Out],"%W")) +53 else tonumber(datetimeformat([DateTime_Out],"%W")) endif)-12

Hope this helps,

M

[Suppu]

Hello,

Thank you for your answer . Could you please break down below part so that I can understand :

PadLeft(ToString(Floor((DateTimeMonth([Date])-1)/3)*3+1),2,'0')+'-01', "%U"))

[Suppu]

Hello,@mceleavey

I need the week number in a quarter . Jan-Mar 13/14 week. If I put in a date then I need to get week number in the quarter so essentially it will differ from a normal week number (which has 53 max) form April onwards. So 1st April will be week 1 then 1st July will be 1 then 1st October and so on. I have provided a sample table .

Kindly reply in case I can provide more information.

Supreet

[jdunkerley79 · Accepted solution]

The expression 'PadLeft(ToString(Floor((DateTimeMonth([Date])-1)/3)*3+1),2,'0')'

- takes the month of the date (DateTimeMonth([Date]))

- it then maps it to a quarter (0-3) by shifting back by 1 and dividing by 3 and then taking the floor (Floor((month-1)/3))

- next it turns this into the month number (1, 4, 7, 10) for the start of the quarter (quarter*3+1)

- finally it converts to a string and puts a leading 0 in front of it (PadLeft(month,2,'0'))

Hope that makes sense

[jdunkerley79 · Accepted solution]

Failed to attach my example workflow.

So have attached here

[atcodedog05 · Accepted solution]

Hi @Suppu 

Amazing approach by @jdunkerley79 I guess I might have ended with a similar approach. Here is the breakdown of the formula. I have broken down into mutiple formulas for your understanding.

Edit: I can see that @jdunkerley79  has already provided an explanation you can use mine for addtional reference 😅

%U gives yearly week number.

If you are still facing any issues let me know.

Hope this helps : )

[Suppu]

Hi @jdunkerley79,@atcodedog05, 

Many Many thanks for the solution and the detailed explanation.

It helped me a lot.

Supreet

[atcodedog05]

Happy to help : ) @Suppu 

Cheers and have a nice day!