Formula Problem

Topic

New Reply

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

Braddy

Reply | Quote

Replies

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

=IF(OR($C2=”Saturday”,$C2=”Sunday”),B2,IF(OR($E2TIME(17,0,0)),B2,””))

Then fill down and to the right.

Reply | Quote

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

=IF(OR($C2=”Saturday”,$C2=”Sunday”),B2,IF(OR($E2TIME(17,0,0)),B2,””))

Then fill down and to the right.

Hi Hans

Thank you for your prompt and excellent reply.

Regards

Braddy

Reply | Quote

Try this formula in B12:
=IF(OR($C2=”Saturday”,$C2=”Sunday”),B2,IF(OR($E2TIME(17,0,0)),B2,””))

Or, shorten to :

=IF((LEFT($C2)=”S”)+(($E2TIME(17,,))),B2,””)

Regards
Bosco

Reply | Quote

Or, shorten to :

=IF((LEFT($C2)=”S”)+(($E2TIME(17,,))),B2,””)

Regards
Bosco

Hi
Thank you for your input.

Regards

Braddy

Reply | Quote

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.

Braddy

Reply | Quote

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.

Reply | Quote

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

Braddy

Reply | Quote

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

Braddy

Reply | Quote

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.

Reply | Quote

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

Braddy

Reply | Quote