I am trying to check to see if the following Times in the picture fall within a certain core hour of 0930 - 1430, in military time. This breaks because you aren't clocked in at 930 therefore missed a half an hour of core time.
Currently I am using min/max to check for those columns which works until this example. Any ideas would be great!
Here is an example:
And the current code I am using
=IF(AND(COUNTA(J8:J12)<>0,COUNTA(K8:K12)<>0),IF(AND($AQ$15>=MIN(J8:J12),$AR$15<=MAX(K8:K12)),"","CR Missed = "),"")
Edit: Sums are not relevant (it just adds the times) AQ=0930 and AR=1430 (The range of "Core Hours")
Essentially you can enter in different times in each row and I am trying to make sure that you are 'logged in' between required times of 0930 and 1430. Currently the Min/Max equation works ish but doesnt cover the shown example where I am actually missing 30 mins (logged out at 0900 and logged back in at 1000).
I feel like I am missing some logic or command. Sorry, I hope this helps.
CodePudding user response：
Preface
My approach was to look for the first time-slot that covered the starting time (9:30).
If we find a time-slot like (7:00 - 10:00), we need to check if the next time slot starts at 10:00.
If the next time slot covers 10:00, we will check this time slot's end time with the next one.
And we repeat this process until all time-slots have been checked. If the time we end up with is greater than your "core end time" (14:30), then we know that all hours of the core hours have been covered continuously without breaks (might even be more).
On the contrary, if it is smaller, the value we ended up with, is the last point in time that they worked continuously without any gaps.
Formulas & Explanations
Insert start time into D1, and end time in E1.
In C2 and downwards is:
=IF(AND($B2>$D1;$D1>=A2);$B2;$D1)This is what checks if the time we were looking for was in this interval, if it was, this value will change into the end time of the time slot. If not, it will remain as the previous - this is what lets us know not all core hours have been covered.
D2 and downwards is simply:
=MAX(D1;C2)(This could be included in C1, but for readability and to easier understand, I made it a separate column.Finally
E4, whether or not all core hours have been covered is:=C4>=E1, which becomesTRUEorFALSE. If you want the "CR Missed", that will be a simple:=IF(E4;"";"CR Missed")
I realize this isn't very dynamic, so if you have more time slots, you will have to copy the formula down further.
So, maybe this won't be the ultimate solution, but rather do the job and perhaps be enough for you to expand upon.
Best regards! ^_^
CodePudding user response：
It is not clear to me what you want for output. Perhaps this will get you started.
To compute the hours from Start=> End that fall within a certain range (eg: Core Time):
=MAX(MIN(TIME(14,30,0),End)-MAX(TIME(9,30,0),Start),0)
So you could do something like:
