Results 1 to 2 of 2

Thread: Help with some time.

  1. #1

    Help with some time.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Forum, I'm really new here but I've use this page several times for help with excel, but now I have a problem that I'm not able to resolve.

    I have a situation, I need to check if one of my workers is leaving before his shift ends. We have a time stamp machine that generates the time, but I need to check if they retired early. What I'm currently using is this:

    OFF RET SET IF RET IF SET Real End Time Scheduled End Time Early Out
    10:00 PM 10:00 PM 10:00 PM 0.916667 0.916667 22:00:00 22:00:00 No
    - 2:17 PM 12:00 AM 0.595139 0.999999 14:17:00 0:00:00 STAFF
    2:00 AM 3:55 PM 2:00 AM 0.663194 0.083333 15:55:00 2:00:00 No
    11:00 PM 10:00 PM 11:00 PM 0.916667 0.958333 22:00:00 23:00:00 Yes
    2:00 AM 7:40 PM 2:00 AM 0.819444 0.083333 19:40:00 2:00:00 No
    2:00 AM 12:16 AM 2:00 AM 0.011111 0.083333 0:16:00 2:00:00 Yes
    2:00 AM 8:00 PM 2:00 AM 0.833333 0.083333 20:00:00 2:00:00 No
    2:00 AM 2:06 AM 2:00 AM 0.0875 0.083333 2:06:00 2:00:00 No
    11:00 PM 6:34 PM 11:00 PM 0.773611 0.958333 18:34:00 23:00:00 Yes
    2:00 AM 11:00 PM 0.083333 0.958333 2:00:00 23:00:00 Yes

    Formula for Early Out

    =IF($H69="-","STAFF",IF($L69<$M69,"Yes","No"))

    Sorry if it's a little messy but attached is the file. The issue I'm havinTest_Early Outs.xlsxg is that if it's past 12am and the employee retires a couple of minutes after it appears as he/she retired early with clearly it didn't.

    If someone could help it will be grand.

    Thanks for the support.

    EO

  2. #2
    The problem is that 12:00AM in your worksheet is not being stored as 0, it is held as 0.999999. See id this works for you

    =IF($H4="-","STAFF",IF($C4<IF(TEXT($D4,"hh:mm am/pm")="12:00 AM",0,$D4),"Yes","No"))

Tags for this Thread

Posting Permissions

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