Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
MarcelBeug
Community Champion
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)

View solution in original post

20 REPLIES 20
TimoRiikonen
Advocate III
Advocate 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.
This one add four hours in M / PowerQuery to column createdon:

 

#"Added Hours" = Table.ReplaceValue(#"Previous line",each [createdon], each DateTime.From([createdon]) + #duration(0,0,0,Number.Round(4 * 3600)), Replacer.ReplaceValue,{"createdon"})

 

Anonymous
Not applicable

 

@TimoRiikonen  This helped but still I felt it was difficult to convert.

 

For thos who need to add hours for example 9.5 hours which is (34,200 seconds) then use the forumla as below,

 

[Last Refreshed Date and Time] + #duration(0,0,0,34200)

 

In other words all your decimal values convert to seconds easily add.

 

Thanks,

Karthik

Anonymous
Not applicable

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)

 

It works! Amazing and thanks


@Anonymous 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)

 



!

MarcelBeug
Community Champion
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)

The #Duration function seems to be related to something else now.

I found the easiest way was to create a new column using the following formula:

 

Modified = [modifiedon]+ 11/24
 
if you say + 11 that adds 11 days. So to add 11 hours, simply divide by 24

This is not a solution though if you are in a timezone that has daylight savings

thanks you just helped me too!!

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

 

I have this error

 

modelo_CONEXION_bdXOOM - Power BI Desktop.jpg

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

Dan Malagari
Consultant at Headspring

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? 

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

#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)

Worth mentioning in the answer as Power Query M is not mentioned in the question either.

This is first Google result for "powerbi date add hours". 
Most people would be expecting DAX

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

 

StartDate = (adx_events[adx_releasedate] + #duration(0,10,0,0))

 

i get this error on powerbi query editor:

 

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

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"}}),
    adx_events = #"Renamed Columns"{0},
    StartDate = (adx_events[adx_releasedate] + #duration(0,10,0,0))
in
    StartDate

 

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

Specializing in Power Query Formula Language (M)

this works!

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

Hi

 

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

Please don't hijack other topics for your own question, but start a new topic instead.

 

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)

My apologies, i believed my question was still on topic, asking a question about your answer and would enhanse the existing thread

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.