Date and Time Formula

[hairycat]

My data set has Name, Assignment, Start Date, Start Time, End Date and End Time as well as a field that has the calculated hours worked. Under the Assignment field there are 5 categories. I need a formula that will give me: If assignment #1 AND assignment #2 were covered each day during the same 4 hour period. One more piece of information -- there is no set start time and end time so I would need to find a 4 hour overlap.

For example:
[TABLE="width: 497"]
<tbody>[TR]
[TD]Lname[/TD]
[TD]Fname[/TD]
[TD]Assignment[/TD]
[TD]StartDate[/TD]
[TD]StartTime[/TD]
[TD]EndDate[/TD]
[TD]EndTime[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD]Cathy[/TD]
[TD]Assignment2[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]23:55[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]Aaron[/TD]
[TD]Assignment1[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]19:30[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]23:55[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]April[/TD]
[TD]Assignment1[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]1/3/2014[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Dunn[/TD]
[TD]Steve[/TD]
[TD]Assignment4[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]1/3/2014[/TD]
[TD="align: right"]1:30[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]John[/TD]
[TD]Assignment1[/TD]
[TD="align: right"]1/4/2014[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]1/5/2014[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Mann[/TD]
[TD]George[/TD]
[TD]Assignment3[/TD]
[TD="align: right"]1/4/2014[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]1/5/2014[/TD]
[TD="align: right"]7:30[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Doer[/TD]
[TD]Jane[/TD]
[TD]Assignment2[/TD]
[TD="align: right"]1/4/2014[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]1/5/2014[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Dunn[/TD]
[TD]Steve[/TD]
[TD]Assignment4[/TD]
[TD="align: right"]1/4/2014[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]1/5/2014[/TD]
[TD="align: right"]7:30[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD]Cathy[/TD]
[TD]Assignment3[/TD]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]17:30[/TD]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]22:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Jim[/TD]
[TD]Assignment4[/TD]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Taylor[/TD]
[TD]Mike[/TD]
[TD]Assignment2[/TD]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]John[/TD]
[TD]Assignment1[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]17:30[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]23:30[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD]Cathy[/TD]
[TD]Assignment2[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]17:30[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mann[/TD]
[TD]George[/TD]
[TD]Assignment3[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]17:30[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]23:30[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]April[/TD]
[TD]Assignment4[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]16:30[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]23:30[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[MarcelBeug]

Probably some helper columns will be required.
And some additional information:
1. What will be the maximum number of each assignment on 1 day? In the example data I see 2x Assigment1 on 1 day, otherwise only single entries.
2. In case of multiple entries, do you need 4 hours overlap from 1 set of assigments 1 and 2 or can it be split over e.g. 1x3 hours and 1x1 hour?
3. Do you need the result for the dates in column "StartDate" or also the dates in column "EndDate" or for all dates or only working days?
4. What will be the maximum number of hours between start and end? E.g. will the enddate always be the startdate or the next day?
5. Do you need the overlap on each date separate? E.g. entries on 1/4 include >=4 hours overlap both on 1/4 and 1/5.

 

[Peter_SSs]

For the sample data you provided, what are the required results and why?

 

[hairycat]

Probably some helper columns will be required.
And some additional information:
1. What will be the maximum number of each assignment on 1 day? In the example data I see 2x Assigment1 on 1 day, otherwise only single entries.
2. In case of multiple entries, do you need 4 hours overlap from 1 set of assigments 1 and 2 or can it be split over e.g. 1x3 hours and 1x1 hour?
3. Do you need the result for the dates in column "StartDate" or also the dates in column "EndDate" or for all dates or only working days?
4. What will be the maximum number of hours between start and end? E.g. will the enddate always be the startdate or the next day?
5. Do you need the overlap on each date separate? E.g. entries on 1/4 include >=4 hours overlap both on 1/4 and 1/5.

1. What will be the maximum number of each assignment on 1 day? In the example data I see 2x Assigment1 on 1 day, otherwise only single entries.

There is no maximum for each day. There may be days when there is someone that is doing only Assignment1 during their shift and there may be days that there isn't anyone on Assignment1 or Assignment2 and there may be days that there is someone only on Assignment2.

2. In case of multiple entries, do you need 4 hours overlap from 1 set of assigments 1 and 2 or can it be split over e.g. 1x3 hours and 1x1 hour?

In the case of multiple entries, I need a 4 hour block of time that is covered by both assignment 1 AND 2. No matter if person1 only did 3 hours and handed the assignment over to person2.


3. Do you need the result for the dates in column "StartDate" or also the dates in column "EndDate" or for all dates or only working days?
Each record represents when the person was on duty and performing the Assignment. The shift may be overnight, so if there is a 4 hour overlap overnight, that counts for the Start Date.

4. What will be the maximum number of hours between start and end? E.g. will the enddate always be the startdate or the next day?
They can work a 24 hour or even be on standby over a 48 or 72 hour period. This is why it can get complicated.

5. Do you need the overlap on each date separate? E.g. entries on 1/4 include >=4 hours overlap both on 1/4 and 1/5.
Yes, we need an overlap for each day to show that the unit (Assignment1 and Assignment2) was fully staffed.

 

[hairycat]

For the sample data you provided, what are the required results and why?

Hi there!

This is to show if an ambulance was fully staffed with both a driver (ASSIGNMENT1) and a charge EMT (ASSIGNMENT2). We need to know how many days during the month that they met their 4 hour standby requirement.

 

[Peter_SSs]

Hi there!

This is to show if an ambulance was fully staffed with both a driver (ASSIGNMENT1) and a charge EMT (ASSIGNMENT2). We need to know how many days during the month that they met their 4 hour standby requirement.

That gives me a general description (that I don't understand) rather than the specific results for your sample data (that might help me understand).

 

[hairycat]

That gives me a general description (that I don't understand) rather than the specific results for your sample data (that might help me understand).

Do my answers to "MarcelBeug" help you?

 

[MarcelBeug]

It's a bit more complicated than the title suggests....
Anyhow I have a solution as outlined below. There may be better solutions.

Shortly summarized:
On a separate sheet I created a matrix with dates and times covering a full month in blocks of 15 minutes.
Matrix cells are counters: any cell where both assignments 1 and 2 are covered by the associated date and time, will get 1 plus the value of the preceding block of 15 minutes.
If the block of 15 minutes is not covered by both assigments 1 and 2, the counter is reset to 0.
So if the maximum value on a particular date is >=16, the standby requirement of a 4 hour block is met.

Details of the solution (based on your example data):
I added 2 columns start date/time (J2=D2+E2) and end date time (K2=F2+G2) to your sheet (assuming this is 'Sheet1').

I added Sheet2 with the following data:
A2:A32 dates from Jan 1, 2014 thru Jan 31, 2014
D1:CU1 times from 0:00 thru 23:45 with 0:15 step increments
B2 (copied down thru B32)=MAX(D2:CU2)>=16
So TRUEs and FALSEs in column B indicate whether requirement is met or not.
Column C left empty
D2 (and copied to the right and down thru CU32):

=(C2+1)*--AND(0 < SUMPRODUCT((Sheet1!$C$2:$C$16="Assignment1")*(Sheet1!$J$2:$J$16 <= ($A2+D$1))*(Sheet1!$K$2:$K$16 > ($A2+D$1))),0 < SUMPRODUCT((Sheet1!$C$2:$C$16="Assignment2")*(Sheet1!$J$2:$J$16 <= ($A2+D$1))*(Sheet1!$K$2:$K$16 > ($A2+D$1))))

Maybe this needs some further tweaking, or someone might come up with a better solution, but at least you have a start.

Remarks:
Your answers to 3 and 5 are contradictory: #3 says it counts only for 4/1, #5 says it counts for both 4/1 and 5/1.
Are you sure about the block of 4 hours? So 3 hours in the morning and another 3 hours in the evening mean: requirement not met (as in my solution).

 

[hairycat]

Thank you very much! I'll try this and see if I have any other questions. To answer your question -- Yes it has to be a block of 4 hours -- it can't be 3 hours in the morning and then 3 hours in the evening.