Drivers Hours... Diary, Big Timer or Excel spreadsheet?

UK PROFESSIONAL DRIVERS FORUMS THE UK PROFESSIONAL DRIVERS FORUM (INTERACTIVE)
21

Santa:

205:
i spent weeks building one, most of it was looking for formulas like this

=IF(E3>F3,F3+1-E3,F3-E3) formula for total hours cell

E3 is start time
F3 is finish time
G3 is your total hours

My formula looked more like this - =IF($A1=“”,“”,(MOD(A2-A1,1))*24) if you started working 4:00 PM and ended 1:00 AM without using MOD, it will give you -15 but Mod will give you exactly hours of work which is 9 hours.

It was still a bore to enter all the data, including dates for every day/time.

Santa, that’s evidently a much more complex and non-intuitive formula than 205’s formula.

It works because Excel stores times as (what I’ll refer to as…) “decimal days”.

So that a start time of 12pm is stored as 0.5 (which actually means “30th December 1899, 12pm”, but the date part is hidden). If the finish time is 3am (“30th December 1899, 3am”), that has a decimal value of 0.125. So the difference between the end time and start time is -0.375 (i.e. it ended before it finished).

I assume what the Excel MOD function effectively does in this particular case - where a minus number is divided by +1 - is return the difference between -1 and the dividend, so 0.625 (i.e. (-1) - (-0.375)). With a positive dividend (i.e. where the finish time occurred later on the 24-hr clock than the start time), I assume the Excel MOD function simply returns the dividend. The figure 0.625 multiplied by 24 hours is 15 hours.

However, the most straightforward solution, which requires no formulas, is to be more specific about the date on which the time occurred, and store a date with both the start and end times - so input 2017-08-02 1200 as the start time, and 2017-08-03 0300 as the finish time, and then simply subtracting the start time from the end time is always correct without needing an IF formula.

22

ROG:

tachograph:
As far as I’m aware the driver is as responsible as the company for making sure he/she complies with the RT(WT)R.

Reading the rules they do not expect drivers to have a PC programme to keep track of the average 48 and the Tacho is not designed to keep track of the weekly max which is why it is the responsibility of the employer to do that in the admin dept

The latest Stonebridge tacho apparently keeps track of both tach hours and wtd hours, which is fitted in my new truck, I will see if it’s more accurate than the one in the 14 plate I had.
I use the stoneridge to keep track of my tacho hours, gives you day week and fortnight driving times, tells you when you need a 45, tells you when you need to be parked up for a daily rest, tells you whether you can take a 9 or 11, will probably tell you when you will need a weekly rest and the minimum you need to take off if you max your hours, I don’t so not sure.