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 ;-) ).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

it can be used then in calculation of starttime:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

F3 formula shall read:

Please Login or Register  to view this content.

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).

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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