Time calculations

[cm_gmail]

Greetings:

I need a bit of help. I think I may be making my life much more
complicated than I need. I have this data:


Code:
--------------------

Date TimeIn1 TimeOut1 TimeIn2 TimeOut2
01/01/2006 06:00 11:00 15:00 19:00
01/02/2006 20:00 02:00
01/03/2006 23:00 09:00
01/04/2006 06:00 19:00

--------------------

What I need to do is develop two functions. The first I have but it
seems more complex than it needs to be. It is to get the number of
hours worked. It got uglier than it should be in the "wraparound"
times such as is shown in 01/03/2006.

The second function is the one that's causing me fits. I need to get
the number of hours outside of the window 08:00 - 17:00. In
01/01/2006, I have a total of 9 hours. The hours 06:00-08:00 and
17:00-19:00 are outside of this window, so I would return 4.0 hours.
Again, the complexity that is just irritating me is the wraparound
hours.

It should not be as difficult as I am making it. I seem to have
started down a path and am stuck in the rut now. I can't break free to
get the obvious answer that I need.

Any help will be greatly appreciated!

Thanks!

 

[Bob Phillips]

Within hours

=MOD(C2-B2, 1)+MOD(E2-D2,1)

Out of hours

=MAX(0,TIME(8,0,0)-B2)+MAX(0,C2-TIME(17,0,0))+(D2<>"")*(MAX(0,TIME(8,0,0)-D2
))+(E2<>"")*(MAX(0,E2-TIME(17,0,0)))+(B2>C2)*(MAX(0,1-MAX(B2,TIME(17,0,0)))+
MAX(0,MIN(C2,TIME(8,0,0))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[jlp1782]

I do not understand your second part with the "outside" comment, but I
think I have an idea of how I would attack the wrap around problem....

ok,
If the first number (23:00) is greater than the second hour (9:00),
take 9hrs + (24-23 hrs) = 10 hrs

If TimeIn > TimeOut Then
x = 24 - TimeIn
y = x + TimeOut

Hope this helps...if not, show me how it doesn't, we will figure it out

 

[cm_gmail]

Some very good info. Bob's formula seems to work perfectly.

The "outside" hours calculation is to compensate employees who work
hours other than 8:00am to 5:00pm. If an employee works noon to
8:00pm, they would get paid extra for the 3 hours between 5:00pm and
8:00pm.

Now, I'm working on converting Bob's formula to an Excel function so I
can do some additional work with the results and simplify my life a
bit. Since I'll have this formula many times, if I put it into a
function, I can use this function in my worksheet directly. It will
make updating it easier if I need to change my window of normal hours
to be 7:00 until 5:00, for example. I would only need to update it in
one place.

Thanks for the formula, Bob! I'll post the results when I get it
converted.

I was right, I was making it much harder than it needs to be. The
answer makes perfect sense now. Big relief.

Thanks

 

[cm_gmail]

OK, I would LOVE some help converting this to an Excel function as
opposed to a formula.

The issue I'm having is keeping the logic as simple in the function as
possible to reduce the possibility for error.

A major driving force to do this is the "exceptions" I have to build
in. Weekends are paid at the extra rate without the 08:00-17:00 window
as is holidays, etc. It's easier to build these exceptions into VBA
than in a formula.

The function I have defined before has this interface:
Function shiftBonus(ShiftDate As Date, Optional in1 = 0, Optional out1
= 0, Optional in2 = 0, Optional out2 = 0) As Single

I call it:
=shiftBonus(A5, B5, C5, D5, E5) to get the date (A5) and the 4 times
(B-E5).

Again, Thanks for the help. This formula taught me some good tricks
that I had only applied to sumif and to doing sumif on multiple
criteria. Namely, multiply by 0 (false) or 1 (true) to get the
optional data included.

 

[Bob Phillips]

Basic functions


Function InHours(Start1, End1, Start2, End2)
Dim tmp
tmp = IIf(Start1 > End1, 1 + End1 - Start1, End1 - Start1)
tmp = tmp + IIf(Start2 > End2, 1 + End2 - Start2, End2 - Start2)
InHours = tmp
End Function

Function OutOfHours(Start1, End1, Start2, End2)
Dim tmp

On Error Resume Next
tmp = Application.Max(0, TimeSerial(8, 0, 0) - Start1)
tmp = tmp + Application.Max(0, End1 - TimeSerial(17, 0, 0))
If Start2.Value <> "" Then
tmp = tmp + Application.Max(0, Time(8, 0, 0) - Start2)
tmp = tmp + Application.Max(0, End2 - TimeSerial(17, 0, 0))
End If
If Start1.Value > End1 Then
tmp = tmp + Application.Max(0, 1 - _
Application.Max(Start1, TimeSerial(17, 0, 0))) + _
Application.Max(0, _
Application.Min(End1, TimeSerial(8, 0, 0)))
End If
OutOfHours = tmp
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[cm_gmail]

That works perfectly! Thanks very much!