Results 1 to 11 of 11

Thread: Formula Problem

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    Attached Files Attached Files
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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

    Thank you for your prompt and excellent reply.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by bosco_yip View Post
    Or, shorten to :

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

    Regards
    Bosco
    Hi
    Thank you for your input.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    Attached Files Attached Files
    If you are a fool at forty, you will always be a fool

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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
    If you are a fool at forty, you will always be a fool

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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
    If you are a fool at forty, you will always be a fool

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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
    If you are a fool at forty, you will always be a fool

Posting Permissions

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