I have a datatable that contains the following columns, and example data to describe what I'm working with.
| Confirmation Number | Clock In | Clock Out |
|---|---|---|
| 500012 | 08:00 | 16:00 |
| 500013 | 08:00 | 11:00 |
| 500014 | 10:00 | 12:00 |
| 500015 | 15:00 | 17:00 |
In this example I am going to do the calculation on Confirmation Number 500013.
I am using this table to calculate pro-rata hour bookings (i.e. if two clocks overlap, the time they overlap should be shared equally) and the method I have chosen for doing this is to essentially count the number of clocks that overlap between each relevant time event.
I need to find some way of returning the minimum values in both the Clock In and Clock Out columns where those values are later that the "Sweep Time". The process flow should like this:
- Identify that at 08:00 start time, there are two clock ins.
- Find the next value in this table, being the Clock in of 500014 at 10:00. Take the two hours between 8 and 10, divide by the number of confirmations over that duration (2) and pass the 1 hour to a holding variable.
- As the next value was a Clock In, the number of overlapping confirmations is now 3.
- Find the next value in the table, being the Clock Out of 500013 at 11:00. Take that hour and divide by 3, pass to the holding variable, making the total number of hours to book by pro-rata 1.3333333.
I cannot come up with a sensible way of doing step 2 without iterating over the entire list one by one. Is there any built in MINIF function in C# as there is in Excel, where a list of numbers can be sent and the smallest will be returned, but only if it is considered true by the constraint (in this case is newtime > oldtime)?
If iterating over every item in the list is the only way to do it then that's what I'll have to do, but I would prefer to have it neatly packaged up if at all possible.