+ Reply to Thread
Results 1 to 3 of 3

If OR AND NESTED formula HELP

  1. #1
    Registered User
    Join Date
    04-10-2019
    Location
    USA
    MS-Off Ver
    Excel
    Posts
    2

    If OR AND NESTED formula HELP

    Here's the what I'm trying to figure out: TS policy states that you are compliant if you enter your time in the TS system for the current day on the next day before 10 am

    I have the hours date in column A; Actual Hours in Column B; Date the hours were entered in Column C; Time the hours were entered in Column D; Result 1 in Column E

    For the first result - I want to know if the person filled in the time sheet daily. - this one easy..
    '=IF(A1=C1,"Daily Entry","No Entry").

    The next result I want is to see if the TS entry was compliant with company Policy - which is you must enter Today's time by 10 am tomorrow...

    =IF(AND(A1=C1,E1="Daily Entry"),"Compliant",IF(OR(A1=C1,AND(A1+1=C1,D1<"10:00:00 AM")),"Compliant within Policy","Non-Compliant"))... this formula is not giving the correct result .. is will say compliant even if the time the hours were entered are after 10 am...

    Any help is appreciated.
    Thanks, UMD

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,717

    Re: If OR AND NESTED formula HELP

    Your logic is overly complicated. For compliance with policy you just need to check whether the time of entry was less than the 10 AM the day following the day that the time was worked:

    For compliance with company policy:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-10-2019
    Location
    USA
    MS-Off Ver
    Excel
    Posts
    2

    Re: If OR AND NESTED formula HELP

    Hi Jeff,
    Yes logic is overly complicated.. Thanks for the simplification! I also believe the Formatting of the Time Entry is causing a problem.. Really appreciate your help!
    UMD

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Nested IF and nested Concatenation formula
    By VisionSmart in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-09-2018, 08:00 AM
  2. Help with a nested, nested, nested formula
    By duanrd2 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2017, 04:43 PM
  3. Replies: 2
    Last Post: 08-24-2014, 04:56 AM
  4. Possible nested IF formula...
    By yekom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-01-2011, 12:38 PM
  5. Excel 2007 : Nested If Formula
    By Aeriuzi in forum Excel General
    Replies: 2
    Last Post: 03-02-2011, 08:20 PM
  6. Replies: 2
    Last Post: 10-08-2009, 09:51 PM
  7. help on a nested countif/nested if formula
    By vickiemc in forum Excel General
    Replies: 2
    Last Post: 08-13-2008, 08:29 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1