Reply
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎03-20-2017
Accepted Solution

I am trying to convert HH:MM:SS

I am trying to convert HH:MMSmiley FrustratedS and I can do that but if there is no HH when I am summing the tabs together on some.  Example:

Staffed TimeAUX TimeTime in 0Time in BreakTime in LunchTime in MeetingTime in TrainingTime in Coaching
168:14:4443:34:4215:48:138:25:5911:14:232:37:14:00:00:00:00
9:09:583:29:160:57:170:14:38:30:02:34:13:00:00:00:00
8:54:482:56:211:35:250:30:21:29:01:08:08:00:00:00:00
8:45:141:53:020: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 TimeAUX TimeTime in 0Time in BreakTime in LunchTime in MeetingTime in TrainingTime in Coaching
773:20:35213:21:5672:36:5139:52:2460:12:0514:15:07#VALUE!#VALUE!
43:42:1515:00:134:40:32#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
42:18:3510:18:564:02:57#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
32:27:287:30:423:11:47#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!

 

Any help would be greatly appreciated.


Accepted Solutions
Frequent Visitor
Posts: 2
Registered: ‎03-20-2017

Re: I am trying to convert HH:MM:SS

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

 

=Value("0")&D3    

 

Thanks for your help.

View solution in original post

Super Contributor
Posts: 1,191
Registered: ‎11-25-2016

Re: I am trying to convert HH:MM:SS

[ Edited ]

Looks like you're on the wrong forum?

 

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

View solution in original post


All Replies
Moderator
Posts: 2,834
Registered: ‎03-06-2016

Re: I am trying to convert HH:MM:SS

@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

 Capture.PNG

 

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.

 

Frequent Visitor
Posts: 2
Registered: ‎03-20-2017

Re: I am trying to convert HH:MM:SS

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

 

=Value("0")&D3    

 

Thanks for your help.

Super Contributor
Posts: 1,191
Registered: ‎11-25-2016

Re: I am trying to convert HH:MM:SS

[ Edited ]

Looks like you're on the wrong forum?

 

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