英文:
Convert text column into time format column and calculate the sum
问题
这个文本列的格式是00:01:30（HH:MM:SS）。这个列是文本格式的。
将这个文本列的格式转换为时间格式列，并创建一个新的度量值来计算总时间和。
英文:
I have a one time column in format 00:01:30 (HH:MM:SS). This column is a text format column.
this text column format convert to a time format column and create a new measure for the total time sum.
答案1
得分: 1
你可以轻松将文本列转换为时间，使用以下代码：
Time = TIMEVALUE('Table'[Text])
但问题在于时间格式不支持超过24小时，因此你的SUM函数可能会导致溢出。这里有一个解决方法：
- 创建一个名为"Seconds"的计算列：
Seconds =
VAR Time = TIMEVALUE('Table'[Text])
RETURN HOUR(Time) * 3600 + MINUTE(Time) * 60 + SECOND(Time)
- 使用以下度量来汇总秒数，并转换为类似"持续时间"的格式：
Total Duration =
VAR total_seconds = SUM('Table'[Seconds])
VAR days = QUOTIENT(total_seconds, 24 * 60 * 60)
VAR rest1 = MOD(total_seconds, 24 * 60 * 60)
VAR hours = QUOTIENT(rest1, 60 * 60)
VAR rest2 = MOD(total_seconds, 60 * 60)
VAR minutes = QUOTIENT(rest2, 60)
VAR seconds = MOD(rest2, 60)
RETURN days & "." & FORMAT(hours, "0#") & ":" & FORMAT(minutes, "0#") & ":" & FORMAT(seconds, "0#")
英文:
You can easily convert your Text column into Time using
Time = TIMEVALUE('Table'[Text])
But the problem is that the Time format doesn't support more than 24 hours, so your SUM will potentially lead to an overflow. Here's a workaround:
- Create a calculated "Seconds" Column
Seconds =
VAR Time =
TIMEVALUE('Table'[Text])
RETURN
HOUR(Time) * 3600 + MINUTE(Time) * 60 + SECOND(Time)
- Aggregate the Seconds with this Measure and convert back to a "Duration-like" format:
Total Duration =
VAR total_seconds =
SUM('Table'[Seconds])
VAR days =
QUOTIENT(total_seconds, 24 * 60 *60)
VAR rest1 =
MOD(total_seconds, 24 * 60 * 60)
VAR hours =
QUOTIENT(rest1, 60 * 60)
VAR rest2 =
MOD(total_seconds, 60 * 60)
VAR minutes =
QUOTIENT(rest2, 60)
VAR seconds =
MOD(rest2, 60)
RETURN
days & "." & FORMAT(hours, "0#") & ":" & FORMAT(minutes, "0#") & ":" & FORMAT(seconds, "0#")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论