Results 1 to 11 of 11

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

Threaded View

decubal Calculating the time elapsed... 03-20-2014, 03:37 AM
Kaper Re: Calculating the time... 03-20-2014, 06:49 PM
kvsrinivasamurthy Re: Calculating the time... 03-21-2014, 05:47 AM
Kaper Re: Calculating the time... 03-21-2014, 07:23 AM
decubal Re: Calculating the time... 03-25-2014, 02:59 AM
decubal Re: Calculating the time... 03-25-2014, 07:38 AM
Kaper Re: Calculating the time... 03-27-2014, 06:02 AM
decubal Re: Calculating the time... 03-28-2014, 06:25 AM
Kaper Re: Calculating the time... 03-29-2014, 03:41 AM
decubal Re: Calculating the time... 03-31-2014, 01:08 PM
Kaper Re: Calculating the time... 03-31-2014, 01:18 PM
  1. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: Calculating the time elapsed between two times when there is lunch and two breaks incl

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel 2007 : Calculating all lunch breaks in Timesheet
    By italiansun in forum Excel General
    Replies: 0
    Last Post: 11-02-2011, 05:37 PM
  2. Subtracting lunch breaks from different shift times
    By badger in forum Excel General
    Replies: 6
    Last Post: 08-31-2011, 12:45 PM
  3. Calculating time, potential lunch break
    By B1123 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-13-2008, 09:18 AM
  4. Replies: 2
    Last Post: 08-07-2008, 05:22 AM
  5. calculate elapsed time between dates and times
    By Jenna in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2006, 12:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1