Thanks very much Glenn. People like you make this site so great!

"Glenn" wrote:

=IF(COUNT(FIND(" ",A1))...

Check for a space in the data in cell A1

....,LEFT(A1,FIND(" ",A1)-1)...

If there is a space, capture the left portion of the data up to, but not
including (the -1 part), the space. This is the number of full days in the data.

....+MID(A1,FIND(" ",A1)+1,LEN(A1))...

Add the value found after the space (the +1 part), which Excel interprets as a
time value.

Excel stores time as a value ranging from 0 (zero) to 0.99999999, representing
the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). A value of 1
would equal a day. So, the last two steps added the number of full days (451)
and the fraction of a day (22:07:34). The number format displays it as requested.

....,--A1)

If there is no space found, just use the data as is. The double unary minus
(--) forces Excel to convert text entries to their numeric equivalent.



Micki wrote:
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.