Truck drivers plan sheet and infringement inform(Excel 2007)

[tseklee]

Hello, i am trying to create an Excel sheet to get different results about infringements and working times. The sheet should theoretically inform the driver if an infringement is being added to the future plans and also if it has actually taken place(for past actions). The sheet formulas and VBA code needed may be massive for my newbie level and it may need occasionally corrections here and there if needed. So the thread will be kept alive until it has reached a stable state.

Here is the EU regulation for reference: https://eur-lex.europa.eu/legal-content/EN/TXT/PDF/?uri=CELEX:02006R0561-20200820&from=HU

Here is the example sheet of how far i have managed to come so far: 46.9 KB file on MEGA

So i jump straight into the questions i'm encountering problems answering.

1)How to calculate the REST period? This shouldn't be only calculated from the time one finishes work till 24:00 but it must add up with the next day ONLY IF the next day is free of service. While if he works the next day the result should be restricted to calculating only that same day.
Example 1: Cell N313 should count 09:30+24:00+24:00+03:15(This is cause the next cells after N313, the N314 and N315 are empty(free of driving), plus the 03:15 from the N316.
Example 2: Cell N316 should be 08:45, cause since the next row contains a working day(J317/K317 is not 0 or empty) the add up should be restricted there.

 

[tseklee]

Here is a sample from the sheet for anyone not wanting to download the file.

Day

Date

Place

Address

Go

Back

Summary(KM)

Start

Finish

Summary(hours)

Summary(hours-decimal)

Pause(workers')

Overnight

Rest

Saturday

Sunday

Holiday

Infringements

=(E1+F1)

=I1-H1

=IF(ROUND((I1-H1)*24,2)<0,ROUND((I1+1-H1)*24,2),ROUND((I1-H1)*24,2))

=IF(J1>=0.416666666666667,0.0416666666666667, IF(J1>=0.25,0.0208333333333333,""))

=IF(K1>0,IF(OR((WEEKDAY(B1)=7)),K1,""),"")

=IF(K1>0,IF(OR((WEEKDAY(B1)=1)),K1,""),"")

=IF(K1>0,IF(OR(B1=Holiday!A1, B1=Holiday!A2,B1=Holiday!A3, B1=Holiday!A4,B1=Holiday!A5, B1=Holiday!A6,B1=Holiday!A7, B2=Holiday!A8,B1=Holiday!A9, B1=Holiday!A10,B1=Holiday!A11),K1,""))

So

01.10.23​

Kenndoch

Umdieecke 16

0​

0​

0​

3:00​

11:00​

8:00​

8​

0:30​

8​

A

02.10.23​

Anofirma

Sonnenblume 64

0​

0​

0​

4:15​

18:15​

14:00​

14​

1:00​

A

03.10.23​

Anofirma

Sonnenblume 64

0​

0​

0​

2:30​

18:45​

16:15​

16.25​

1:00​

A

04.10.23​

Anofirma

Sonnenblume 64

0​

0​

0​

4:45​

16:30​

11:45​

11.75​

1:00​

A

05.10.23​

Anofirma

Sonnenblume 64

0​

0​

0​

4:15​

18:30​

14:15​

14.25​

1:00​

A

06.10.23​

Anofirma

Sonnenblume 64

0​

0​

0​

3:00​

22:30​

19:30​

19.5​

1:00​

Sa

07.10.23​

---

0​

0:00

0