Pay Period

  • Thread starter Thread starter Chris Aca
  • Start date Start date
C

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
 
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.
 
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
Click to expand...


.
Click to expand...
 
Back
Top