I have a formula that computes pay periods (thank you Brian!). I need to add a number to the front of the value that will allow me to sort by the field. Right now its sorting alpha numerically, which doesn't work so well on calendar dates. Ideally a prefix in front of each pay period indicating the year and the number of the payperiod. We do payroll every two weeks and need to group by those periods.
Its giving an output that looks like "October 29, 2023 to November 5th" which is the two start weeks for that pay period. I'd like to see it like this "202322 - October 29th, 2023 to November 5th" where the 22 would be the 22nd two-week payroll for this year. Next payroll would be "202323 - November 6th to......." In January it would go "202401 - " followed by the weeks of the pay period.
Here is the formula as it currently stands.
IF( CONTAINS(TEXT(WEEK(DATEADD([Date], 1, "days"))/2), ".5"),
CONCAT(DATEADD(DATEADD([Date],
-NUMBER(
CASE(
LEFT(DATE_FORMAT([Date], "long"), 3),
"Mon", "1",
"Tue", "2",
"Wed", "3",
"Thu", "4",
"Fri", "5",
"Sat", "6",
"Sun", "0")
),"days"), -1, "weeks"), " โ ", DATEADD([Date],
-NUMBER(
CASE(
LEFT(DATE_FORMAT([Date], "long"), 3),
"Mon", "1",
"Tue", "2",
"Wed", "3",
"Thu", "4",
"Fri", "5",
"Sat", "6",
"Sun", "0")
),"days")),
CONCAT(DATEADD([Date],
-NUMBER(
CASE(
LEFT(DATE_FORMAT([Date], "long"), 3),
"Mon", "1",
"Tue", "2",
"Wed", "3",
"Thu", "4",
"Fri", "5",
"Sat", "6",
"Sun", "0")),"days"), " โ ", DATEADD(DATEADD([Date],
-NUMBER(
CASE(
LEFT(DATE_FORMAT([Date], "long"), 3),
"Mon", "1",
"Tue", "2",
"Wed", "3",
"Thu", "4",
"Fri", "5",
"Sat", "6",
"Sun", "0")),"days"), 1, "weeks")))