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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Steve Hartman

Active Member
Joined
Feb 17, 2002
Messages
417
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?
 

amcmillio

New Member
Joined
Oct 8, 2002
Messages
13
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
 

amcmillio

New Member
Joined
Oct 8, 2002
Messages
13

ADVERTISEMENT

I would want to keep rows 1,3,6,9,12,14,17,20,23,25
 

Steve Hartman

Active Member
Joined
Feb 17, 2002
Messages
417
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...
 

Steve Hartman

Active Member
Joined
Feb 17, 2002
Messages
417

ADVERTISEMENT

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

amcmillio

New Member
Joined
Oct 8, 2002
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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
Top