Allocate the nightshift hours to the correct day

[Applicable88]

Hello,

I have a problem dealing with nightshift hours. My sample table looks like this: 

DatetimeStart	DatetimeFinish	Shift	Weekday	Mastercalendar	ORDERID
13.06.2021 22:55:01	13.06.2021 23:45:01	Nightshift	Sunday	13.06.2021	12345
13.06.2021 23:00:00	13.06.2021 23:45:01	Nightshift	Sunday	13.06.2021	12346
14.06.2021 01:00:00	14.06.2021 02:00:00	Nightshift	Monday	14.06.2021	12347
14.06.2021 04:00:00	14.06.2021 05:00:00	Nightshift	Monday	14.06.2021	12348
14.06.2021 06:00:00	14.06.2021 11:00:00	Dayshift	Monday	14.06.2021	12349
14.06.2021 11:00:00	18.06.2021 13:00:00	Dayshift	Monday	14.06.2021	12350
15.06.2021 14:30:00	15.06.2021 15:00:00	Lateshift	Tuesday	15.06.2021	12351
16.06.2021 15:00:00	16.06.2021 17:00:00	Lateshift	Wednesday	16.06.2021	12352
17.06.2021 11:00:00	17.06.2021 14:00:00	Dayshift	Thursday	17.06.2021	12353

The shift column is build very typically with if statment:

When hour is from 6 to 14, "Dayshift", 14 to 22 "Lateshift", 22 to 06, "Nightshift".

But there is also another condition, which I don't know how to solve. For example nightshift of Mondays starts actually on Sunday 22PM and last until Monday morning 6AM. And this is true for every following nightshift, where the starting time is actually in the evening of the night before. So for instance, when  I want to filter my data to Monday the 14th of June and want to count the orders or sum up the duration time of that nightshift, unfortunately it displays me the nightshift from Monday 12:00:00 -6AM plus the data of the same day Monday 22PM-24PM. So these two time ranges are not belonging together, since the hours of 22PM-24PM of that very same day actually are already belonging to the nightshift of Tuesday. For Monday the nightshift starts at Sunday 13th 22PM until Monday 14th 6:00AM.

What is best practice here? Aynone encounter same issue before?

Thank you very much in advance. 

Best. 

[MFelix · Accepted solution]

Hi @Applicable88 ,

Believe that for this case you need to create a new column with the correct date, in this case you need to check if the hour is higher than the 22 PM and if yes return one addtional day:

if Time.Hour([DatetimeStart]) >= 22 then [Mastercalendar] + #duration(1,0,0,0)else [Mastercalendar]

Now you should use this column to make your filtering. since you are no the query editor you can delete the other colunm from you table

[MFelix · Accepted solution]

Hi @Applicable88 ,

Believe that for this case you need to create a new column with the correct date, in this case you need to check if the hour is higher than the 22 PM and if yes return one addtional day:

if Time.Hour([DatetimeStart]) >= 22 then [Mastercalendar] + #duration(1,0,0,0)else [Mastercalendar]

Now you should use this column to make your filtering. since you are no the query editor you can delete the other colunm from you table

[Applicable88 · Accepted solution]

Hello @MFelix , yes I cam to the same conclusion whereas I think your right,  I could have save the smaller than 24 since after 24 its start at zero again:

if Time.Hour([StartTime])>=22 and Time.Hour([StartTime]) <= 24 then Date.AddDays([StartTime],1) else [StartTime]

Thank you.

Best.