Thanks for your formula suggestions on Wednesday's blog post about promotional pricing. Here's another formula example, and I'm sure you'll have alternate methods for this problem too.
Driver Limits
Step 2b: Enter Time in Minutes. Instead of entering the Start Time and End Time, you can use the Minutes Worked column. Even though it's possible to use both methods simultaneously, that would be confusing. Most likely, the person looking at the time log will think the Minutes Worked column is a calculation based on the Start and End times.
In some countries, there are limits to the hours that truck drivers can work in a string of consecutive days. In this example, the limit is 60 hours, in any period of 7 consecutive days.
The maximum hours is entered in cell C1, and the number of consecutive days is entered in cell F1. If the regulations change, it will be easy to change those settings.
Calculate the Remaining Hours
To help prevent drivers from going over their limits, we'll set up a table where the daily hours are entered.
Kleysen Group Ltd. To calculate the hours between times, when both times are in the same calendar day, you can simply subtract the start time from the end time: = end - start And apply number formatting to display hours. However, when times cross a day boundary (midnight), things can get tricky.
The date and driver name are entered in each row. In column D, a SUMPRODUCT formula calculates how many hours the driver has remaining, in the current 7 day period.
The SUMPRODUCT formula checks all the rows above, where the date within the 7 day range, and the driver name matches the current row.
That amount is subtracted from the maximum hours allowed.
=$C$1-(SUMPRODUCT(–($B$4:$B4>=$B4-F$1-1),–($C$4:$C4=$C4),–($E$4:$E4))-E4)
Calculate the Consecutive Hours
The current hours are typed in column E, and a simple formula in column F calculates the total for a consecutive 7 day period.
=$C$1-D4+E4
Highlight the Violations
With Conditional Formatting, you can highlight any cells where the total consecutive hours exceeds the maximum allowed.
- On the Ribbon's Home tab, click Conditional Formatting
- Click Highlight Cells Rules, and then click Greater Than
- In the Greater Than dialog box, select cell C1 as the limit in the text box.
- Select one of the preset format, or create a custom format to highlight the cells.
View the Results
With the conditional formatting applied, it's easy to see where the trouble is. In this example, Lou has gone over his limit on April 10th.
Download the Driver Limit Sample File
To see the data and the formulas, you can download the Driver Hours Limit sample file. The file is zipped, and is in Excel 2003 format.
There is also a pivot table that totals the drivers' hours per calendar week.
__________
$0.00
Description
All drivers of commerecial motor vehicles are excepted from preparing logs if they can meet the following conditions:
1. The CDL drivers stay within a 100 air mile radius of their work reporting location. Non-CDL drivers stay within 150 air mile radius.
2. The driver returns to his/her work reporting liocation and are released from work. CDL drivers must return within 12 consecutive hours.
3. The drivers must have had a full 10 hour break (8 hours for passenger vehicle drivers) between each 12 hour shift.
4. The driver must be within all of the 395.3 hours limits
5. The driver records his/her hours on a time sheet which shows at least start time, end time, and total hours each day.
Drivers Hours Calculator Excel Spreadsheet
This form meets the requirements for the time sheets described in item number 5 above. Note: This form is available in an excel format upon request for those who would like to customize it to their operation. Contact Jeff Simon.