Knock a day off a date field depending on the time

[benmillea]

I'm working on some data where the days are split in to 3 separate shifts:

06.00 - 14.00 = Early shift

14.00 - 22.00 = Late shift

22.00 - 06.00 = Night shift

When I am analysing what happened during the night shift, the data cuts between two separate dates. What I want to be able to do is assign the date for any records where the time (from my "Feeding Date Time" field) is between 00.00.00 and 05.59.59 to the previous date (when the shift actually started).

The query that I have set up for my "Date" field is as follows:

if [Feeding Date Time]>="00:00:00" and [Feeding Date Time] <"06:00:00"
then DateTimeAdd([Date],-1,"days")
else [Date]
endif

But this doesn't seem to be working for me. 

Please can somebody advise?

Thank you

[perin · Accepted solution]

Hello @benmillea ,

You can use ToNumber with DateTimeHour functions to achieve what you want.

See an example attached.

Best,

Gustavo.

[ChrisTX]

Which data type is [Feeding Date Time]?

Can you post a sample workflow to show what is not working?

[benmillea]

Thank you very much - that's worked for me now