Possible to search in Excel columns?

[Pharmagirl]

8:41:18 AM*
8:43:12 AM*
9:53:24 AM*
10:05:00 AM*
10:39:42 AM*


My boss and I are trying to find a way to search excel columns for times before 7am and After 730pm.

This is just a small sample of the worksheet. Is there a way to do this? Would it help if the hours were in miltary time?

Thanks for any help you can give.

PG

[swatsp0p]

It is possible. What do you want to do once you find the times?

If you simply want to flag them, first in two cells (say A1 & B1) enter 7:00:00 AM and 7:30:00 PM (format as time). Then in the column next to your times (assume col D), enter this formula (adjust cell references as needed):

=IF(OR(C1<$A$1,C1>$B$1),"Flag","OK") and copy this down your range of times.

Any cell in Column C that is before or after your times in cells A1:B1 will return "Flag" in col D. Otherwise, "OK" will be returned.

Of course you can edit the "Flag" and "OK" values to meet your needs.

HTH

[Pharmagirl]

Bruce, thank you for the info. I'm going to try it now.

I appreciate the help.

PG

It is possible. What do you want to do once you find the times?

If you simply want to flag them, first in two cells (say A1 & B1) enter 7:00:00 AM and 7:30:00 PM (format as time). Then in the column next to your times (assume col D), enter this formula (adjust cell references as needed):

=IF(OR(C1<$A$1,C1>$B$1),"Flag","OK") and copy this down your range of times.

Any cell in Column C that is before or after your times in cells A1:B1 will return "Flag" in col D. Otherwise, "OK" will be returned.

Of course you can edit the "Flag" and "OK" values to meet your needs.

HTH

[swatsp0p]

Another option, if it meets your needs, is to use Conditional Formatting to 'highlight' the cells with the out lying values.

in the top cell of your range, click Format>Conditional Formatting.... Set Condition 1 to Formula Is and enter: =OR(B10<$A$1,B10>$B$1) in the formula box. Set your formatting as desired (Change font color or cell fill color on the Patterns tab, etc.).

Copy this formatting down the range of times.

Further, you could use two conditions: =B10<$A$1 in cond 1 and then use Add>> to set cond 2 as =B10>$B$1. Then early hours could be one color, late hours another.

Show this to your Boss and ask for the rest of the day off!

Good Luck.

[AlfD]

Hi!

I would use Data > Filter > Autofilter.

Add the two times you are interested in to the list if times you have.
Start the autofilter and go for Custom.

Choose "Greater than or equal to" for the first condition and select 07:00:00AM from the second dropdown list of times

Then select the "And" button.

Then choose "less than or equal to" and 07:30:00PM from the second pair of list boxes.

Alf

[Pharmagirl]

Thanks for the help, but i think i've confused people, mostly myself.

I need the formula to Show me the times BEFORE 700am and AFTER 730pm, ie, 638am, 257am 818pm, like that.

Is there a formula that will do this?

Thanks again for your help.

PG

[swatsp0p]

PG:

Both of my suggestions will highlight those times before 7 am and after 7:30 pm. AlfD's suggestion will hide all values after 7 am and before 7:30 pm (leaving only those you seek visible).

You say you want the formula to "Show" you the times. The conditional formatting option can make those times Stand Out by changing the background color of the cell and/or the Font color/size. Try that out and see if it meets your needs.

Have you tried any of them out? What is the result? What did you want it to do? What will you do with the data once it is found? Do you need to move it, change it, delete it, etc.?

Let us know if we can be of further help. Provide explicit details of what you are trying to accomplish.

Good Luck.

[AlfD]

Hi!

No: your original post was quite precise on the matter. Mea culpa!

In my suggestion simply swap the greater than and less than conditions and change the And into an Or.

Alf

[Pharmagirl]

PG:

Both of my suggestions will highlight those times before 7 am and after 7:30 pm. AlfD's suggestion will hide all values after 7 am and before 7:30 pm (leaving only those you seek visible).

You say you want the formula to "Show" you the times. The conditional formatting option can make those times Stand Out by changing the background color of the cell and/or the Font color/size. Try that out and see if it meets your needs.

Have you tried any of them out? What is the result? What did you want it to do? What will you do with the data once it is found? Do you need to move it, change it, delete it, etc.?

Let us know if we can be of further help. Provide explicit details of what you are trying to accomplish.

Good Luck.

Bruce, I have tried your suggestions. With the first one that you gave me, my list read 'Flag' all the way down, but the times were 814am and such. With Alf's suggestion, for whatever reason, it only listed one time.

It could be that I'm punch drunk from having to look at this information from last week up to today and/or a case of bad typing on my part. I am definitely going to try them again.

What I'm trying to accomplish is being able to search, preferably in two hour blocks, any and all times that are before 7am and 730pm.

However, as long as I can highlight those times, or differentiate them in some fashion, moving the information is/should be unnecessary.

Thanks
PG

[swatsp0p]

PG: Is it possible that your "times" are not truly Times as Excel knows them? I just noticed the "*" in your original post next to the times. If these times are TEXT, none of these options will work.

Test to verify: in a cell place this formula: =ISTEXT(B10) adjust B10 to match the cell with the "time" in it. If this returns TRUE, then your time is actually text!!!

If that is the case, and the '*' is part of your time, that will need to be removed and the text converted to a value.

First, make sure you have saved this file (make a backup, just in case....)

Insert a helper column next to your times

In the top cell of this new column, enter this formula:

=LEFT(B10,LEN(B10)-1) and copy down your range (this will return all but the *)

Next, highlight this new range (the one without the *) and from the menu select:

Edit>Copy and then Paste Special> select "Values" and click OK

Finally, with the range still selected, from the menu select Data>Text to Columns

No changes need to be made here, simply click Finish. Now all of your new column should be true Times to Excel. Make sure this range is formatted in your desired Time format.

Now apply your desired formula/formatting as suggested earlier by either AlfD or myselft to this range.

Good Luck

[AlfD]

Hi!

I could (should) kick myself. Of course I removed the asterisks to make it work...

BTW the reason for adding the boundary times to the list of times was to ensure they would be compatible in format etc.

Alf