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.

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.

It's possible that the "3:39:10" is already in time format. Try this:

=IF(ISNUMBER(A33),A33,RIGHT(A33,SEARCH(":",A33)+2) )

Format the cell as time.

One way.

This returns a true Excel time value...

=IF(COUNT(FIND(" ",A1)),TIMEVALUE(RIGHT(A1,FIND(":",A1)+2)),--A1)

Format as [h]:mm:ss

--
Biff
Microsoft Excel MVP


"Micki" wrote in message
...
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.

That did it! Thanks very much.

"T. Valko" wrote:

One way.

This returns a true Excel time value...

=IF(COUNT(FIND(" ",A1)),TIMEVALUE(RIGHT(A1,FIND(":",A1)+2)),--A1)

Format as [h]:mm:ss

--
Biff
Microsoft Excel MVP


"Micki" wrote in message
...
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.

Try this:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))


"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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Micki" wrote in message
...
That did it! Thanks very much.

"T. Valko" wrote:

One way.

This returns a true Excel time value...

=IF(COUNT(FIND(" ",A1)),TIMEVALUE(RIGHT(A1,FIND(":",A1)+2)),--A1)

Format as [h]:mm:ss

--
Biff
Microsoft Excel MVP


"Micki" wrote in message
...
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.

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.

=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.

It's returning #VALUE

"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.

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.

Try my correction (below), and if that doesn't work, post a sample of your data
to www.savefile.com and we'll see if we can figure out what's up.

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


Micki wrote:
It's returning #VALUE

"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.

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.

OK. Let's do some debugging for you.
If the content of A1 is a text string of 451 22:07:34 , which is what you
quoted, then the answer is 10846:07:34

If you are seeing 22:07:34 are you sure that you formatted the result as
[h]:mm:ss, rather than as h:mm:ss ? What do you see if you format the
result cell temporarily as General?

Having clarified that, check the input by copying the content of A1 to here
to check whether it really is 451 22:07:34, and also use the formulae
=ISTEXT(A1), which should return TRUE, and =LEN(A1), which should retain 12.
You can check whether the space is really a space by using
=CODE(MID(A1,4,1)) to see whether it is 32.
--
David Biddulph

"Micki" wrote in message
...
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.

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.

I performed all of the tests. I made sure the format is [h]:mm:ss. When I
format as general, I get #VALUE. =ISTEXT(A1) returns TRUE. =LEN(A1) returns
12. =CODE(MID(A1,4,1)) returns 32.

"David Biddulph" wrote:

OK. Let's do some debugging for you.
If the content of A1 is a text string of 451 22:07:34 , which is what you
quoted, then the answer is 10846:07:34

If you are seeing 22:07:34 are you sure that you formatted the result as
[h]:mm:ss, rather than as h:mm:ss ? What do you see if you format the
result cell temporarily as General?

Having clarified that, check the input by copying the content of A1 to here
to check whether it really is 451 22:07:34, and also use the formulae
=ISTEXT(A1), which should return TRUE, and =LEN(A1), which should retain 12.
You can check whether the space is really a space by using
=CODE(MID(A1,4,1)) to see whether it is 32.
--
David Biddulph

"Micki" wrote in message
...
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.

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.

=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.

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.

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.

Glad I could help.


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