I am trying to convert HH:MM:SS

[RosCoe]

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.

[RosCoe · Accepted solution]

I am using Excel and I got the formula to work.  This is how

=Value("0")&D3    

Thanks for your help.

[Eric_Zhang]

@RosCoe

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.

[RosCoe · Accepted solution]

I am using Excel and I got the formula to work.  This is how

=Value("0")&D3    

Thanks for your help.

[MarcelBeug · Accepted solution]

Looks like you're on the wrong forum?

By the way your Excel formula returns text. I would expect =VALUE("0"&D3).