Results 1 to 6 of 6
  1. #1
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142

    Calculate time between dates.


    On my DB I have a part of My data entry form where I enter I a person's clock in time and clock out time. This works great as long as that is on the same day. However, if a person clocks in on one day ( 1:30 pm) and out the next (12:30 AM) all hell breaks loose. Currently the entry fields are set to medium time. I tried setting it to general date/time but when I enter (11/09/2018 1:30:00 PM) I get a wrong format for the field message. what should I put, or is there another solution so I can get the proper time back?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think your problem is that access has no way of knowing that the second time entry refers to a different date. You could clarify by having data stored in the table as 12/11/2018 02:00:00 and entering data in your form in that way. You could set the Default value of your data entry controls to save some typing new entries.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    In case you use a fields in Time format, only the the current time is entered and entries matches specific conditions (p.e. you have an log form, where you register event's start and end times, no event can take more time as 23:59:59, and you have on form a disabled control for time with default as NOW(), then you can calculate event duration as
    Code:
    Iif(EventEndTime<EventStartTime, 1, 0) + EventEndTime - EventStartTime
    Of-course you can use this only for certain log type tables. When start and end times are recorded in same row, or time difference can be 24 hours or more, you have to use datetime fields as Bob adviced.

  4. #4
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    I understand that I need to add the date to the time field in order for access to understand I am talking about two time fields from two different dates. My issue is I can't set the format of the field correctly to put in both the date and the time together. The input mask for the field right now is set to medium time. That is to say (99:00\ >LL;0;_ is 01:30 pm).

    When I go into the data tap of the property's window for the entry field the input mask builder does not give the option for both date and time but rather wants one or the other. Leaving it blank does not work and combining date and time into one mask does not work. I have been working on this for two days with no luck.
    what do I have to do to get both date and time into this thing?

    I know this might not be possible but I would prefer to not have to enter in the date each time I enter a time. besides clock in and clock out time I have to enter individual task time. The majority of which will not need a date since task start and task stop times happen on the same date. Usually for second shift it is only the last task witch overlaps. If I have to add date to every time field then it will greatly slow data entry down and decreasing data entry time was the whole reason for moving from excel to access.
    Attached Files Attached Files

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Does the following expression return the correct value for you when used in the query "qryAssocWorkDays"

    WkHrs: Abs(Round((DateDiff("n",[wdClockInTime],[wdClockOutTime])-35)/60,2))
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Calculate between Dates
    By AJ_25 in forum SQL Server
    Replies: 5
    Last Post: 07-18-2018, 08:36 AM
  2. Replies: 8
    Last Post: 07-14-2017, 07:23 AM
  3. Calculate Dates
    By dave_282 in forum Access
    Replies: 5
    Last Post: 01-14-2016, 09:46 AM
  4. Calculate time between two working dates
    By Alprashant in forum Access
    Replies: 15
    Last Post: 10-10-2013, 03:19 PM
  5. Replies: 42
    Last Post: 03-01-2013, 06:58 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