time-rounding,and if then statements

amcmillio

New Member
Joined
Oct 8, 2002
Messages
13
I have a spreadsheet that has 2000 points of time listed. The time is in 22 minute incremants. I only want the cells with the time that are closest to the hour. It is never the same. How can I delete the cells that are not closest to the hour without doing it manually? I have 40 other spreadsheets to do the same with. Please help me to find a way to stop doing it manually and get rid of my headache. Is there a formula I can use? I am not use to using time in formulas.

Example:
11:06 (I would keep)
11:28 (I would delete)
11:50 (I would keep)
12:12 (I would delete)
12:34 (I would delete)
12:56 (I would keep)
1:18 (I would delete)
Etc...
This message was edited by amcmillio on 2002-10-09 13:25
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You want to delete the times between hh:15 and hh:45, correct? Are you just going to delete the cell or the whole row?
 
Upvote 0
Unfortunately I cannot make it concrete to say that it would be 15 minutes before or after an hour. And I would have to delete the whole row not just the cell. Sorry for the confusion. Here is in excert from my spreadsheet:
Point Date Time Sensor1
1 8/23/02 11:06:13 AM 75.8
2 8/23/02 11:28:13 AM 75.6
3 8/23/02 11:50:13 AM 75.3
4 8/23/02 12:12:13 PM 87.8
5 8/23/02 12:34:13 PM 92.4
6 8/23/02 12:56:13 PM 97.8
7 8/23/02 1:18:13 PM 101
8 8/23/02 1:40:13 PM 102.9
9 8/23/02 2:02:13 PM 104.8
10 8/23/02 2:24:13 PM 105.6
11 8/23/02 2:46:13 PM 105.8
12 8/23/02 3:08:13 PM 106.3
13 8/23/02 3:30:13 PM 107.2
14 8/23/02 3:52:13 PM 108.5
15 8/23/02 4:14:13 PM 109.5
16 8/23/02 4:36:13 PM 110.1
17 8/23/02 4:58:13 PM 110.7
18 8/23/02 5:20:13 PM 111.3
19 8/23/02 5:42:13 PM 111.8
20 8/23/02 6:04:13 PM 111.3
21 8/23/02 6:26:13 PM 109.5
22 8/23/02 6:48:13 PM 107.3
23 8/23/02 7:10:13 PM 105
24 8/23/02 7:32:13 PM 102.9
25 8/23/02 7:54:13 PM 100.9
26 8/23/02 8:16:13 PM 98.8
27 8/23/02 8:38:13 PM 97.1
 
Upvote 0
On 2002-10-09 14:26, amcmillio wrote:
I would want to keep rows 1,3,6,9,12,14,17,20,23,25

Ok, so you are keeping the times between 15 minutes before the hour(hh:45) and 15 minutes after the hour (hh:15) and deleting the times between hh:15 and hh:45.

Working on it...
 
Upvote 0
On 2002-10-09 14:26, amcmillio wrote:
I would want to keep rows 1,3,6,9,12,14,17,20,23,25

Ok, so you are keeping the times between 15 minutes before the hour(hh:45) and 15 minutes after the hour (hh:15) and deleting the times between hh:15 and hh:45.

Working on it...
 
Upvote 0
I am for the most part using the time in between 15 minutes before the hour and 15 minutes after the hour but not always. If you notice I would not keep rows 4, 15 and 22 which are in between 15 minutes before amd 15 minutes after the hour.

Ugh. I really appreciate your help.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top