MoPatel


Hi all,

I am facing such a horrible problem, i have tried everything and cannot find a way to solve it and it is now driving me insane. What i am trying to do is to calculate the amount of hours a member of staff has worked for the whole week, this is done through VBA on a userform and i just cannot do it. the main problem i am facing is how to add hour and minutes together to give me a value in format HH:MM see code below

Code Snippet

Dim BreakLength As Date
Dim TotalHours As Date

'Dim for Sunday
Dim TimeAstart As Date
Dim TimeAend As Date
Dim TimeAelapsed As Date
Dim Day1 As Date

'Dim for Monday
Dim TimeBstart As Date
Dim TimeBend As Date
Dim TimeBelapsed As Date
Dim Day2 As Date

'Dim for Tuesday
Dim TimeCstart As Date
Dim TimeCend As Date
Dim TimeCelapsed As Date
Dim Day3 As Date

'Dim for Wednesday
Dim TimeDstart As Date
Dim TimeDend As Date
Dim TimeDelapsed As Date
Dim Day4 As Date

'Dim for Thursday
Dim TimeEstart As Date
Dim TimeEend As Date
Dim TimeEelapsed As Date
Dim Day5 As Date

Dim TimeFstart As Date
Dim TimeFend As Date
Dim TimeFelapsed As Date
Dim Day6 As Date

'Dim for Saturday
Dim TimeGstart As Date
Dim TimeGend As Date
Dim TimeGelapsed As Date
Dim Day7 As Date

BreakLength = "00:30:00"

'------------------------------------------------------

'this section is repeated seven time all together

'once for each day of the week

'label8 for sunday, label9 for monday and so on

'TimeAxxxxx for sunday, TimeBxxxxx for monday and so on

If Label8.Caption = "--:--" Or Label8.Caption = "" Or Label8.Caption = "00:00" Then
Day1 = 0
Else
TimeAstart = ComboBox1.Text
TimeAend = ComboBox2.Text
TimeAelapsed = TimeAend - TimeAstart + IIf(TimeAend > TimeAstart, 0, 10)
If TimeAelapsed >= "07:00" Then
Day1 = TimeAelapsed - BreakLength
Else
Day1 = TimeAelapsed
End If
End If

'-----------------------------------------------------

'Here is where all the problems start, to

TotalTime = Day1 + Day2 + Day3 + Day4 + Day5 + Day6 + Day7
Label15.Caption = Int(CDbl(TotalTime * 24)) & Format(TotalTime, ":nn")

if my shift goes over midnight then the number of hours is in 3 digits e.g if my shift is 21:45 to 06:00 then label15 shows 223:45 instead of 7:45 (note that is should be 8:15 but the break time is deducted) but label8 will show me 7:45

So the main solution i am looking for is how to add time together.

I am now desperate for a solution to this, so if anyone does have a solution please help me

Thanks in advance




Re: Time Addition

sjoo


hello

Walkenbach's a web page would be of help.

plz refer to http://www.j-walk.com/ss/excel/files/timesht.htm