Forum Discussion
Duration hours
I have an Excel file that I import to Power BI that has a column for labor hours. It is formatted as "general" in Excel. When I import it to PBI, it looks at it as text. That would be great except that I need to calculate all totals for each person. So, I tried to change the data type to "duration". And that works, except for 49 cells in the Excel file that have numbers larger than 24 (1.e. 210:00). So, I removed errors and went back to Excel. I formatted the column as HH:MM. I reimported it to PBI, and it couldn't parse anyof that colums entries. I changed it back to general in Excel and reloaded it to PBI. And that's when I come here looking for some direction. I thought about splitting the colum at the colon
I actually came about a way to fix this. I don't want to format the column in Excel as I'm trying to do as little data manipulation there as possible. I prefer doing it in PBI. That said, I was willing to share what I am trying to do when it hit me. I removed the colon and replaced it with a period. I then replaced minute values with fraction percents, i.e. if it was HH:30 I replaced the :30 with .5, and so on. I then changed the entire data type to a decimal number. And voila! It works. Thanks for the efforts to help.
3 Replies
I actually came about a way to fix this. I don't want to format the column in Excel as I'm trying to do as little data manipulation there as possible. I prefer doing it in PBI. That said, I was willing to share what I am trying to do when it hit me. I removed the colon and replaced it with a period. I then replaced minute values with fraction percents, i.e. if it was HH:30 I replaced the :30 with .5, and so on. I then changed the entire data type to a decimal number. And voila! It works. Thanks for the efforts to help.
- themistoklis
Community Champion
Anonymous
try keeping the format as HH:MM (210:00 is not a valid format).
Then create a column and add the following formula to convert to mins or hours:
Mins = HOUR(Sheet1[Time_Diff])*60+MINUTE(Sheet1[Time_Diff])
Hours = HOUR(Sheet1[Time_Diff])+(MINUTE(Sheet1[Time_Diff]))/60
