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

[llyons]

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

[Alphatooth]

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

[mahoneypat]

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

[PC2790]

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

[PC2790]

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]

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

[llyons]

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?

[jbwtp]

I think this is rather Column Tools -> Format

You should not need any DAX for that.

[Alphatooth]

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

[jbwtp]

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?