Pay Period

[Chris Aca]

Our pay period includes the 25th of the current month
into the 24th of the following month. How do I group
dates so that when I slice my data by month, the
appropriate dates are aggregated? For example, I want to
aggregate all the data for the month of June. This
includes May 25th - June 24th.

Thanks for your help

 

[Allen Browne]

Group on:
DateSerial(Year([MyDate]), Month([MyDate]) - (Day([MyDate]) > 24), 1)

The expression:
(Day([MyDate]) > 24)
is True for 25th through 31st, else False.
In Access, True is -1 and False is 0.
Subtracting -1 from the month bumps the date into the following month.

 

[Chris Aca]

Thanks, Allen. Worked like a charm!

-----Original Message-----
Group on:
DateSerial(Year([MyDate]), Month([MyDate]) - (Day ([MyDate]) > 24), 1)

The expression:
(Day([MyDate]) > 24)
is True for 25th through 31st, else False.
In Access, True is -1 and False is 0.
Subtracting -1 from the month bumps the date into the following month.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Our pay period includes the 25th of the current month
into the 24th of the following month. How do I group
dates so that when I slice my data by month, the
appropriate dates are aggregated? For example, I want to
aggregate all the data for the month of June. This
includes May 25th - June 24th.

Thanks for your help

.