# Power Query Date Field to Subtract 4 Hours



## legalhustler (Mar 20, 2018)

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 (Mar 20, 2018)

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

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


----------



## Tyler Hilton (Mar 20, 2018)

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 (Mar 20, 2018)

Oops, of course the 2 should be a 4.

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


----------



## legalhustler (Mar 20, 2018)

Norie said:


> 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 (Mar 20, 2018)

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 (Mar 20, 2018)

Tyler Hilton said:


> 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 (Mar 20, 2018)

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.


----------

