Time convertion based on condition
 

Hello.

How can I inspect cells which contain data like...
2 weeks
1 day
3.2 hours

and convert them all to hours as...
336 hours
24 hours
3.2 hours

I have been playing with the conditional IF and the text command SEARCH...

=IF(SEARCH("days",G4),1,0)

....but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.

Time convertion based on condition
 

=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),
ROW(INDIRECT("A1:A"&LEN(A1))),255))-1)*(IF(ISNUMBER(SEARCH("week",A1)),188,IF(ISNUMBER (SEARCH("day",A1)),24,1)))

this is an array formula, so commit with Ctrl-Shift-Enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Maglez" wrote in message
...
Hello.

How can I inspect cells which contain data like...
2 weeks
1 day
3.2 hours

and convert them all to hours as...
336 hours
24 hours
3.2 hours

I have been playing with the conditional IF and the text command SEARCH...

=IF(SEARCH("days",G4),1,0)

...but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.

Time convertion based on condition
 

Try this !

Suppose u have data in column A

select the data , then go to data | text to column | choose delimited
option | check space option | finish

In col A you have number and in col B you have week, days or hours

put this formula in Col C
=IF(B1="week",A1*7*24,IF(B1="days",B1*24,IF(B1="ho urs",A1,"")))


On May 28, 2:31*pm, "Bob Phillips" wrote:
=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),
ROW(INDIRECT("A1:A"&LEN(A1))),255))-1)*(IF(ISNUMBER(SEARCH("week",A1)),188,*IF(ISNUMBE R(SEARCH("day",A1)),24,1)))

this is an array formula, so commit with Ctrl-Shift-Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Maglez" wrote in message

...



Hello.

How can I inspect cells which contain data like...
2 weeks
1 day
3.2 hours

and convert them all to hours as...
336 hours
24 hours
3.2 hours

I have been playing with the conditional IF and the text command SEARCH....

* *=IF(SEARCH("days",G4),1,0)

...but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.- Hide quoted text -

- Show quoted text -

Time convertion based on condition
 

beat me to it Bob

a small amendment

=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),
ROW(INDIRECT("A1:A"&LEN(A1))),255))-1)*(IF(ISNUMBER(SEARCH("week",A1)),168,IF
(ISNUMBER(SEARCH("day",A1)),24,1)))

edvwvw


Bob Phillips wrote:
=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A 1:A"&LEN(A1))),1))),
ROW(INDIRECT("A1:A"&LEN(A1))),255))-1)*(IF(ISNUMBER(SEARCH("week",A1)),188,IF(ISNUMBER (SEARCH("day",A1)),24,1)))

this is an array formula, so commit with Ctrl-Shift-Enter

Hello.

[quoted text clipped - 14 lines]
...but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1

Time convertion based on condition
 

This formula (looking at G4 as in your example) will convert "day",
"days", "week" or "weeks" into hours as appropriate, or leave G4 as it
is (assuming it to be in hours already):

=IF(ISNUMBER(SEARCH("day",G4)),LEFT(G4,SEARCH("d", G4)-1)*24&"
hours",IF(ISNUMBER(SEARCH("week",G4)),LEFT(G4,SEAR CH("w",G4)-1)*7*24&"
hours",G4))

Put it in a helper cell on row 4, and then copy it down to cover your
range of cells.

Hope this helps.

Pete

On May 28, 9:36*am, Maglez wrote:
Hello.

How can I inspect cells which contain data like...
2 weeks
1 day
3.2 hours

and convert them all to hours as...
336 hours
24 hours
3.2 hours

I have been playing with the conditional IF and the text command SEARCH...

* * =IF(SEARCH("days",G4),1,0)

...but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.

Time convertion based on condition
 

I think this normally entered formula does what you want...

=IF(ISNUMBER(SEARCH("week*",A1)),168*LEFT(A1,FIND( " ",A1))&"
hours",IF(ISNUMBER(SEARCH("day*",A1)),24*LEFT(A1,F IND(" ",A1))&" hours",A1))

Rick


"Maglez" wrote in message
...
Hello.

How can I inspect cells which contain data like...
2 weeks
1 day
3.2 hours

and convert them all to hours as...
336 hours
24 hours
3.2 hours

I have been playing with the conditional IF and the text command SEARCH...

=IF(SEARCH("days",G4),1,0)

...but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.

Time convertion based on condition
 

I get both Bob's and your formula dropping the decimal part of 3.2 hours. In
addition, both of your formulas do not contain the "hours" tag which I think
the OP wants (although I'm not 100% sure of that).

Rick


"edvwvw via OfficeKB.com" <u42512@uwe wrote in message
news:84d217756fad8@uwe...
beat me to it Bob

a small amendment

=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),
ROW(INDIRECT("A1:A"&LEN(A1))),255))-1)*(IF(ISNUMBER(SEARCH("week",A1)),168,IF
(ISNUMBER(SEARCH("day",A1)),24,1)))

edvwvw


Bob Phillips wrote:
=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT(" A1:A"&LEN(A1))),1))),
ROW(INDIRECT("A1:A"&LEN(A1))),255))-1)*(IF(ISNUMBER(SEARCH("week",A1)),188,IF(ISNUMBER (SEARCH("day",A1)),24,1)))

this is an array formula, so commit with Ctrl-Shift-Enter

Hello.

[quoted text clipped - 14 lines]
...but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1

Time convertion based on condition
 

Guys, I am impressed for the number of answers, quality and the speed you
guys answered, it seems like a competition :-)

Thank you very much to all of you... I finally when for Rick's solution for
being shorter.

Miguel.

"Rick Rothstein (MVP - VB)" wrote:

I think this normally entered formula does what you want...

=IF(ISNUMBER(SEARCH("week*",A1)),168*LEFT(A1,FIND( " ",A1))&"
hours",IF(ISNUMBER(SEARCH("day*",A1)),24*LEFT(A1,F IND(" ",A1))&" hours",A1))

Rick


"Maglez" wrote in message
...
Hello.

How can I inspect cells which contain data like...
2 weeks
1 day
3.2 hours

and convert them all to hours as...
336 hours
24 hours
3.2 hours

I have been playing with the conditional IF and the text command SEARCH...

=IF(SEARCH("days",G4),1,0)

...but I am able only to perform 1 condition, I don't know if there is a
better approach to do this.