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
ronrsnfld
Super User
Super UserIf 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
ronrsnfld , sorry i marked as solution but it did not work. i expect the yellow circled to be 04/06/2025
- ronrsnfld
Your adaptation will not work.
You must use the formula I posted, with all the parentheses.
Since you removed the outer set of parentheses, you are subtracting 6 hours from just the date, and not from the datetime.
Correct formula: Date.From(([Date] & [Time])-#duration(0,6,0,0)) ^ ^ Your formula: Date.From([Date] & [Time])-#duration(0,6,0,0)