Results 1 to 7 of 7
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    Trying to Calculate Hrs Worked Minus Lunch Period

    Hello Hello...

    I'm trying to write a query which includes a field that calculates time worked minus 30 minutes for lunch if the total hours worked is => 6:30:00

    What I'm trying are 'many' variations of the following

    Code:
    IIf((Format([TimeOut]-[TimeIn]*24,"Short Time")=>"6:30",(Format([TimeOut]-[TimeIn]*24,"Short Time")-"00:30",(Format([TimeOut]-[TimeIn]*24,"Short Time"))
    The few attempts that have made it past compilation produce the wrong results...

    Help?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I'm not mistaken, the order of equals and less/greater than must be >= or <= and can't be as you have it.
    I'll try playing with the rest of your expression.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Since I don't have your field data to work with, I tested as
    Code:
    Sub testExpression()
    'IIf((Format([TimeOut]-[TimeIn]*24,"Short Time")=>"6:30",
    '(Format([TimeOut]-[TimeIn]*24,"Short Time")-"00:30",
    '(Format([TimeOut]-[TimeIn]*24,"Short Time"))
    
    Dim TimeOut As Date, TimeIn As Date
    
    TimeIn = Format("07:00", "short time")
    TimeOut = Format("15:30", "short time")
    Debug.Print "time in: "; TimeIn & ";  " & "time out: " & TimeOut
    Debug.Print Format(TimeOut - TimeIn * 24, "short time")
    
    If Format(TimeOut - TimeIn * 24, "short time") >= Format("06:30", "short time") Then
        MsgBox "True"
    Else
        MsgBox "false"
    End If
    
    End Sub
    and it seems to work. I also wrapped your comparison values (e.g. 06:30) in the format function to ensure text comparisons ("06:30") weren't being made.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If timein and timeout are date/time type, I would suggest you not use formatting at all in your calculations. All you need is the datediff function, and look at the difference in minutes:

    The other problem you have is that you cannot format a "length" of time; the format statement when applied to date/time is used to format a moment in time, not a length of time. The expression below will give you the length of time in decimal hours (which is what is usually used to calculate pay):

    IIf((datediff("n",Timein, Timeout) > 390,(datediff("n",Timein, Timeout)-30)/60,datediff("n",Timein, Timeout)/60)
    You want to use > 390 there, and not >=390 , because 390 minutes - 6.5 hours - is OK.

    But you want to rethink your logic here, otherwise you are going to have some very upset employees. If an employee clocks in even one minute early, or clocks out one minute late, then the time difference will be >390, and you will be docking them 30 minutes pay for nothing.

  5. #5
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Micron & JG...

    Two brilliant solutions...Can't thank either of you enough. When it comes to Microsoft & Time calculations and formatting... Well, you know the rest...

    Thank you, both very much... On to the next problem...

  6. #6
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Whelp... I added a Round argument to the above formula and now, yeah... It stopped working properly. I'm sure it's just a parenthesis misplaced or too many of them...

    The formula still works great until the Emp works less than 6.5 hour or 390 minutes, then the formula returns a negative decimal

    Code:
    Round(IIf(DateDiff(""n"",[FirstLogIn],[LastCase])>390,(DateDiff(""n"",[FirstLogIn],[LastCase])-30)/60,DateDiff(""n"",[FirstLogIn],[LastCase]/60)),1)
    As for the 1 minute here or there... This program is designed to compare values between two systems until I can redesign the entire time keeping system

    As always, Thank You for your help

  7. #7
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    SOLVED...

    Moved one of the parentheses after the 60 to before the divided sign and Viola!

    This is working (for now)
    Code:
    Round(IIf(DateDiff(""n"",[FirstLogIn],[LastCase])>390,(DateDiff(""n"",[FirstLogIn],[LastCase])-30)/60,DateDiff(""n"",[FirstLogIn],[LastCase])/60),1)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-30-2016, 09:15 AM
  2. Replies: 7
    Last Post: 07-02-2014, 09:30 AM
  3. Replies: 5
    Last Post: 07-01-2014, 02:28 PM
  4. Replies: 4
    Last Post: 01-25-2013, 05:20 AM
  5. calculate rate * hours worked
    By hamish mather in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 06:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums