Forum Discussion
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?
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.
21 Replies
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.THIS FUNCTION IS AVAILABLE BY direct query?????
I have this error
Not trying to resurrect a dead topic, but for those wondering where #duration is.. it's in PowerQuery (M), not DAX.
thanks you just helped me too!!
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
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)
!
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"})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
I needed to do this recently and successfully used UTCNOW()+TIME(10,0,0) to add 10 hours. 🙂
