Forum Discussion
Get Time from DateTime field
Dear Power BI community,
Can anyone please help me with a DAX formula with extracting the time value from a datetime field.
See for example the below datetime values and I want to extract the timevalue from the datetime field
So for the output, I would like to see 11:17:56 or 3:00:00 in a new column - I want to keep the current column which shows datetime and add a new column with the correct formula.
Can anyone please help me?
Thank you guys!
Hi @Thankyouverymuc ,
Here a solution for a calculated column as described in this blog post as well:
https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#22_How_to_retrieve_the_time_from_a_datetime_columnWe start by duplicating the datetime column:
--> Right click on a column in your table and select new column:
--> duplicate the column by referencing the datetime column:
NewColumn = TableDateTime[DateTime]Then, just change the format to Time (just like @Syk suggested in Power Query):
The result:
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Sorry to jump in on this forum but noticed there wasn't a DAX solution on here. If you're looking for a DAX solution, you can extract the Time from a DateTime column by doing the following:
1. Click on new Calculated Column2. Time = FORMAT ( 'TableName'[DateTimeColumnName] , "hh:mm:ss" )
3. Convert the new Time column from Text to Time.
Hope this helps.
Theo
23 Replies
- TheoC
Community Champion
Sorry to jump in on this forum but noticed there wasn't a DAX solution on here. If you're looking for a DAX solution, you can extract the Time from a DateTime column by doing the following:
1. Click on new Calculated Column2. Time = FORMAT ( 'TableName'[DateTimeColumnName] , "hh:mm:ss" )
3. Convert the new Time column from Text to Time.
Hope this helps.
Theo
How would I control what timezeone is used when the datetime is formatted? i.e. the datetime is stored in UTC but I want to format it into a fixed timezone (for example US/Eastern).
- TheoC
Hi arothberg, you need to know the difference in hours between UTC and your target timezone. Once you know that, then all you need to do is create a calculated column:
AdjustedTime = FORMAT ( 'Table'[TimeColumn] - TIME ( 0 , 0 , 0 ) , "hh:mm:ss" )The ( 0 , 0 , 0 ) represents Hours , Minutes , Seconds. So if you want to take 5 hours off your TimeColumn, then just write ( 5 , 0 , 0 ) in the above formula. Make sure to adjust the - / + according to the timezone you're after.Hope this helps.Theo
- tackytechtom
Most Valuable Professional
Hi Thankyouverymuc ,
One way to achieve this is by using Power Query:
Just add a new column with the following code:
= Time.From([DateTime])
Let me know, if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ - Syk
Resident Rockstar
Right click on column and hit duplicate
Then click on the new column and go to transform > Time > Time only - tackytechtom
Hi Thankyouverymuc ,
For a solution in DAX, create a calculated column where you duplicate the datetime column. Then, just change the format to Time (just like Syk suggested in Power Query).
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi Tomfox,
First of all, thank you so much for your quick response.
As I stated here below, i can't do that solution because I can't acces the table through Power Query due to rights that I currently have on that table (this is getting fixed) and that's why I was asking whether this is possible through a DAX formula. Do you have an alternative solution?
- tackytechtom
Hi @Thankyouverymuc ,
Here a solution for a calculated column as described in this blog post as well:
https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#22_How_to_retrieve_the_time_from_a_datetime_columnWe start by duplicating the datetime column:
--> Right click on a column in your table and select new column:
--> duplicate the column by referencing the datetime column:
NewColumn = TableDateTime[DateTime]Then, just change the format to Time (just like @Syk suggested in Power Query):
The result:
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi Guys! Thank you both for your quick responses but I can't work through the Power Query because I don't have the rights yet so could you please give me a solution with a DAX formula? In Excel, there are various ways through which I can convert it but somehow I can't find it with DAX.
Jon_vBAdvocate II
For a pure DAX solution - I came up with the below. Not sure if it is more or less efficient than making a second copy of the column ... I just don't prefer to do that.
The dax below would be used to make a Calculated column. Or - you can use the logic in a measure.
TimeOfDateTime = TIME( HOUR( FactStuff[CreateDateTime] ), MINUTE( FactStuff[CreateDateTime] ), SECOND( FactStuff[CreateDateTime] ) )
Use this measure to convert UTC to PST or anything.. this is dynamic, based on time , date also will change.. Let utc 12 AM, automatically it will give 8 or 9PM before day .
Current EST_Today =VAR UTC_DateTime = now() // Variable to hold the original UTC datetime value from your table.VAR Year = YEAR(UTC_DateTime) // Extracts the year from the UTC datetime to calculate DST boundaries for that specific year.VAR DST_Start = DATE(Year, 3, 14 - WEEKDAY(DATE(Year, 3, 8), 2)) // Calculates the start of DST (Second Sunday in March).VAR DST_End = DATE(Year, 11, 7 - WEEKDAY(DATE(Year, 11, 1), 2)) // Calculates the end of DST (First Sunday in November).VAR IsDST = IF(UTC_DateTime >= DST_Start && UTC_DateTime < DST_End, -4, -5) // Determines if the datetime falls in DST period; adjusts offset to -4 for DST (EDT), otherwise -5 for standard time (EST).VAR LocalDateTime = UTC_DateTime +IsDST/24RETURN FORMAT(LocalDateTime, "M/DD/YYYY HH:MM AM/PM")
