Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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


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

Frequent Visitor

Also, can we convert dd/mm/yyyy hh:mm:ss AM/PM to hh AM/PM to plot a 24 hour timeline.


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.




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

Community Champion
Community Champion

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:



Let me know if it solves your purpose. 

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

Community Champion
Community Champion

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.



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.

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?


Memorable Member
Memorable Member

I think this is rather Column Tools -> Format


You should not need any DAX for that.

But I don't see hh AM/PM choice. Any idea on how we can achieve this ?

For example:

DAX (as custom column)


Column = FORMAT('Table'[Time], "hh AM/PM")


Do not forget to set sorting for [Column] by [Time] in the Column tools menu.


Do you alsowant to show 12 AM as 0AM?

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors