Results 1 to 6 of 6
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to subtract time?

    Hello everyone - I have the three tables below:



    AddHours - contains number of hours that should be added to the "Out of State" table (depending on date).
    Out of State - contains date, time and account
    Local - contains date, time and account

    I also have a query (HoursAdjusted) where hours are added to the time field in the Out of State table. I also have a Differences query where I subtract the time fields from the Local table and HoursAdjusted query. So the problem is as you can see in act 1 and 6 of that query, When its ran I get 33 hours because the HoursAdjusted query pushes that record to the next day (I get why but don't want that). The results for those rows should be 9 hours, the other records are right.

    Any idea how I can get 9 hours and not 33? also, if there are minutes and seconds, I would like the difference.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    add /subtract hours:

    DATEADD(“h”....)

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You need to include the date for each time.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Instead separate date and time fields, use datetime field (format like "m.d.yyyy hh:nn:ss"). The new time will be calculated as (DateTime + HourDiff/24), or you can use dateadd function.
    The results for your data will be like:
    03.07.2021 06:15:24
    03.08.2021 09:26:36
    04.09.2021 00:13:40
    03.10.2021 09:02:14
    03.11.2021 09:15:18
    03.12.2021 07:17:19
    (I used different date format here)

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    BE AWARE that "Date" and "Time" are reserved words and built in functions and shouldn't be used for object names.


    Quote Originally Posted by MsAxes View Post
    <snip>Any idea how I can get 9 hours and not 33?<snip>
    Since 33 is greater than 24 hours, you need to subtract 24.
    In the query "Differences", change the "Diff" column to
    Code:
    Diff: DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime])-(24*(Abs(DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime])>24)))
    From left to right:
    DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime]) -->> calculates the difference
    (24*(Abs(DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime])>24))) -->> calculates the difference and checks if the diff is greater than 24. Results in TRUE (-1) or FALSE (0). the ABS() function changes TRUE from -1 to 1. Then the result is multiplies with 24. This gives you 24 or 0, which is subtracted from DIFF (33) to return 9.



    Quote Originally Posted by MsAxes View Post
    <snip> also, if there are minutes and seconds, I would like the difference.
    When you subtract two times, you get an elapsed time or duration. You cannot (and should not) write a duration using colons. Anything with a colon is a time.
    So you cannot say it took you 1 hour and 16 minutes and write it 1:16:00.


    See the article by Allen Browne Calculating elapsed time
    Also, see the Alternative functions at the bottom of the page.

  6. #6
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    ssanfu said So you cannot say it took you 1 hour and 16 minutes and write it 1:16:00.
    I think you will find that is the exact opposite of what Allen Browne actually said:


    Allen Browne said:To display this value as hours and minutes on your report, use a text box with this Control Source:

    =[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
    he literally says, display it as time, with a colon.


    take care,


    Cottonshirt

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

Similar Threads

  1. Replies: 1
    Last Post: 04-09-2018, 12:35 PM
  2. add or subtract
    By MdHaziq in forum Queries
    Replies: 2
    Last Post: 10-05-2017, 10:23 PM
  3. Subtract Groups
    By davetedwards in forum Reports
    Replies: 4
    Last Post: 12-30-2013, 10:56 AM
  4. Subtract from Previous Row
    By lambo102 in forum Queries
    Replies: 1
    Last Post: 08-06-2011, 09:39 AM
  5. How to do subtract in Query
    By NoOoN in forum Queries
    Replies: 6
    Last Post: 04-07-2011, 01:15 PM

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