IF STATEMENT with time.

[itshere]

Hello,

Need help with a multiple IF statement. This cell is TIME =0:53:46 ( so 53 minutes and 46 seconds).

I want to group the results into a few categories. Under 5 minutes, 10 - 20 min, 20 - 30min and 30+ minutes.

Please help. Thanks!

[AliGW]

What do you mean by 'group'? What does your raw data look like? What do your expected outcomes look like?

[itshere]

=IF(AE2<=0:05:00,"0-5",IF(AE2<=0:10:00,"5-10",IF(AE2<=0:20:00,"10-20",IF(AE2>0:20:01,">20"))))

this is what I have but its not working...

[AliGW]

That's not what I asked to see. Review my previous post and provide the information requested there, please. Attach a workbook if it is easier.

[itshere]

That's not what I asked to see. Review my previous post and provide the information requested there, please. Attach a workbook if it is easier.

Disregard the word "Group". Raw data example already included. A column with h:mm:ss (0:53:46, 0:10:01,etc)... the outcome i want is based on the IF statement above....

[AliGW]

OK - you don't seem to be understanding what I'm asking for.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture or a description of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[itshere]

wow, with over 2 years on these forums - never had a problem. Haha dont take this too seriously, the people responding to the inquires will easily understand my issue. I have provided the ALL the necessary data for someone to answer my issue and provided my version of an attempted formula but requires some tweaking. The OUTCOME is listed in the formula if you read it carefully, but more so understand it. The IF statement has quotations " " and that usually signifies the outcome to be displayed in the cell

Happy New Years

[AliGW]

OK - here's a guess:

=IF(E8<=TIME(0,5,0),"0-5",IF(E8<=TIME(0,10,0),"5-10",IF(E8<=TIME(0,20,0),"10-20",IF(E8>TIME(0,20,1),">20"))))

Change the cell references to suit.

If that's not what you mean, then please attach a workbook.

[itshere]

Unfortunately that did not work. The results appeared >20 for all of them - so it didnt understand the time.

TIME COLUMN
0:53:46
0:10:50
0:07:07

[AliGW]

It is working fine here with the three times you have listed. Would you like me to have a look at your workbook?

Excel 2016 (Windows) 32 bit

	E	F
8	00:53:46	>20
9	00:10:50	10-20
10	00:07:07	5-10

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	F
8	=IF(E8<=TIME(0,5,0),"0-5",IF(E8<=TIME(0,10,0),"5-10",IF(E8<=TIME(0,20,0),"10-20",IF(E8>TIME(0,20,1),">20"))))

Sheet: Sheet1

[AliGW]

Have you checked that the time column is not formatted as text?

[itshere]

it was pasted as a value.. so its formatted as GENERAL.

[AliGW]

Then they are text. I'll show you what I mean. Hang on.

[AliGW]

Have a look at this:

Excel 2016 (Windows) 32 bit

	B	C	D	E
2	00:53:46	Copied from your post	0.037338	Copied as values
3	00:10:50	with just copy & paste	0.007523	& formatting comes in
4	00:07:07	& formatting comes in	0.004942	as general
5		as custom (time)

Sheet: Sheet1

[itshere]

I literally have this.. if you paste it into an excel right now

A B
0:53:46 >20
0:10:50 >20
0:07:07 >20
4:45:12 >20
4:02:50 >20

with the following formula

=IF(A5<=TIME(0,5,0),"0-5",IF(A5<=TIME(0,10,0),"5-10",IF(A5<=TIME(0,20,0),"10-20",IF(A5>TIME(0,20,1),">20"))))

[AliGW]

As you have instructed me to do:

Excel 2016 (Windows) 32 bit

	A	B
1	00:53:46	>20
2	00:10:50	10-20
3	00:07:07	5-10
4	04:45:12	>20
5	04:02:50	>20

Sheet: Sheet2

Now, would you like me to have a look at your file (or a sample version of it)?

[AliGW]

Are your times showing flush left or right in the cell? What do you get with this formula in any free cell?

=VALUE(A1)

[AliGW]

Just so that you are aware, I'll be going offline within the next half an hour - it's almost 6pm here - so if you do want me to look at the file or a sample of it, please upload it as soon as you can. It would be nice to resolve this after over two hours of trying!

However, it looks as if you have gone offline now, so maybe someone else will have a look at your file for you when you come back. If your issue has been resolved in the meantime, then please mark the thread as solved using the thread tools at the top. Thanks!

[itshere]

Are your times showing flush left or right in the cell? What do you get with this formula in any free cell?

=VALUE(A1)

when I do that for the first one

A
0:53:46

the result is 0.037337963
when i convert that ^ to time i get 12:53:46 AM

but thats incorrect because what my column should indicate is 53 minutes and 46 seconds.

Thanks

[AliGW]

This shows that the data you have is not displaying properly as time, even though it looks as if it is. You seem reluctant to post the workbook here, but if you change your mind and it still hasn't been resolved in the morning, I'll take a look then. I am away from my computer now, unfortunately.

[itshere]

I tried posting the worksheet here multiple times but keep getting an error so I continued posting as much info I can.

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!