cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hansbogaert
New Member

Text to time

Looking for the best way to convert a text column to a time column, allowing me to calculate the sum, average, ...

 

Data comes in looking like time, but is formatted as text. For most only the time is visible, for those going over 24 hours, you'll see the days as well.

hansbogaert_0-1624541720402.png

 

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

Hey @hansbogaert 

this was something new for me and I thank you for making me discover it.

Here are the steps:

let's say your column looks like this in your DB

aj1973_0-1624642236926.png

You need to add a custom column and convert that column to Sum of seconds

here is the formula Duration.TotalSeconds([Duration] - #datetime(1899,12,31,0,0,0))

aj1973_1-1624642314373.png

Change the Column Type to number

aj1973_2-1624642378552.png

Then add a Dax formula like this:

aj1973_3-1624642714852.png

 

And here is the DAX

Total Duration =
var vSeconds=SUM(Sheet1[Dur])
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
vDays&" D : "&
vRemainingHours&" H : "&
vRemainingMinutes&" M : "&
ROUND(vRemainingSeconds,0)& " S "

 

Hope it works for you.

Attached the Sample Pbix

https://drive.google.com/file/d/1wU_VFfS69XSIPeYkT2v12-r1s84QQb3G/view?usp=sharing

 

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

10 REPLIES 10
aj1973
Community Champion
Community Champion

Hi @hansbogaert 

change the type of the column to Date/Time, then change it into Time...In Power query

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hi @aj1973 ,

Thanks for your fast reaction.

When I try to do this, I get the message that I need to convert my data to Import Mode instead of DirectQuery. 

 

aj1973
Community Champion
Community Champion

Oh yes, you can't do it in DQ mode connection. you need to do the cleaning directly to the source. Is the source an excel file?

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

It's linked to a huge SQL database.

I imported it, and indeed the steps work, but with an error. For those who go over 24 hours. They are indicated with the date:

One row looks like this: 2/01/1900 8:15:00

After converting it to Date/Time it stays the same

And when I move to Time again, it is 8:15:00, but it should be 56:15:00

aj1973
Community Champion
Community Champion

"but it should be 56:15:00"  why!? is this a Duration type?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Yes, it is in fact the amount of time people followed a training (classroom + self study). The idea is to get a sum or average per department/team/...

aj1973
Community Champion
Community Champion

Sorry but I don't really understand how 8:15:00 should be 56:15:00? 

Do you have a column for start time and another column for end time in your table?

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

No, it's an automatic report containing the learning time from our platform. It contains the time a person has been learning a specific course. Can be classroom or e-learning.

For some topics this is over 24 hours and in the automatic report it is, just like in Excel, converted to a date/time format. 25 hours = 01/01/1900 01:00:00. But imported in the SQL it defines it as text (tried to change, but doesn't work well, probably because the fields are different, some with the date, others without - depending if >24)

In Excel you can easily say [h]:mm:ss to calculate over 24 hours, but doesn't seem to be an option in PowerBI

Hi @hansbogaert ,

 

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

 

Best Regards,
Winniz

aj1973
Community Champion
Community Champion

Hey @hansbogaert 

this was something new for me and I thank you for making me discover it.

Here are the steps:

let's say your column looks like this in your DB

aj1973_0-1624642236926.png

You need to add a custom column and convert that column to Sum of seconds

here is the formula Duration.TotalSeconds([Duration] - #datetime(1899,12,31,0,0,0))

aj1973_1-1624642314373.png

Change the Column Type to number

aj1973_2-1624642378552.png

Then add a Dax formula like this:

aj1973_3-1624642714852.png

 

And here is the DAX

Total Duration =
var vSeconds=SUM(Sheet1[Dur])
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
vDays&" D : "&
vRemainingHours&" H : "&
vRemainingMinutes&" M : "&
ROUND(vRemainingSeconds,0)& " S "

 

Hope it works for you.

Attached the Sample Pbix

https://drive.google.com/file/d/1wU_VFfS69XSIPeYkT2v12-r1s84QQb3G/view?usp=sharing

 

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!