Calculating Time in VBA

[tpsmith]

Hi

I am using Access to create a Timesheet system and using VBA to perform the calcuations, on a daily basis I have managed to calculate the times and managed to get them to display in time format (eg 07:30) for 7 hours 30 mins instead of decimal format (eg 7.50), with a bit of help from some of the threads here.

What I am now having problems with is adding up the 5 daily values to give me a total of hours (eg 37:30) not 37.5. I would really appreciate any help.

The daily value is not stored in the table of data it is just calculated after each change of the time in / out fields.

I have 5 boxes on the screen which have the daily hours displayed in there and I need a total for the week displayed below. I also need an accumulated total for the staff member as well but once I have the weekly one sorted the other should be pretty straight forward.

I hope there is someone that can help.

Thanks
Tony

 

[Crowley16]

split the hours and mins for each time value, add the 2 seperately.

then use integer division by 60 on the mins part and add the 2 results together.

tip:
encapsulate the whole thing in a function, maybe even use a paramarray as input...

--------------------
Procrastinate Now!

 

[tpsmith]

Hi Crowley16

Thanks for the post. While I was just replying to you I had a flash in inspiration and I have just written this bit of code and it seems to do exactly what I require. Hopefully this may help others as well. I haven't tested this fully but it seems to be ok.

Instead of using the /60 routine on the minutes value which will return a decimal value, I have summed the minutes and looped through and for every 60 minutes I have added 1 hour to the hours field and taken 60 mins off the minutes.

Function CalcTime(t1, t2, t3, t4, t5)
Dim TmpMins As Double
Dim TmpHrs As Double

TmpHrs = Val(Mid(t1, 1, InStr(t1, ":") - 1)) + _
Val(Mid(t2, 1, InStr(t2, ":") - 1)) + _
Val(Mid(t3, 1, InStr(t3, ":") - 1)) + _
Val(Mid(t4, 1, InStr(t4, ":") - 1)) + _
Val(Mid(t5, 1, InStr(t5, ":") - 1))
TmpMins = Val(Mid(t1, InStr(t1, ":") + 1, 2)) + _
Val(Mid(t2, InStr(t2, ":") + 1, 2)) + _
Val(Mid(t3, InStr(t3, ":") + 1, 2)) + _
Val(Mid(t4, InStr(t4, ":") + 1, 2)) + _
Val(Mid(t5, InStr(t5, ":") + 1, 2))

While TmpMins >= 60
TmpMins = TmpMins - 60
TmpHrs = TmpHrs + 1
Wend

CalcTime = Format(TmpHrs, "00") & ":" & Format(TmpMins, "00")

'Use this to test the function
'calctime(#07:25#,#07:25#,#07:25#,#07:25#,#07:25#)
End Function

 

[Hans8823]

Function CalcTime(t1, t2, t3, t4, t5)
CalcTime = (t1 + t2 + t3 + t4 + t5) * 24
End Function

 

[tpsmith]

Thanks Hans8823 but again this version returns a decimal value of 37.08333333 instead of 37:05 which is whats required. I need the user to see how many hours and minutes they have worked without them having to work out what .083333 is in minutes.

Thanks anyway.
Tony

 

[MajP]

It sounds like you start with decimals. If that is the case why do you not add the decimals and then convert to your text format? Seems a lot easier. From your post it looks like you start with decimals, convert to text, then sum.

Public Function TimeFromDecimal(theTime As Double) As String
  Dim snglHours As Single
  Dim snglMin As Single
  snglHours = Fix(theTime)
  snglMin = Round((theTime - snglHours) * 60)
  'To handle the case of a decimal time like 37.999
  If snglMin = 60 Then
    snglMin = 0
    snglHours = snglHours + 1
  End If
  TimeFromDecimal = CStr(snglHours) & ":" & Format(CStr(snglMin), "00")
End Function

values
?TimeFromDecimal(37.999)
38:00
?timefromdecimal(37.5)
37:30
?timefromdecimal(144.1)
144:06
?timefromdecimal(37.009)
37:01

if you want to pass in text (i.e 3:00, 3:15, 7:05) and sum up, here is a way to pass in multiple values.

Public Function CalcTimeFromText(ParamArray theTime() As Variant) As String
  Dim intCount As Integer
  Dim snglHours As Single
  Dim snglMin As Single
  Dim tmpTime
  Dim colonLocation As Integer
  For intCount = 0 To UBound(theTime)
     tmpTime = theTime(intCount)
     colonLocation = InStr(tmpTime, ":")
     snglHours = snglHours + Left(tmpTime, colonLocation - 1)
     snglMin = snglMin + Mid(tmpTime, colonLocation + 1)
     Debug.Print snglMin
  Next intCount
  snglHours = snglHours + snglMin \ 60
  snglMin = snglMin Mod 60
  CalcTimeFromText = CStr(snglHours) & ":" & Format(CStr(snglMin), "00")
  End Function

Good exercise in the Mod, Round, Fix, and integer division (\) functions.

 

[Hans8823]

Tony,
Sorry, I didn't read it correctly, next try then:

Function CalcTime(t1, t2, t3, t4, t5)
total = t1 + t2 + t3 + t4 + t5
CalcTime = Int(Total * 24) & Format(total, ":nn")
End Function

 

[SkipVought]

Hi guys,

The key fact is that TIME is part of DATE and is in units of DAYS.

Hrs, Min, Sec are simply a FORMAT text conversion.

So you have several time duration values? Just add 'e up

Function CalcTime(t1, t2, t3, t4, t5) As String

    tt = t1 + t2 + t3 + t4 + t5
    
    CalcTime Format(Int(tt) * 24 + Hour(tt), "00") & ":" & Format(tt, "nn:ss")
End Function

Skip,
[sub]
[red][/red]
[/sub]

 

[tpsmith]

Thank you all for your valuable posts, they have been most useful.