Extract Middle characters in a Time Field (1 Viewer)

[JohnLee]

Good afternoon Folks,

I hopeing someone can help me here.

I have a spreadsheet that records timings for individuals processing times and I have a totals column that totals up those times, but because the time format will only calculate up to a maximum of 24 hours and then it starts again, the calculations do not display as I would like.

So having worked out how to convert the time format to decimal to get the total hours in decimal format I now what to display the total hours in time format instead of decimal so for instance 225.64 to show as 225:38:24

I have been able to seperate out the 225 by using the left function, but on using the right function as follows

=RIGHT(G104,3)

The cell is in decimal format, I get this .64

I then use another cell in time format to convert that to this 00:38:24

Now what I would like to do is to extract the middle two characters between both sets of colons i.e. 38 and keep it as a text string, and then get the right two characters of the right colon and save that as a text string.

I will then marry all three together as a string but looking like a time format as follows: 225:38:24

any assistance would be most appreciated.

Regards

John

 

[namliam]

In excel you can put [h] (where h depends on your regional settings) and the hours will display properly beyond 24 hours into 25,26, etc, 9999, 10000, etc.. hours

 

[JohnLee]

Hi,

Thanks for your response, I'm not clear on where you put the "H" can you be more specific as to how it would look and where "H" would be placed.

Regards

John

 

[namliam]

Sorry should have included that.... in the format of the cell
Which normaly says H:N:S for a time format, you change that to [H]:N:S

 

[JohnLee]

Hi Namliam,

Thanks very much for that, most helpful and something new learnt.

Regards

John

 

[namliam]

Sometimes I make sense