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.

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.

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.

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.

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.

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.

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.