Power Query Date Field to Subtract 4 Hours

[legalhustler]

In Power Query, I have a Date field and would like to subtract 4 hours from each date/time value in a custom column. For example, I want to subtract 4 hours from 2/7/2018 2:44:12 AM which should return 2/6/2018 10:44:12 PM. How can I do this? What function do I need to use in the custom column to get this result?

 

[Norie]

Try this, where DateTime is the name of your date/time field.

[DateTime]-DateTime.From(#time(2, 0, 0))

 

[Tyler Hilton]

Maybe try this formula, with [DateTime] being your DateTime Column:

#time(if Time.Hour([DateTime]) >= 4 then Time.Hour([DateTime]) else Time.Hour([DateTime])+24-4,Time.Minute([DateTime]),Time.Second([DateTime]))

Cheers!

 

[Norie]

Oops, of course the 2 should be a 4.

[DateTime]-DateTime.From(#time(4, 0, 0))

 

[legalhustler]

Oops, of course the 2 should be a 4.

[DateTime]-DateTime.From(#time(4, 0, 0))

My original [DateTime] field is data type Date/Time, once I used your formula it gave me results in this format 43137.15:44:13 (for a date/time of 2/7/2018 11:44:12 PM) and the data type shows as ABC/123, when I tried to change the data type to Date/Time or Date it gives me an error saying "We couldn't convert to DateTime."

Can you please advise.

Thanks!

 

[Norie]

I had that problem too and tried to format the date/time 'properly' within Power Query but didn't have any luck.

Mind you when I loaded the data back into Excel the new column did contain date/time values and I was able to format as required.

I'll have another go at the formatting within Power Query and get back to you - hopefully it's something simple.

 

[legalhustler]

Maybe try this formula, with [DateTime] being your DateTime Column:

#time(if Time.Hour([DateTime]) >= 4 then Time.Hour([DateTime]) else Time.Hour([DateTime])+24-4,Time.Minute([DateTime]),Time.Second([DateTime]))

Cheers!

Hi,

Your formula resulted for example "2/7/2018 11:44:12 PM" (data type Date/Time) as "7:44:13 PM" (data type ABC/123). It only shows the correct time. When I changed the data type from ABC/123 to Date/Time it resulted "12/30/1899 7:44:13 PM." The date is incorrect.

 

[legalhustler]

Got it!

I played around and got it to work with this formula and works when I changed the data type to Date/Time:

[DateField]-#duration(0,4,0,0)

Thanks both.