Sale Date within date range results in "x" Quarter

[Rich S]

Happy holidays, everybody.

I'm trying to create a custom function where given "x" month/date of a product's sales date it results in the quarter it was sold, irrespective of year. How can I code this so it only analyzes the month/day and not the year so I don't have to annually input the year?

Let (

_Date_ = SaleDate //Date Field ;

Case (

_Date_ ≥ GetAsDate ( "9/1/2022" ) and _Date_ < GetAsDate ( "12/1/2022" ) ; "Q1" ;

_Date_ ≥ GetAsDate ( "12/1/2022" ) and _Date_ < GetAsDate ( "3/1/2023" ) ; "Q2" ;

_Date_ ≥ GetAsDate ( "3/1/2023" ) and _Date_ < GetAsDate ( "6/1/2023" ) ; "Q3" ;

_Date_ ≥ GetAsDate ( "6/1/2023" ) and _Date_ < GetAsDate ( "9/1/2023" ) ; "Q4" 

     )

)

 

Cheers,

Rich

Edited December 20, 2022 by WF7A
Grammar Police

[comment]

Apparently your 1st quarter starts on September 1, so you could do:

"Q" & Div ( Mod ( Month ( SaleDate ) - 9 ; 12 ) ; 3 ) + 1

 

[Rich S]

MANY thanks, but I'm having trouble understanding how the calc works. Am I botching the Order of Operations, or...?

"Q" & Div ( Mod ( Month ( SaleDate ) - 9 ; 12 ) ; 3 ) + 1

Ex: SaleDate = 12/10/2022

"Q" & Div ( Mod ( 12 ) - 9 ; 12 ) ; 3 ) + 1    // Month ( SalesDate ) = 12

"Q" & Div ( Mod ( 3 ; 12 ) ; 3 ) + 1    // Mod (12) - 9 = 3/12 )   .25?

"Q" & Div ( .25 ) ; 3 ) + 1  // Div .25/3 = .08 with a remainder of 1

"Q" & Div ( 1 ) + 1 = Q2  ?

[comment]

41 minutes ago, WF7A said:

I'm having trouble understanding how the calc works.

Here's a table showing what happens at each step, for each possible value of Month(), starting with September:

Month ( SalesDate )

9	10	11	12	1	2	3	4	5	6	7	8

Month ( SaleDate ) - 9

0	1	2	3	-8	-7	-6	-5	-4	-3	-2	-1

Mod ( Month ( SaleDate ) - 9 ; 12 )

0	1	2	3	4	5	6	7	8	9	10	11

Div ( Mod ( Month ( SaleDate ) - 9 ; 12 ) ; 3 )

0	0	0	1	1	1	2	2	2	3	3	3

As you can see, any month in the first quarter will now have the value of 0, the next 3 months will return 1, and so on. All that remains is to add 1 and prepend the Q character to get the expected Q1 to Q4 numbering.

---
Conceptually, there are two steps taking place here: 

1. Renumber the months so that September is moved to the beginning of the year;
2. Divide them into groups of 3. 

 

Edited December 20, 2022 by comment

[Tpaairman]

If each quarter is only dependent on the month itself, then try the following:

Case ( Month ( Date ) < 4; "Q1" ;Month ( Date ) ≥ 4 and Month ( Date ) ≤ 6; "Q2"; Month ( Date ) ≥ 7 and Month ( Date ) ≤ 9; "Q3" ; Month ( Date )  > 9; "Q4" )

Edited March 19, 2023 by Tpaairman

[comment]

[groan]