Can you group dates in a pivot table by the 1st-15th and from 16th to EOM?

[eMHDfNhuwLhQCzJ]

Hello.

I am trying to group dates in a pivot table by pay period.

The pay periods extend from the 1st of each month to the 15th for the first half and then from the 16th to whatever the last day in the month is.

In the date grouping options I can only find the ability to group dates by the number of days which won't work. January ends up being grouped:
Jan 01-15
Jan 16-30
Jan 31-Feb 14

It should simply be grouped:
Jan 01-15
Jan 16-31

 

[Alex Blakenburg]

You can probably do it if you load it into the datamodel and use Power Pivot and DAX.
For a normal pivot I always just used a helper column in the datasource for grouping. Its a simple if statement.
I would probably still use the month from the date in a column and something like this in a second column to ensure it sorts correctly.

Book5
	A	B
1	Date	Mth Group
2	1/01/2022	1st Half
3	4/01/2022	1st Half
4	7/01/2022	1st Half
5	10/01/2022	1st Half
6	13/01/2022	1st Half
7	16/01/2022	2nd Half
8	19/01/2022	2nd Half
9	22/01/2022	2nd Half
10	25/01/2022	2nd Half
11	28/01/2022	2nd Half
12	31/01/2022	2nd Half
13	3/02/2022	1st Half
14	6/02/2022	1st Half
15	9/02/2022	1st Half
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=IF(DAY(A2)<=15,"1st Half","2nd Half")

 

[eMHDfNhuwLhQCzJ]

Ah. I was hoping to somehow do it with pivot tables only but this will work perfectly thank you very much.

 

[Alex Blakenburg]

You're welcome. Sorry there isn't a pure pivot table solution.
At least this method works for multiple scenarios, especially if you throw in a grouping table and use Vlookup with the True (approximate match) parameter.