If you are struggling to understand things, the easiest way is to copy the
formula (numerous times if necessary) into spare cells, and chop it down to
see whichever part is causing you confusion.

-1 subtracts 1 from the value calculated beforehand, so, for example in
LEFT(A1,FIND(" ",A1)-1) the expression FIND(" ",A1) will count how many
characters along the A1 string you have to go to find the space character
[giving an answer of 4 in your case], and then because you don't want to
include the space character in working out the number it subtracts 1 to get
3, and then uses the LEFT function to give you the left-hand 3 characters of
A1, hence your number 451.

I don't think you've got a --1, but if you mean --A1, the -- (double unary
minus) construct is an easy way of converting a text string to a numerical
value without changing it. If you have a text string in A1 saying 42, the
formula -A1 would give a numerical value of -42, and --42 gives you a
numerical value of 42.

If any of the Excel functions are causing you problems, look them up in
Excel help.
--
David Biddulph

"Micki" wrote in message
...
Glenn, when I found 37:30:55 under formatting and used that, it worked. I
manually used [h]:mm:ss prior to your post. Now, I thank you and David so
much. If you have time and feel like it, I'd appreciate a step by step of
what exactly the formula does (like the "-1" and "--1") so I can use it in
the future for similar scenarios. I can usually scan others' requests for
assistance and find what I need, but after 2 hours of piecing formulas
together, I had to give up and ask.

"Glenn" wrote:

Format / Cells / Number and make sure you select the format [h]:mm:ss;@
for the
result cell. On my version of Excel (2003), it shows 37:30:55 in the
"Type:"
box when you select Time in the "Category:" list.


Micki wrote:
atm sorry to be a nudge Glenn, but it's returning 22:07:34 and not
multiplying the 451 days x 24 to get hours, and then adding the result
to
22:07:34 to get total HH:MM:SS. The result should be 10,846 hrs, 7
minutes
and 34 seconds.. I'm thinking it may be because HH:MM:SS only
recognizes 24
hrs in a day?

"Glenn" wrote:

Actually that was wrong:

=IF(COUNT(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1)+
MID(A1,FIND(" ",A1)+1,LEN(A1)),--A1)

Glenn wrote:
=IF(COUNT(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1)+
(RIGHT(A1,SEARCH(":",A1)+1)),--A1)


Micki wrote:
Sorry for not asking this sooner. The person i was trying to assist
just came back to me this morning with the all-in result she was
looking for. Where the data in the cell contains the # of days an
issue is outstanding, she wants to convert that to hours and add it
to
the hh:mm:ss. So where the data = 451 22:07:34 she wants to multiply
451 x 24 hrs, and add it to 22 hrs, 7 mins and 27 seconds. Please
note
that not all data contains # days outstanding. Some only contain
3:39:10 (hh:mm:ss) I am really stumped.)

"Micki" wrote:

I have raw data that downloads in 2 different formats.
451 22:07:34 (451 = # days a problem is not resolved)
3:39:10 (hrs, minutes, seconds)
I need a formula that will enter only the hrs, mins and seconds in
an
adjacent column. The formula =RIGHT(A33,SEARCH(":",A33)+2) gives me
the results I need where the data in column A = 451 22:07:34, but I
believe I also need to add an if statement to give me only the hrs,
mins, sec for the case where the value is 3:39:10. I have tried
using
=if(len(a27)8,RIGHT(A33,SEARCH(":",A33)+2),A27, but I just get
#Value return. Help is greatly appreciated.