cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
llyons
Frequent Visitor

convert DateTime format from dd/mm/yyyy hh:mm:ss AM to hh:mm:ss

Hello,

Please let me know how to convert a DateTime format from dd/mm/yyyy hh:mm:ss AM to hh:mm:ss in Power Query Editor.

 

I uploaded call center data from excel and the data importe example  12/31/1899 12:01:26 AM instead of 00:01:26 for AHT. I will need to show (1)  line chart for each interval of the day and (2) a chart with the aggregated AHT for a line of business.

 

I have Power BI professional not sure what version. I was able to update from Power BI desktop to Pro this year.

 

I tried this using using this formula HOUR([AHT])*3600+MINUTE([AHT])*60+SECOND([AHT]) but I received an error

5 REPLIES 5
mahoneypat
Microsoft
Microsoft

You can convert it to a duration with 

= [DateTimeColumn] - #datetime(1899,12,31,0,0,0)

 

But to do math with that column, you should wrap that in Duration.TotalHours (other related functions too).

 

= Duration.TotalHours([DateTimeColumn] - #datetime(1899,12,31,0,0,0))

 

You will get the hours in decimal form so you can add them up, average, etc.

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


PC2790
Super User
Super User

Hi @llyons ,

 

Two ways to do it:

1) Create a new custom column using the below M query:

= DateTime.ToText([Date],"hh:mm:ss")

 

My code looked something like this:

= Table.AddColumn(#"Changed Type2", "Date Format", each DateTime.ToText([Date1],"hh:mm:ss"))

 

2) The second way is in the main Power Bi desktop. Steps below:

      a) Convert the column into 'Time' DataType

      b) In ColumTools, select the format that you want. Screenshot for your help below:

 

PC2790_0-1610513718926.png

Let me know if it solves your purpose. 

Please mark this as solution if this was exactly you were looking for

PC2790
Super User
Super User

Hi @llyons ,

 

There are two ways to do it:

1)You can split the date and time in Power query by clicking on the column --> Split --> By delimiter --> select space

This will give you two columns with date and time separately. 

The corresponding M query will be something like:

= Table.SplitColumn(#"Table", "DateTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date", "Time"})

2) The second way is to right click and change the data type to time instead of DateTime. The corresponding M query code is as below:

= Table.TransformColumnTypes(#"Table",{{"DateTime", type time}}) 

 

I would recommend to take appraoch 1 as it will enable you to retain the date data for future use.

 

Let me know if this is what you were looking for.

 

Thankyou

llyons
Frequent Visitor

when I use your suggestion I cannot show the time in a chart or add to create a total for a particular month.

llyons
Frequent Visitor

Thank you for your responds.

When I split the column my time example is 12:01:26 AM  .  I need  the result to show 00:01:26 because it is not 12 hours nor do I need the AM.  Is there a DAX code I could use?

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors