cancel
Showing results for
Did you mean:
Helper I

## Calculate time data into groups

I have a spreadsheet with data in column J for time shown as 05 for 5 min and 60 for 60 minutes. I want to show the data as:

05 or less "Less than 5"

5-10 "5 to 10"

im using IF Statement buts not working as expected. Any suggestions please?
=IF(\$J2>=0,\$J2<=5),"Leas than 5", IF(AND(\$J2>5,\$J2>=10)"5 to 10",""))

TimeHours & MinutesMinutesMinjutes as ValuesTime Spent

 0:05:32 00:05 05 5 Over 40 1:00:18 01:00 60 60 Over 40 0:25:42 00:25 25 61 Over 40 1:00:12 01:00 60 62 Over 40 0:51:05 00:51 51 63 Over 40 0:22:50 00:22 22 64 Over 40
1 ACCEPTED SOLUTION
Community Champion

it seems your I column is text instead of number, try the following:

=IF(AND(VALUE(\$I2)>=0,VALUE(\$I2)<=5),"Leas than 5", IF(AND(VALUE(\$I2)>5, VALUE(\$I2)<=10),"5 to 10",""))

p.s. consider @some to continue a discussion.

4 REPLIES 4
Helper I

@FreemanZ this worked- thank you so much!

Community Champion

try like:

=IF(AND(\$J2>=0,\$J2<=5),"Leas than 5", IF(AND(\$J2>5, \$J2<=10),"5 to 10",""))

Helper I

Hi @FreemanZ unfortunately it didn't work. Column L is where I would expect to see values.

Community Champion

it seems your I column is text instead of number, try the following:

=IF(AND(VALUE(\$I2)>=0,VALUE(\$I2)<=5),"Leas than 5", IF(AND(VALUE(\$I2)>5, VALUE(\$I2)<=10),"5 to 10",""))

p.s. consider @some to continue a discussion.

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors