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.