Forum Discussion
Derive 24 hour shift date. Overlapping calendar date
I need a query to derive shift date from my data table. The expected Shift date is what i have highlighted in yellow, column shift date.
If you subtract from all the datetimes, the results will be the same, and the equation is simpler.
eg. 4/6/2025 6AM - 6hrs => 4/6/2025
5/6/2025 5:59AM - 6hr => 4/6/2025
Cookistador
Super User
Super UserI'm very sorry, You are a right, we are on Power query board 🙂
You can achieve that with the following M code for a custom culomn
let
timeValue = [Time],
dateValue = [Date],
shiftStart = #time(6, 0, 0),
shiftEnd = #time(17, 59, 59),
shiftDate = if timeValue >= shiftStart and timeValue <= shiftEnd then
dateValue
else
Date.AddDays(dateValue, -1)
in
shiftDate