Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    488

    Post Calculating time

    I've checked out previous posts, but couldn't find one quite like mine. I have a form with 3 fields [BeginTime], [EndTime],and [CompTime]. I'm using the 24 hour clock. On the after update of [EndTime] I put this:
    If (EndTime < BeginTime) Then
    Me.CompTime = ((EndTIme + 2400) - BeginTime) / 100
    Else
    Me.CompTime = (EndTIme - BeginTime) / 100


    End If
    This works great for whole hours, however when I start putting in minutes, it gets messed up. I put in [BeginTime] as 2200 and [EndTime] as 0130. It gave a return of 1.3. This is because the clock is based on 6 min and not 100. I need to get it to 1.5 instead of 1.3. Ideas?

  2. #2
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    488
    Correction. I got a return of 3.3. I need a return a return of 3.5.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,850

  4. #4
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    488
    That was pretty confusing for me. Probably need something a little less complicated.

  5. #5
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    488
    I might be able to do this in a query. Is there a way to separate the numbers? The indigent and the decimal? I could have 2 fields and then string them together.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,069
    I think you'd find this easier if your table stored the entire date regardless of how you format it. If begin time was
    01/01/2019 10:00:00 PM and end time was 01/02/2019 01:30:30 AM
    this ought to be an easy DateDiff solution. Leaving out the full date brings lots of people here with such time problems. You can still format the field as short time (military time or hh:nn) to show only 1:30 so I see no reason to not include the date in the underlying data. As it is, you're looking at complex expressions or functions to fix it.
    Last edited by Micron; 02-07-2020 at 10:21 PM. Reason: clarification
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,731
    I got focused on this about 4pm, then got because I couldn't get anything to give the correct answer. But I think I finally figured out the code.

    Do lots of testing.


    Now time for home -> time for this and then this and no more of this

    Tomorrow - have to repair the brakes on my plow truck - in the driveway - in the cold and snow. Blah!!
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,069
    Sorry if this is the same thing but it's late and I'm not about to strain my noodle anymore tonight. Here's a hack (probably not as good as ssanfu's) and you might or might not have to worry about the order of values since it uses DateDiff. Afraid I'm done for the night. Still think the right way is to include the date portion in the table field(s).
    Code:
    Sub testTime()
    Dim dteEnd As Date, dteStart As Date
    Dim sngElapsed As Single
    
    dteEnd = TimeSerial(130 \ 100, 130 - (100 * (130 \ 100)), 0)
    dteStart = TimeSerial(2200 \ 100, 2200 - (100 * (2200 \ 100)), 0)
    sngElapsed = Round(DateDiff("n", dteStart, TimeSerial(23, 59, 59)) / 60) + DateDiff("n", TimeSerial(0, 0, 1), dteEnd) / 60
    
    Debug.Print sngElapsed
    
    End Sub
    ? 3.5
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,731
    @Micron
    Impressive!

    Small changes to use the controls on the form and Ta-Da!
    I have way more lines - had to baby step my brain through the process.

    Sigh........ will I ever catch up to you???
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,850
    Good stuff guys!
    I agree that storing full date makes sense.

    Steve--good luck with the brakes.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,069
    Sigh........ will I ever catch up to you???
    You are delusional, my friend. Is it from brake fluid fumes or something?
    BTW no one beats you with your rapid development/table relationship skills. I marvel at the detail of those and the speed at which you come up with them.

  12. #12
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    488
    All y'all are seriously smart. ssanfu, I think I figured out your code. It's really good. I'm not sure why it didn't work on mine. It gave "24" to every time entered into the fields. I'm going over it to try and figure out why. Micron, that code was so far above my head, I didn't understand much of it at all. I thought that I'd try a copy and paste, but couldn't even figure out how to use it. I know that I should probably use hours and min like Orange put out in that link. I'm trying to calculate time worked so that it can be converted to overtime. That means I need to take the total time and multiply by 1.5. Hours and min doesn't cut it. I have a beginning time and ending time for time worked above the regular hours worked. That's just how things are done at my job. We don't clock in and out. We have set times and anything above that is calculated at overtime rate (1.5). So, I need the time worked in a decimal so I can calculate the overtime.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,069
    If you research the TimeSerial function it will probably make more sense than any simplified explanation I can give here. The important point is, if you tried it with a few time values and they calculate correctly, then it is a starting point given what you're working with. A quick explanation:
    the TimeSerial function needs 3 parts - hours, mins and secs. The reversed slash (from normal) causes only the integer portion to be returned, thus 130\100 gives you hour 1.
    130 - 100*(130\100) = 130-100*1 = 130-100 = 30 That results in the minutes. Now you have hour 1 and minutes 30. The ,0 is the seconds being passed to TimeSerial. This results in TimeSerial(1,30,0) = 1:30:00 which is your clock hour. To use this function you'd have to swap time values with variables so that you can pass any time to it.

    When crossing over midnight I figure you need to calculate the duration before and add it to the duration after. However, it would not accept 00:00:00 as a time value, so I went to one second before midnight and one second after. Because ElapsedTime is a single number type in order to handle fractions of an hour it needs to be rounded before adding the portion after midnight. The ?3.5 at the end shows that using your values, the result was 3.5 as you asked for.

    What I'm not aware of is whether or not you've grasped that by not having the date in these time fields you have made this much more difficult than it needs to be, I think. Maybe go back and review those comments as I don't grasp the meaning of your reference to Orange's comments. These time clocks or time cards do show the date with the times do they not? If they do, I have to believe you can make this easier on yourself. You might get something working now, but if it breaks later, or you have to go back and tweak it, chances are you won't remember what you learned thus won't be able to fix it. If this could be a simple DateDiff function, it would be so much easier.

  14. #14
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,126
    I agree that it would be much better to store both date and time together
    However, I think both solutions offered so far are more complex than necessary

    I've adapted Steve's database and used this function:

    Code:
    Private Function GetElapsedTime() 
    'isladogs - 09/02/2020
    
    Dim dteEndTime As Date, dteStartTime As Date
    
    dteEndTime = Me.EndTime
    dteStartTime = Me.BeginTime
    
    'elapsed time in days
    If dteEndTime < dteStartTime Then 'overnight - add a day
        GetElapsedTime = 1 + CDbl(dteEndTime) - CDbl(dteStartTime)
    Else
        GetElapsedTime = CDbl(dteEndTime) - CDbl(dteStartTime)
    End If
    
    'convert to hours
    GetElapsedTime = 24 * GetElapsedTime
    'Debug.Print GetElapsedTime
    
    End Function
    If date/time were both stored this would simplify to:

    Code:
    Private Function GetElapsedTime() 
    
    Dim dteEndTime As Date, dteStartTime As Date
    
    dteEndTime = Me.EndTime
    dteStartTime = Me.BeginTime
    
    'get elapsed time in hours
    GetElapsedTime = 24*(CDbl(dteEndTime) - CDbl(dteStartTime))
    
    'Debug.Print GetElapsedTime
    
    End Function
    Attached Files Attached Files
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  15. #15
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    488
    Thanks for the help. I don't disagree with y'all on the correct and best way of doing things. I'm building a database for work. Here, they don't like change. Anything other than Word docs and Excel, I get an earful. So not to confuse anyone too much I try to keep things done the same way as much as possible. Thus, using decimal instead of hrs & min. Putting the date with the times might actually be too much to handle. I really appreciate the help. That worked perfectly.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-01-2015, 01:29 AM
  2. Calculating days & Time
    By siapliw in forum Forms
    Replies: 5
    Last Post: 09-11-2012, 11:44 AM
  3. Replies: 8
    Last Post: 08-24-2012, 01:54 AM
  4. Calculating Time
    By jlclark4 in forum Forms
    Replies: 0
    Last Post: 04-25-2011, 09:04 AM
  5. Replies: 12
    Last Post: 02-22-2011, 03:39 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 - Senior Forums