Timesheet problem

I use an Excel 2000 spreadsheet to log sub-contract work hours - and
as the hours are then used to calculate charges I use separate hours
and minutes columns (start hh, start mm; finish hh, finish mm). That
seemed the easiest way to me.

Everything is working just how I want it, but....I now need to
introduce a night charge:

A shift can start anytime day/night and finish anytime day/night. If,
the shift includes ANY time worked between 1 am and 4 am I then need to
(a) invoke an extra charge and (b) do some other checks in a different
spreadsheet.

How do I get Excel to test for this? Thanks for your help.

Bit difficult without knowing what the data looks like, but making some
assumptions

If A1 holds the start time
B1 holds the finish time
The time worked between 02:00 and 04:00 can be calculated with

=IF(A1<TIME(4,0,0),MIN(B1,TIME(4,0,0)),0)-IF(A1<=TIME(4,0,0),MAX(A1,TIME(1,0
,0)),0)

This will not work if the start and end times are in different days.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Oi you" <[email protected]> wrote in message
news:[email protected]...
> I use an Excel 2000 spreadsheet to log sub-contract work hours - and
> as the hours are then used to calculate charges I use separate hours
> and minutes columns (start hh, start mm; finish hh, finish mm). That
> seemed the easiest way to me.
>
> Everything is working just how I want it, but....I now need to
> introduce a night charge:
>
> A shift can start anytime day/night and finish anytime day/night. If,
> the shift includes ANY time worked between 1 am and 4 am I then need to
> (a) invoke an extra charge and (b) do some other checks in a different
> spreadsheet.
>
> How do I get Excel to test for this? Thanks for your help.
>

This function returns day and night work hours even if end time is in the
next date (the maximum difference between end time and start time is 24
hours).

Public Function Shifttime2(starttime As Date, endtime As Date, daytime As
Integer, _
startday As Date, startnight As Date)
Dim length As Variant
Dim timeday As Date, timenight As Date
If starttime >= endtime Then endtime = 1 + endtime
If startday >= startnight Then startnight = 1 + startnight
length = endtime - starttime
timeday = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
endtime - startnight)) _
+ WorksheetFunction.Max(0, endtime - (1 + startday)) _
- WorksheetFunction.Max(0, endtime - (1 + startnight))

timenight = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
startnight - starttime)) _
- WorksheetFunction.Max(0, endtime - (1 + startday)) _
+ WorksheetFunction.Max(0, endtime - (1 + startnight))
Select Case daytime
Case 1
Shifttime2 = timeday
Case 2
Shifttime2 = timenight
Case Else
Shifttime2 = "Invalid daytime!"
End Select
End Function

Regards,
Stefi


"Oi you" wrote:

> I use an Excel 2000 spreadsheet to log sub-contract work hours - and
> as the hours are then used to calculate charges I use separate hours
> and minutes columns (start hh, start mm; finish hh, finish mm). That
> seemed the easiest way to me.
>
> Everything is working just how I want it, but....I now need to
> introduce a night charge:
>
> A shift can start anytime day/night and finish anytime day/night. If,
> the shift includes ANY time worked between 1 am and 4 am I then need to
> (a) invoke an extra charge and (b) do some other checks in a different
> spreadsheet.
>
> How do I get Excel to test for this? Thanks for your help.
>
>

Thanks everyone for your help. The answer I am now using (courtesy of
Mrexcel.com) is very elegant and works in all circumstances I have
tried. Thought you might be interested in it too:

=IF(OR(AND(startHH>=1,startHH<4), AND(finishHH>=1,finishHH<=4),
AND(finishHH>=4,finishHH-totalhours<4)),"Yes","")

This works for finish hours in 24 hour format but it was quite simple
to adjust to suit my spreadsheet. It returns "Yes" if any hours were
worked between 1am and 4 am and I have been able to take it from
there.

Buggered if I know how it works but it does!