This is an improved Function that can handle periods up to 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
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


€žStefi€� ezt Ã*rta:

Hi hello,

Why do you make your task harder then necessary? Use "h:mm" format to
exploit Excel's full computing power! If you do so, the following method
solves your problem:

Place start of day (5:30) and start of night (18:30) in unused cells, e.g.
E2,G2.

A B C D E F
1 start end day night startday startnight
2

In C2: =Shifttime2(A2;B2;1;$E$2;$F$2)
In D2: =Shifttime2(A2;B2;2;$E$2;$F$2)

gives you the day and night hours. Do not forget to format columns A:F like
"h:mm"!

The Shifttime2 function:
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
length = endtime - starttime
timeday = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
endtime - startnight)) _
+ WorksheetFunction.Max(0, endtime - (1 + startday))
timenight = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0,
startnight - starttime)) _
- WorksheetFunction.Max(0, endtime - (1 + startday))
Select Case daytime
Case 1
Shifttime2 = timeday
Case 2
Shifttime2 = timenight
Case Else
Shifttime2 = "Invalid daytime!"
End Select
End Function

It's possible to use cell formulas in columns C and D, but they are rather
lengthy:

In C2:
=MAX(0;(IF(A2B2;1+B2;B2)-A2)-MAX(0;IF(A2B2;1+B2;B2)-$F$2))+MAX(0;IF(A2B2;1+B2;B2)-(1+$E$2))

In D2:
=MAX(0;(IF(A2B2;1+B2;B2)-A2)-MAX(0;$F$2-A2))-MAX(0;IF(A2B2;1+B2;B2)-(1+$E$2))

Choose as you like!

Regards,
Stefi

€žhello€� ezt Ã*rta:

Thank you everyone of your assistance.
I cannot format the cells in to h:mm, as I am working with 24hour time which
looks like 1600 =4pm or 0230=2:30am so it has no ' : '. That's another hurdle
i'm come across. The other issue is that every day has to be broken down into
it's 24hours.
Meaning- if a guard works a shift from 1600-0600, the shift needs to be
broken down into 1600-1830 day rate, 1830-2400 night rate - next day
2400-0530 night rate then 0530-0600 day rate!
You can see my delima.
Please keep up the useful assistance
Regards,
--
thank you


"Sandy Mann" wrote:

Hello Hello,

With the start time in F7 and the finish time in G7 both entered as XL times
ie 04:30 etc. then:

=IF(OR(F7="",G7=""),"",24*MAX(MIN(G7,TIME(18,30,0) )-MAX(F7,TIME(5,30,0)),0))

will give the number of hours betwen 05:30 and 18:30

and shamelessly stealing Harlan Grove's formula:

=IF(OR(F7="",G7=""),"",24*((G7<=F7)*(1-TIME(5,30,0)+TIME(18,30,0))+MIN(TIME(
5,30,0),G7)-MIN(TIME(5,30,0),F7)+MAX(TIME(18,30,0),G7)-MAX(TIME(18,30,0),F7)
))

will give the number of hours outside of those hours. Both hours will
return the hours as a number not a time, (with the cell formated as
General). If you want it to remain as an XL time theb remove the 24* from
both formulas.