Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: help on a time calculation please

    Hey guys,

    Can someone please help me on a time calculation coding.

    I have 5 txt boxes on a form

    txtSDate (this is for Start Date)
    txtSTime (this is for Start time in HHMM format)
    txtFDate (this is for Finish Date)
    txtFTime (this is for Finish Time)
    txtTimeworked (I want this showing in hours and minutes)

    Please can someone help me in this calculation as im lost with regards to the syntax.

    Regards

    Aboo

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Time is hard cal out

    as you want hours and minutes

    1st let find how mins there are then div that by 60 which give us hours find out what let that must be the mins

    Dim txtStartTime as Date
    Dim txtEndTime as Date
    Dim TotalMins as long

    txtStartTime = txtSDate & " " & txtSTime
    txtEndTime = txtFDate & " " & txtFTime

    TotalMins = DateDiff("n",txtStartTime,txtEndTime)


    THours = int(TotalMins / 60) ' Number of hours only want the hole hours INT() drop every thing after the .
    TempHour = THours * 60


    so the differance between the THours and TempHour will be less the 60 there must be the Mins left over.

    TotalMins = THour - Temphour

    txtTimeworked = THour & ":" & TotalMins


    So that it now leet make a function to do the job

    Code:
    Function TotMins(Stime As Date, Etime As Date)
          Dim TotalMins As Long
          TotalMins = DateDiff("n", Stime, Etime)
          THours = Int(TotalMins / 60)  ' Number of hours
          Temphour = THours * 60
          TotalMins = TotalMins - Temphour
          TotMins = THours & ":" & TotalMins
    End Function

    if you want 01:09 format

    TotMins = format(THours,"00") & ":" & format(TotalMins,"00")




    now now to get it working

    open a module and paste the code into close the module save it

    then in the form you can just call it

    txtStartTime = txtSDate & " " & txtSTime
    txtEndTime = txtFDate & " " & txtFTime

    txtTimeworked = TotMins(txtStartTime, txtEndTime)

    or

    txtTimeworked = TotMins(txtSDate & " " & txtSTime, txtFDate & " " & txtFTime)

    trun the txtTimeworked into a unbound box and '= TotMins(txtSDate & " " & txtSTime, txtFDate & " " & txtFTime)' in the control source.



    aboo a test for you can you change the code so its work out the days also....

    ie 25:30 = 1:01:30
    Last edited by myle; 02-15-09 at 21:33.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2006
    Posts
    178
    Myle,

    Thankyou for your reply . I will try this today. thanks again!....

    I will take up the challenge of the test.... remember though... your gonna pay for the asprin after i develop a headache!..lol

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wonder if I can claim back for asprin on expenses?
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the problem I see here is how do you work out the elapsed time
    are you looking for true elapsed time or elapsed working time.

    true elapsed time is relatively trivial
    make both the start and end date a date time value
    then do a datediff().. that will give you the elapsed time in wahtever units you want.
    essentially this is Myles suggestion. personally I'd want to factor in a bit more error checking to make sure the proposed values are valid dates and times. mind you if it was me I'd only be displaying date and time as separate values if I had to.. far better to use the inherent date/time datatype.

    however if you want elapsed working time then you need to know start/finish times per day, non working days.
    you calculate the number of full working days (you probably need a working calendar). it gets more complicated if you allow part working days (eg say Saturday's)
    the amount of time between the event start and the close of business that day.
    the amount of time between the opening of business that day and the event end time.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2006
    Posts
    178
    Hey guys thanks for your replies.

    Well essentially its is as follows.


    A user will input a start date and a start time then a finish date and a finish Time.

    So here is an example.

    Start date: 17/02/09 Start time: 23:00hrs

    Finish date: 18/02/09 Finish time: 06:00hrs


    i want the result to obviously show 7 hours.

    You guys see what i mean?


    Thanks

    Aboo

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so myles suggestion should meet your requirements
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Quote Originally Posted by aboo
    Myle,

    Thankyou for your reply . I will try this today. thanks again!....

    I will take up the challenge of the test.... remember though... your gonna pay for the asprin after i develop a headache!..lol

    And yess you will get a headache

    and the asprin will come when you see it working and test a

    01/01/09 23:00 to 02/05/09 14:45 and it returns the right answer.

    LOL
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    May 2006
    Posts
    178
    Myle,

    Did I pass?

    Code:
    Function TotMins(Stime As Date, Etime As Date)
          Dim TotalMins As Long
          TotalMins = DateDiff("n", Stime, Etime)
          Days = Int(TotalMins / 1440)  ' Number of days
          TempDays = Days * 1440
          TotalMins = TotalMins - TempDays 'Number of hours left over after deducting the days(in minutes)
          Thours = Int(TotalMins / 60)  ' Number of hours
          Temphour = Thours * 60
          TotalMins = TotalMins - Temphour
          'TotMins = THours & ":" & TotalMins
          TotMins = Days & ":" & Format(Thours, "00") & ":" & Format(TotalMins, "00")
    
    End Function

    Woot Woot!

    Did I pass?

    Took me over an hour.......lol........

    almost forgot the answer is it........120:15:45


    Aboo

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    You Pass

    well done

    how many Differnace version can we get here.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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