Time convertion based on condition

M

Maglez

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

Bob Phillips

=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)
 
M

muddan madhu

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="hours",A1,"")))
 
E

edvwvw via OfficeKB.com

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 said:
=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.
Click to expand...
[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.
Click to expand...
Click to expand...
 
P

Pete_UK

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,SEARCH("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
 
R

Rick Rothstein \(MVP - VB\)

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,FIND(" ",A1))&" hours",A1))

Rick
 
R

Rick Rothstein \(MVP - VB\)

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 said:
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 said:
=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.
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
M

Maglez

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