Total Hours in a day

[jpxme]

Hello, All..

Hope someone can assist with this.

Please see reference below:

Total Hours.xlsx

drive.google.com

The rules are as follows:

1. The TotalHours must be within scheduled time-in and time-out.
2. All hours before Time-in, and all hours beyond time out are disregarded, and not included in TotalHours.
3. The TotalHours must disregard overlapping segments. This avoids double counting.

I have tried the following:

Get the max and min, of a date.

Variables
Z = Total Hours
X = Max time to use
Y = Min time to use

X = If max time is > then sched out then use sched out, else use max time.
Y = If min time is < then sched in then use sched in, else use min time.
Z = X - Y

This solution could work if only I can determine the gap. Been trying for to figure it out for couple hours now.

Appreciate any help.

Thank you so much.

 

[yky]

Why does A001 have three overlapping entries on Feb. 26th? How do they get into the system?

 

[jasonb75]

Cross post found at Total Hours within Range

 

[jpxme]

Hi, Jason.

A001 is a unique code for a location.

The overlapping entries could be due to Ins and Outs (or how long they were at the location) of multiple people.

 

[yky]

Shouldn't the total hours for A001 on Feb. 26th be 10 hours instead of 9? It is effectively 8:00 to 18:00.

 

[jasonb75]

Given that it is a training assignment, what guidance have you been given in order to resolve it and what have you tried so far?

Also, which other forums have you asked the same question on?

 

[jpxme]

Hi, Jason.

I see you are also a member of both forums. Probably a big fan of excel as I am.

I had posted in excelforum, and to my dismay my question was either not interesting enough or too difficult to solve. For days I waited for assistance to no avail. I bumped it up to no avail. So I turned to MrExcel for assistance.

I have solved it after a week. Quite simple. All I needed was to sort Actual Start by A-Z, sort by Column A, then create a column to store the gap. Of course there were a couple adjustments to the formula but, it worked.

Thanks to all.

 

[jasonb75]

Many people are members of multiple forums, which is why cross posts rarely go unnoticed. As soon as it is noticed the chance of getting your question answered is reduced significantly, if one cross post that you haven't told us about is found, there is always the possibility of others. Those able to help will be less willing to do so if they feel that they might be wasting their time on a question that may have already been answered elsewhere.