1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2010 Total Time and Date Values

Discussion in 'Business Applications' started by Bradybunch, Dec 1, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    Hello guys,

    I have a report which has (User, Office, Problem, Date time open, Date Time Close, and Total Date time of job.) TotalDateTime calcultates the time from when it was open to when it closes.

    The report shows the total of all jobs so i can print it out. At the bottom of the report i want it to tell me the total time of all the TotoalDateTime records, i.e:

    Joe Bloggs Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes
    Jack Black Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes

    TotalOverPeriod - 2 Days, 1 Hour, 50 Mintues

    I cant seem to make it total up...Anyone know how to do this? :confused:

    Thank you
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    As you have found it is not straight forward because you have to add the minutes first to get the hours to be added to the hours total, then you have to calculate the hours to get the days to add to the days total and then calculate the days total.
    It may be possible using queries, but if not it will need VBA code.
     
  3. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    Gosh that sounds a bit mad! I had to do something simular to get it to calculate the difference from open to close using the following code: (Something i found on Microsoft, but it does not really help me with what im trying to do - i dont think?)

    Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
    '*********************************************************************
    ' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
    ' Returns the time elapsed between a starting Date/Time and an ending
    ' Date/Time formatted as a string that looks like this:
    ' "10 days, 20 hours, 30 minutes, 40 seconds".
    '*********************************************************************
    Dim interval As Double, str As String, days As Variant
    Dim hours As String, minutes As String, seconds As String
    If IsNull(dateTimeStart) = True Or _
    IsNull(dateTimeEnd) = True Then Exit Function
    interval = dateTimeEnd - dateTimeStart
    days = Fix(CSng(interval))
    hours = Format(interval, "h")
    minutes = Format(interval, "n")
    seconds = Format(interval, "s")
    ' Days part of the string
    str = IIf(days = 0, "", _
    IIf(days = 1, days & " Day", days & " Days"))
    str = str & IIf(days = 0, "", _
    IIf(hours & minutes & seconds <> "000", ", ", " "))
    ' Hours part of the string
    str = str & IIf(hours = "0", "", _
    IIf(hours = "1", hours & " Hour", hours & " Hours"))
    str = str & IIf(hours = "0", "", _
    IIf(minutes & seconds <> "00", ", ", " "))
    ' Minutes part of the string
    str = str & IIf(minutes = "0", "", _
    IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
    str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
    ' Seconds part of the string
    str = str & IIf(seconds = "0", "", _
    IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
    ElapsedTimeString = IIf(str = "", "0", str)
    End Function
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    You can probably use elements of that code to do the other calculation, but you would need to loop through all the records within a Group.
    It may be possible to do this with queries, but break the data up in to separate fields ie days and time. You would then total the Time and the days, then on the Report add the days created by the time to the days from the query.
    If you could provide with some Dummy data in a zipped Access 2003 format database I can take a look for you.
     
  5. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    (DB Attached)
    Hello, i've been doing more working out...and ive managed to get it to sum up the time, but...it displays it as hours and minutes.
    However...this is where it gets slightly more complicated...The report which i have left in the DB i need to total the time, but with the days equal to only 10 hours/8am to 6pm. This is because when my boss wants to produce a report on how long a server has been off line for, he only wants to calculate it for the working day 8-6.

    So, on a report for QrySystemDowntime i need a total time counting days as 8am-6pm,

    and a report for QryClientSupport to total the time, but i want it to be displaying as it does in the TotalDataTime field.

    I was doing some google searching for VBA to make the Day equal to so many hours, but i dont understand how to make it work for me, and how to use it...or if i can use part of it for what im trying to do:

    Sub Add_Hours_Version1()

    Const HOURS_PER_DAY As Long = 8
    Dim StartDate As Date
    Dim NumDays As Long
    Dim HoursToAdd As Long
    Dim HolidaysList() As Variant
    Dim holiday As Variant
    Dim bWasFound As Boolean

    ' edit/add/delete as needed
    ' these are US Federal Holidays
    ' taken from http://www.opm.gov/Operating_Status_Schedules/fedhol/2009.asp
    HolidaysList = Array("1/1/2009", "1/19/2009", "2/16/2009", _
    "5/25/2009", "7/3/2009", "9/7/2009", _
    "10/12/2009", "11/11/2009", "11/26/2009", "12/25/2009")

    StartDate = InputBox("Enter start date")
    HoursToAdd = InputBox ("Enter number of hours")

    ' round up number of days
    NumDays = WorksheetFunction.Ceiling(HoursToAdd / HOURS_PER_DAY, 1)

    ' increase date
    StartDate = StartDate + NumDays

    ' check if it's a holiday
    For Each holiday In HolidaysList
    If holiday = StartDate Then
    bWasFound = True
    Exit For
    End If
    Next holiday

    If bWasFound Then
    StartDate = StartDate + 1
    End If

    ' check if it's a weekend, if so then move it to Monday
    If Weekday(StartDate, vbMonday) = 6 Then
    StartDate = StartDate + 2
    ElseIf Weekday(StartDate, vbMonday) = 7 Then
    StartDate = StartDate + 1
    End If
    End Sub
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    You say "The report which i have left in the DB i need to total the time, but with the days equal to only 10 hours/8am to 6pm"
    So does that mean that you first have to count only the times between 8am & 6pm?
    ie if the 2 date/times are Open-11/06/2011 10:30:00 Close-12/06/2011 23:25:00 is 1 day 7.5 hours
     
  7. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    Yes that is correct, (I guess i gave a bad example for times there - as it we would not be in work at 2300 hours)

    So Open-11/06/2011 10:30:00 Close-12/06/2011 13:30:00 = 1 Day(=10Hours) and 3 Hours

    Equaling 13 Hours Total Work time elapsed of downtime.

    I think i worked that to be correct! :eek:
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    Are all Close times in the working day 8 to 6.
     
  9. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    Yes, Excluding Sat & Sun

    So Monday to Friday 8am to 6pm.
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    OK, here is how I would do it using just a query with calculating fields which can then be "Summed" on a report or in another query.

    I will need to think about the weekend part.
     

    Attached Files:

  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    OK, this version adjusts the days for the weekend.
     

    Attached Files:

  12. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    Wow...i can see where your going with this, was does Int mean? Ive had a quick gander because my head is still in DB mode from work.

    Im going to have to sit down and go through it understanding it, but im not back into work untill monday, so ill pick it up then...thankyou for your time on this...it is very much appreciated, and im sure anybody else who is tracking this will be just as grateful, as there is useful info in here for people. :)
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    Int means the Integer (whole number) part of the number. So int gives you the whole number and original value mins Int(value) gives you the remainder. I think that there is also a function for that as well, but I can't remember what it is.

    ps Nope, can't find a remainder function.
     
  14. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    The process works okay when you have a day in the workings, but if you have no day, then it doesnot seem to addup the hours and mins properly, ive added a few more dummy entries to show. What does +0.5 mean?
     

    Attached Files:

  15. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    Actually it does not seem to have any difference with the days in. The Minute goes to a Minus, to indicate to take away from the hour field
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1029200

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice