Forum Discussion
Change date by time
Hello all.
I have two columns "Date" and "Time". I need to add a third column with the date "New Date".
The date should change not at 00:00 but at 06:00.
Example:
Weighing date March 1, 2023, time 11:39, "New date" March 1, 2023
Weighing date March 2, 2023, time 04:56, "New date" March 1, 2023
Thank you in advance for your cooperation.
Hi Anonymous
you may try
New Date =
DATEVALUE (
'Table'[2nd Weight Date] + 'Table'[2nd Weight Time]
- TIME ( 6, 0, 0 )
)
4 Replies
Hi Anonymous
you may try
New Date =
DATEVALUE (
'Table'[2nd Weight Date] + 'Table'[2nd Weight Time]
- TIME ( 6, 0, 0 )
)I believe I understand this as for any times before 6am, the new date column should show the previous date, for times of 6am or later it should show the 2nd Weight date. Because this is a static property of the 2nd weight, and the 6am changeover time doesn't change, I suggest doing this in PowerQuery.
Here's an expression to do this with a custom column in PowerQuery:
if Time.Hour([2nd Weight Time]) >= 6 then [2nd Weight Date] else Date.AddDays([2nd Weight Date], -1)
Here's the equivalent in DAX for a calculated column, but this would be less performant, so try the above first:
New Date =IF(HOUR([2nd Weight Time]) < 6,'Weight Date and Times'[2nd Weight Date] - 1,'Weight Date and Times'[2nd Weight Date])
