Can anybody help with my formula please???

[Fox123]

Hi,

I have a column with a list of time periods in a format like this :

<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=95 border=0 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=95 height=17 x:num="1.9444444444444375E-2">00:28:00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="1.7361111111111112E-2">00:25:00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="2.2222222222222254E-2">00:32:00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="7.1527777777777857E-2">01:43:00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="6.9444444444444198E-4">00:01:00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="6.2499999999999778E-3">00:09:00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="6.9444444444444198E-4">00:01:00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="1.5972222222222276E-2">00:23:00</TD></TR></TBODY></TABLE>
​

I need a formula that will categorise the data in the column like this :

1. If the cell value (let's assume this is cell A1) is between 00:00 and 00:14 I want Excel to return "0 to 14 Minutes".
2. If the cell value is between 00:15 and 00:29 I want Excel to return "15 to 29 Minutes".
3. If the cell value is between 00:30 and 00:44 I want Excel to return "30 to 44 Minutes.
4. If the cell value is between 00:45 and 00:59 I want Excel to return "45 to 59 Minutes"
5. If the cell value is 00:60 or greater I want Excel to return "Over 60 Minutes"

This is probably really simple but I can't get my formulas to work.

Thanks guys.

Fox123

 

[Andrew Poulsom]

If your data starts in A1, in B1 enter:

=IF(HOUR(A1)>0,"Over 60 minutes",LOOKUP(MINUTE(A1),{0,15,30,45},{"0 to 14 minutes","15 to 29 minutes","30 to 44 minutes","45 to 59 minutes"}))

and copy down.

 

[schielrn]

How about this:

Excel Workbook
	A	B
1	0:28:00	15 to 29 Minutes
2	0:25:00	15 to 29 Minutes
3	0:32:00	30 to 44 Minutes
4	1:43:00	Over 60 Minutes
5	0:01:00	0 to 14 Minutes
6	0:09:00	0 to 14 Minutes
7	0:01:00	0 to 14 Minutes
8	0:23:00	15 to 29 Minutes
Sheet1

Cell Formulas
Range	Formula
B1	=IF(A1<=TIMEVALUE("00:14:00"),"0 to 14 Minutes",IF(A1<=TIMEVALUE("00:29:00"),"15 to 29 Minutes",IF(A1<=TIMEVALUE("00:44:00"),"30 to 44 Minutes",IF(A1"00:59:00"),"45 to 59 Minutes","Over 60 Minutes"))))

Hope that helps.

 

[Fox123]

Hi both,

Thank you for your help. The problem is now sorted.

mrexcel.com rules!!!

Fox123