Hi,
I rewrote somewhat the function to make it a bit more universal. So the function now expects just start time, end time and breaks (as a range).
There is also optional argument if end of break is given as time not as length of break (minutes).
Checking if it is the first tast that day or if the end is not after scheduled end of the workday is done with standard excel functions. This allows for quite flexible data layout and possible reause of the function in several other cases.
so it is called now in F3 as:
=IF(E3="","",minutes_skipping_breaks(IF(B3=B2,C2,$M$3),MIN(E3,$N$3),$J$3:$K$5))
if end time is empty result is empty
as starttime we take either time from row above or standard workdaystart IF(B3=B2,C2,$M$3)
as endtime either real endtime or workday end - whichever is earlier MIN(E3,$N$3)
See attached file, and the code of UDF is as follows:
Function minutes_skipping_breaks(s_time As Double, e_time As Double, r_breaks As Range, Optional b_minutes_column As Boolean = True) As Double
' written March 2014 by Kaper for excelforum.com/excel-formulas-and-functions/998062-calculating-the-time-elapsed-between-two-times-when-there-is-lunch-and-two-breaks-included.html
'
' function to calculate time difference in minutes between two excel times (0-1 double): e_time and s_time
' breaks given in 2columns range r_breaks are excluded
' the second column in r_breaks can be given either as length in minutes of each break - default
' or as end time of break (optional argument b_minutes_column = False)
'
Dim i As Integer, t_start As Double, t_end As Double, t_break As Double, breaks As Variant
Application.Volatile
t_start = s_time
t_end = e_time
breaks = r_breaks.Value
For i = 1 To UBound(breaks) 'correction of start/end
If b_minutes_column Then breaks(i, 2) = breaks(i, 2) / (24 * 60) + breaks(i, 1)
If t_start >= breaks(i, 1) And t_start <= breaks(i, 2) Then t_start = breaks(i, 2)
If t_end >= breaks(i, 1) And t_end <= breaks(i, 2) Then t_end = breaks(i, 2)
Next i
For i = 1 To UBound(breaks) 'whole breaks inside
If t_start <= breaks(i, 1) And t_end >= breaks(i, 2) Then t_break = t_break + breaks(i, 2) - breaks(i, 1)
Next i
minutes_skipping_breaks = (t_end - t_start - t_break) * 24 * 60
End Function
Bookmarks