Sum Times using expression

[aarondewberry]

All I have an expression i use to sum a time field called 'AnnualLeave'.

Exp: Int(Sum([AnnualLeave]))*24+Hour(Sum([AnnualLeave])) & ":" & Minute(Sum([AnnualLeave]))

What I want to do is add the sum value for AnnualLeave to the sum total of another time field 'SpecialLeave'

Exp: Int(Sum([SpeciallLeave]))*24+Hour(Sum([SpecialLeave])) & ":" & Minute(Sum([SpecialLeave]))

Any ideas?

 

[SkipVought]

Hi,

[AnnualLeave] and [SpeciallLeave] are declared as what kind of data types?

Skip,

_[red][/red]

 

[aarondewberry]

Hi Skip

[AnnualLeave] and [SpecialLeave] data types are Date/Time - Short Time!

 

[OhioSteve]

You are adding several time spans. That means you want the computer to do calculations such is this:

one day, two hours, four minutes
+
five hours
=
one day, seven hours, four minutes

It will be quite challenging to achieve this goal using Access. However, I do have an alternative suggestion.

Ask yourself, what is the smallest unit of time that I want to store? Let's say that the smallest unit is minutes. Access can store MASSIVE integers with no problem. With that in mind, simply store the number of minutes of leave time for each worker. Then it will be no problem to sum the fields using sum...because you will be summing integers.

The big issue with my approach will be output. You will need to create a VBA function that converts the quantities of minutes into phrases that humans can read. Okay, I can hear you groaning. But trust me that will be FAR easier than trying to add timespans.

If you decide to pursue this solution post and I will try to help you this weekend.

 

[aarondewberry]

Hi Steve.

Bearing in mind the smallest unit is Minutes. If i decide to go down the integer route (using a VBA function for output), will team leaders be able to go into an input form and type in agents working hours in short time 00:00? or would they need to work out how many minutes everytime?
If they are able to key in in short time, then i agree it will definitely be better to go down the integer route.

Thanks

 

[OhioSteve]

You should not use a text box for input. The users will not use the same format consistently. For example, lets say a user wants to enter the value "two hours". He may enter "120", "2 hours", or "2".

For input I would use two combo boxes, one for hours and one for minutes. The first combo box should display the integers 0-9. The second combo box should display the integers 0-59.

 

[OhioSteve]

Public Function minutesToTimespan(ByVal minutes As Integer)

'Note that this function has
'no error handling, so it will
'be quite fragile. You should
'add error handling ASAP.

'This function accepts a
'quantity of minutes as
'an integer. If the integer is
'between 1-5999 inclusive, the
'function returns the timespan
'as a string with the format HH:MM.

If (minutes < 1) Or (minutes > 5999) Then
minutesToTimespan = "XX:XX"
Exit Function
End If

'To understand this function
'you must understand the
'arithmetic operation modulus.
'So you may want to google
'modulus before you try
'to decipher this code. The
'modulus operator in vba is "mod".

'a is the number of minutes
'that will appear in the answer.
'for example, if the output
'was 02:06, then a would be 6.
Dim a As Integer
a = 0
a = minutes Mod 60

'b is the number of hours
'that will appear in the answer
'for example, if the output was
'02:06, then b would be 2.
Dim b As Integer
b = 0
b = (minutes - a) / 60

'Now create a string, so
'that the output will look nice.
Dim myAnswer As String
myAnswer = Right("00" & b, 2)
myAnswer = myAnswer & ":"
myAnswer = myAnswer & Right("00" & a, 2)

minutesToTimespan = myAnswer

End Function

 

[aarondewberry]

Thanks for this steve. It looks like i am gonna have to go down the integer route. Any probs with the code and i'll post back.

Cheers mate