1. I*am hoping that you may be able to help me to select specific data . The problem which I haven't yet been able to crack is to show a whole*row of data with the following selection*criteria:-
a). Show the entire row if the 'day' is either a 'Saturday' or a 'Sunday' and
. Show the entire row for any weekday if the 'time' is*before "08:00" or after "17:00"

Regards

2. The order in which you test is wrong. Try this formula in B12:

=IF(OR(\$C2="Saturday",\$C2="Sunday"),B2,IF(OR(\$E2<T IME(8,0,0),\$E2>TIME(17,0,0)),B2,""))

Then fill down and to the right.

3. Originally Posted by HansV
The order in which you test is wrong. Try this formula in B12:

=IF(OR(\$C2="Saturday",\$C2="Sunday"),B2,IF(OR(\$E2<T IME(8,0,0),\$E2>TIME(17,0,0)),B2,""))

Then fill down and to the right.
Hi Hans

Regards

4. Originally Posted by HansV
Try this formula in B12:
=IF(OR(\$C2="Saturday",\$C2="Sunday"),B2,IF(OR(\$E2<T IME(8,0,0),\$E2>TIME(17,0,0)),B2,""))
Or, shorten to :

=IF((LEFT(\$C2)="S")+((\$E2<TIME(8,,))+(\$E2>TIME(17, ,))),B2,"")

Regards
Bosco

5. Originally Posted by bosco_yip
Or, shorten to :

=IF((LEFT(\$C2)="S")+((\$E2<TIME(8,,))+(\$E2>TIME(17, ,))),B2,"")

Regards
Bosco
Hi

Regards

6. Hi

Hans and bosco_yip kindly supplied me with a formula which worked perfectly in the sample worksheet but when I applied it to the actual worksheet it does not work as required, for example in cheshire out of hours rows with n should not appear, any help would be much appreciated.

7. For others reading this: this should have been a reply in http://www.wopr.com/...howtopic=767898

In the "Report 1" sheet, the value in cell E7 is a real time value, but the values in E8, E9 and E10 are text values, even though they look like times.
Select E8, press F2, then press Enter without changing anything. This will convert the value to a time. Repeat for E9 and E10.

8. Originally Posted by HansV
For others reading this: this should have been a reply in http://www.wopr.com/...howtopic=767898

In the "Report 1" sheet, the value in cell E7 is a real time value, but the values in E8, E9 and E10 are text values, even though they look like times.
Select E8, press F2, then press Enter without changing anything. This will convert the value to a time. Repeat for E9 and E10.
Hi Hans

My apologies, I wasn't too sure where to post this,I will take more care in the future.

Thank you for the explanation.

Regards

9. Originally Posted by HansV
For others reading this: this should have been a reply in http://www.wopr.com/...howtopic=767898

In the "Report 1" sheet, the value in cell E7 is a real time value, but the values in E8, E9 and E10 are text values, even though they look like times.
Select E8, press F2, then press Enter without changing anything. This will convert the value to a time. Repeat for E9 and E10.
Hi Hans

If you could indulge me just once more on this, I have 2000 rows to press F2 and enter is there a way I can automate this, I tried a macro with no success.

Best Regards

10. Select the column with the times (on the Report 1 sheet).
Press Alt+F11 to activate the Visual Basic Editor.
Press Ctrl+G to activate the Immediate window.
Type

Selection.Value = Selection.Value

Press Enter.

11. Originally Posted by HansV
Select the column with the times (on the Report 1 sheet).
Press Alt+F11 to activate the Visual Basic Editor.
Press Ctrl+G to activate the Immediate window.
Type

Selection.Value = Selection.Value

Press Enter.
Hi Hans

Does your genius have no bounds.

Many Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•