Separate dates/times by specifics

Hi Again

Is it possible to seperate this sheet by

Time - using column F, anything which is between the hours of 07:00 - 09:00 and 16:00 - 19:00?

Response - column I - times up to 60 mins - 61 - 120 mins and over 120 mins

thanks in advance

Buzzby

Hi,

I'm not quite clear what you mean by separating the sheet. If you want to copy data that meets certain criteria, you could

- add a column with the formula =MOD(F2,1) formatted as time and copied down
- sort by that column
- copy the data that meets your time requirements and paste it somewhere else.

hth

And when you've identified them, then what? Do they stay where they are? would you like to colour them in a different colour? would you like to move them to another sheet?

??

Easy things first:

Perhaps I should have used 2 threads as I still would like to know how to sort column I by the given numbers ie up to 60 mins etc.

create another column next to column I with the formula

=LOOKUP(I2,{1,61,121},{"1 - <= 60","2 - 61 - 120","3 - >120"})

and copy down. sort to your heart's content.

Now for the coloring.
You can use conditional formatting to highlight rows that meet a certain condition. Your condition in this case is that the time portion of the date/time value in column F must be between x:00 and y:00

Assuming that you want different colors for the two time frames you can do:

- Select the whole table
- click Format - Conditional formatting
- click Formula Is
- enter the first formula in the condition box
- select a background color

then click Add to add a second condition, this time with the second formula and then select a different background colour.


for 7:00 - 9:00
=AND(MOD($F2,1)>=0.666667,MOD($F2,1)<=0.791667)

For 16:00 - 19:00
=AND(MOD($F2,1)>=0.291667,MOD($F2,1)<=0.375)

If you do not want two different colors for the two time frames, just combine the two formulas into one:

=OR(AND(MOD($F2,1)>=0.666667,MOD($F2,1)<=0.791667),AND(MOD($F2,1)>=0.291667,MOD($F2,1)<=0.375))

(or do the two conditions above and assign them the same color )


In case you're wondering what these funny numbers are and how I got them, just enter 16:00 in a cell. It will display as a time. Now format the cell to show "General" format and you will see a number. That is how Excel stores times.

hth