Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to convert HH:MM:SS and I can do that but if there is no HH when I am summing the tabs together on some. Example:
Staffed Time | AUX Time | Time in 0 | Time in Break | Time in Lunch | Time in Meeting | Time in Training | Time in Coaching |
168:14:44 | 43:34:42 | 15:48:13 | 8:25:59 | 11:14:23 | 2:37:14 | :00:00 | :00:00 |
9:09:58 | 3:29:16 | 0:57:17 | 0:14:38 | :30:02 | :34:13 | :00:00 | :00:00 |
8:54:48 | 2:56:21 | 1:35:25 | 0:30:21 | :29:01 | :08:08 | :00:00 | :00:00 |
8:45:14 | 1:53:02 | 0:38:13 | :32:32 | :35:04 | :00:00 | :00:00 | :00:00 |
Total TAB - becausse the Time in Break is :32:32 it is giving me the Value error. If I manually type a zero in front the :32:32 it will come up correct. I have several tabs for different reps, so I need to be able to put in a formula so I don't have to manually fix every break, lunch etc.
Staffed Time | AUX Time | Time in 0 | Time in Break | Time in Lunch | Time in Meeting | Time in Training | Time in Coaching |
773:20:35 | 213:21:56 | 72:36:51 | 39:52:24 | 60:12:05 | 14:15:07 | #VALUE! | #VALUE! |
43:42:15 | 15:00:13 | 4:40:32 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! |
42:18:35 | 10:18:56 | 4:02:57 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! |
32:27:28 | 7:30:42 | 3:11:47 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! |
Any help would be greatly appreciated.
Solved! Go to Solution.
I am using Excel and I got the formula to work. This is how
=Value("0")&D3
Thanks for your help.
Looks like you're on the wrong forum?
By the way your Excel formula returns text. I would expect =VALUE("0"&D3).
I think you can add a prefix 00 and extract the right 8 characters.
let Source = Table.FromRows({{"39:52:24"},{":32:32"},{":35:04"},{":00:00"}},{"time"}), AddedPrefix = Table.TransformColumns(Source, {{"time", each "00" & _, type text}}), extract=Table.TransformColumns(AddedPrefix , {{"time", each Text.End(_,8), type text}}) in extract
By the way, may I know how do you do the conversion? DAX or Power Query, as I can see 39:52:24 is not a valid time format as well.
I am using Excel and I got the formula to work. This is how
=Value("0")&D3
Thanks for your help.
Looks like you're on the wrong forum?
By the way your Excel formula returns text. I would expect =VALUE("0"&D3).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |