Calculating the time elapsed between two times when there is lunch and two breaks included

Hi all,

I am creating a worksheet for my production line to calculate the time elapsed between points that the line takes a step forward.

The problem is that I need a formula what calculates time elapsed and takes notice of the lunch and coffee breaks and also the start time and the end time of the work day.

Please find attached the workbook. I think it will describe my problem clearly.

Thanks a lot for any help and answers! Please ask if you need a better description of the problem.

I started working on formula (as the subforum heading suggests, but it quickly started to grow. So gave up and prepared short UDF - a small macro which can be used the same way as build-in functions. The only difference is that macros have to be enabled in excel.

Have a look on attached file and code below. I tried to use self explanatory variable names.

calling function can be seen in the spreadsheet - first argument is relative address of the registered time, and two other are ranges with absolute addresses of work start-end and breaks (you are no longer limited to 3 ;-) ).

Function takt(r_end As Range, r_time As Range, r_breaks As Range) As Variant
Dim i As Integer, t_start As Double, t_end As Double, t_break As Double, breaks
Application.Volatile
If r_end.Cells.Count = 1 And r_time.Cells.Count = 2 And r_breaks.Columns.Count = 2 Then
  t_start = IIf(r_end.Offset(0, -1) <> r_end.Offset(-1, -1), r_time(1), r_end.Offset(-1, 0))
  t_end = WorksheetFunction.Min(r_end, r_time(2))
  breaks = r_breaks.Value
  For i = 1 To UBound(breaks) 'correction of start/end
    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
  takt = (t_end - t_start - t_break) * 24 * 60
Else
  takt = "bad data"
End If
End Function

=IF($A7=$A6,24*60*(MAX(MIN($B7,$K$3)-MAX($B6,$H$5),"0:00")+MAX(MIN($B7,$G$5)-MAX($B6,$H$4),"0:00")+MAX(MIN($B7,$G$4)-MAX($B6,$H$3),"0:00")+MAX(MIN($B7,$G$3)-MAX($B6,$J$3),"0:00")),IF($A7>$A6,24*60*((MAX(MIN($B7,$K$3)-MAX($J$3,$H$5),"0:00")+MAX(MIN($B7,$G$5)-MAX($J$3,$H$4),"0:00")+MAX(MIN($B7,$G$4)-MAX($J$3,$H$3),"0:00")+MAX(MIN($B7,$G$3)-MAX($J$3,$J$3),"0:00"))+(MAX(MIN($K$3,$K$3)-MAX($B6,$H$5),"0:00")+MAX(MIN($K$3,$G$5)-MAX($B6,$H$4),"0:00")+MAX(MIN($K$3,$G$4)-MAX($B6,$H$3),"0:00")+MAX(MIN($K$3,$G$3)-MAX($B6,$J$3),"0:00"))+($A7-$A6-1)*"7:40"),"0:00"))

Wow, that's impressive.
Of course there are repeating parts, but anyway.
One minor comment - it does not take care of (I dont know if it happens in real data) hours after K3 (Work Ends).

Hi,

First what I noticed is that you now have time and date not in adjacent columns but separate.
A minor modification shall be applied to show the UDF where are dates located, for instance passing this by additional argument:

Function takt_date(r_end As Range, r_date As Range, r_time As Range, r_breaks As Range) As Variant

it can be used then in calculation of starttime:

t_start = IIf(r_date <> r_date.Offset(-1, 0), r_time(1), r_end.Offset(-1, 0))

of course, as the name of the function is now different, it assigning result to function name lines have to reflect this:

  takt_date = (t_end - t_start - t_break) * 24 * 60
Else
  takt_date = "bad data"

F3 formula shall read:

=takt_date(E3,B3,$M$3:$N$3,$J$3:$K$5)

and copy down
in D3 you can either leave old version (if in VBA module you fave both functions) or use (if only nev version is in module).

=takt_date(C3,B3,$M$3:$N$3,$J$3:$K$5)

This does not address the issue you have shown in comments - I'm a bit "slow thinker", so write in "big letters" like:
time in F5 shall be 110 because it is the same day (B4 and B5), takes start time from .... and end time from E5(?) and includes all breaks in between.

whole code is:

Function takt_date(r_end As Range, r_date As Range, r_time As Range, r_breaks As Range) As Variant
Dim i As Integer, t_start As Double, t_end As Double, t_break As Double, breaks
Application.Volatile
If r_end.Cells.Count = 1 And r_time.Cells.Count = 2 And r_breaks.Columns.Count = 2 Then
  t_start = IIf(r_date <> r_date.Offset(-1, 0), r_time(1), r_end.Offset(-1, 0)) 'here changed
  t_end = WorksheetFunction.Min(r_end, r_time(2))
  breaks = r_breaks.Value
  For i = 1 To UBound(breaks) 'correction of start/end
    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
  takt_date = (t_end - t_start - t_break) * 24 * 60
Else
  takt_date = "bad data"
End If
End Function

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

Glad to hear that.

As the minutes_skipping_breaks is now rather universal, I'll probably refer to this thread if someone has similar problem.

Just for easier search: several breaks during period of time stops deduct deducted