I just had a customer ask me to write a formula for determining the shift from a datetime value. Their shifts run:

07:00 to 14:59 (1st)
15:00 to 22:59 (2nd)
23:00 to 06:59 (3rd)

This is pretty common, including a shift that crosses midnight. The shift formula would look like this:

if Hour({tbl.DateTime}) < 7 then '3rd' else
if Hour({tbl.DateTime}) < 15 then '1st' else
if Hour({tbl.DateTime}) < 23 then '2nd' else '3rd'

But invariably the next request is to group the records by date and then by shift. But the first hour of the third shift has a different date so it will be grouped with the 7 hours the night before, rather than the 7 hours that follow. Of course that makes no sense, since we want the 3rd shift to be 8 consecutive hours, despite the fact that they are (technically) on different days.

You resolve this by writing another formula called “Shift Date”, and there are two ways to write it.  You choose, depending on how you view the date of the third shift.  Lets say our third shift runs from Monday night to Tuesday morning. I think most users would consider the entire third shift an extension of Monday, since that is when the shift started. Also it is the last shift of the series that starts on Monday. But I have seen cases where the users consider the third shift part of Tuesday since most of it is on Tuesday. Here are both formulas

If you are in the first category your formula would look like this to shift 7 hours back to the previous date:

if Hour({tbl.DateTime}) < 7
then Date({tbl.DateTime})-1
else Date({tbl.DateTime})

If you are in the second category, your formula would look like this to move the last hour to the next date:

if Hour({tbl.DateTime}) = 23
then Date({tbl.DateTime}) + 1
else Date({tbl.DateTime})