Solved: Access 2010 Total Time and Date Values

Not open for further replies.

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?

Thank you

Joined:
Mar 8, 2005
Messages:
19,694
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.

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

Joined:
Mar 8, 2005
Messages:
19,694
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.

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:

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

' these are US Federal Holidays

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:

• TotalTime.zip
File size:
38.6 KB
Views:
35

Joined:
Mar 8, 2005
Messages:
19,694
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

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!

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

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

So Monday to Friday 8am to 6pm.

Joined:
Mar 8, 2005
Messages:
19,694
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:

• TotalTime.zip
File size:
27.7 KB
Views:
31

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

Attached Files:

• TotalTime.zip
File size:
28.4 KB
Views:
32

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.

Joined:
Mar 8, 2005
Messages:
19,694
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.

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:

• TotalTime v 0.3.zip
File size:
73.2 KB
Views:
33

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

As Seen On