cancel
Showing results for
Did you mean:

## How to add hours to DateTime data?

I have a column DateTimeCreated (Example: 2016-12-20 19:40) and I want to add 10 hours to this resulting in 2016-12-21 05:40. Is this possible?

1 ACCEPTED SOLUTION
Community Champion

You can add a duration to a datetime field, e.g. if your fieldname is "DateTime":
[DateTime] + #duration(0,10,0,0))
The arguments of#duration are days, hours, minutes, seconds.

Specializing in Power Query Formula Language (M)
16 REPLIES 16
Helper III

This accepted solution works for integers, but not for decimals.

If you wish to support decimals as well, then you should convert the value to second and round it:

DateTimeValue = DateTimeValue + #duration(0,0,0,Number.Round(DaysToShift * 24 * 3600))

You can do this in a calculated column if you prefer not to delve in to M.

The default unit in a Date/Time context is 1 day, so:

Column = [DateTimeCreated] + 1  in your example would return 2016-12-21 19:40

You can use this to add hours using decimals or a calculation like:

Column = [DateTimeCreated] + 0.5  (12 hours added)

Column = [DateTimeCreated] + ((1/24)*10)  (10 hours added)

Frequent Visitor

It works! Amazing and thanks

@steve_honey wrote:

You can do this in a calculated column if you prefer not to delve in to M.

The default unit in a Date/Time context is 1 day, so:

Column = [DateTimeCreated] + 1  in your example would return 2016-12-21 19:40

You can use this to add hours using decimals or a calculation like:

Column = [DateTimeCreated] + 0.5  (12 hours added)

Column = [DateTimeCreated] + ((1/24)*10)  (10 hours added)

!

Community Champion

You can add a duration to a datetime field, e.g. if your fieldname is "DateTime":
[DateTime] + #duration(0,10,0,0))
The arguments of#duration are days, hours, minutes, seconds.

Specializing in Power Query Formula Language (M)
Helper II

thanks you just helped me too!!

Frequent Visitor

THIS FUNCTION IS AVAILABLE BY direct query?????

I have this error

Resolver IV

Not trying to resurrect a dead topic, but for those wondering where #duration is.. it's in PowerQuery (M), not DAX.

Dan Malagari
Helper II

I tried this to type the function #duration function but it's not coming up. Power Bi does not recognize it. Do you know why?

Regular Visitor

Same here.. that #Duration formula doesn't works

Community Champion

#duration is a Power Query (M) function to be used in the Query Editor.

And it is case-sensitive, so #Duration won't work in any case.

Specializing in Power Query Formula Language (M)
Regular Visitor

Sorry, i write Duration here, but i tried with duration as follows:

i get this error on powerbi query editor:

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 32, #.

Community Champion

In the code below I copied your line without any modification and it works fine.

```let
Source = {40000..40001},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "adx_releasedate"}}),
in
StartDate```

You can copy/paste this code into your query editor and see for yourself.

Specializing in Power Query Formula Language (M)
Frequent Visitor

Thansk a lot, your solution worked magically for my issue as well 🙂

New Member

Hi

How do you add 10hrs to all dates in the list instead of just one?

Community Champion

And don';t forget to react to replies you get, maybe give kudos, mark replies as answer, which you still have to do for your for your first and only topic so far, raised in April 2016.

Specializing in Power Query Formula Language (M)
New Member

Announcements