Formula request. Text under the sheet.

[Guy Boot]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

M

12

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

M

14

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

A

12

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

A

13

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

N

12

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

N

15

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

D

12

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

D

14

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

The sheet is counting 48 column width and 31 rows down Column A & B is info from other sheet and is not visible. Here it is only shown to help the programmer If Column A is M then the crosses start 25 clicks. The crosses from there are the nummer ( here Column B ) multiplied by 2 For example Row 5 ( M 12 ) there are 12h worked in M ( morning ). Resulting 24 crosses counting from 25 cells. IF "C 5" would be the first cell then the crosses start "C5" +24 cells is "AA5" Figures in column B are the nr of hours worked. All above 12 h should be substracted from the crosses in the sheet. For example Row 19. There are 15 hours worked at night. Is 3 hours les rest resulting 6 crosses les Resuming. This is a rest hour sheet. People are working normally 12h. Overtime is given and those have to be substracted from the resttime. Rest time is marked in the sheet with an X on half hour base. There a 4 shift, counting Morning ( 0:00 to 12:00 ), Afternoon ( 12:00 to 24:00 ), Day ( 06:00 to 18:00 ) And Night ( 18:00 to 06:00 ) Mind there is 1 sheet per person for every month separatly

 

[Artik]

Didn't you make a mistake in row 13? The Afternoon shift starts at 12:00. The employee works 13 hours. He should start at 12:00 and finish at 01:00. And from your schedule , it appears that he starts work 1 hour earlier.

Artik

 

[Guy Boot]

G

Didn't you make a mistake in row 13? The Afternoon shift starts at 12:00. The employee works 13 hours. He should start at 12:00 and finish at 01:00. And from your schedule , it appears that he starts work 1 hour earlier.

Artik

 

[Guy Boot]

G

Good afternoon, the crossed are rest hours. He had 1 h less rest.

 

[Artik]

But your schedule shows that he started work at 11:00 and finished at 24:00, and the shift starts at 12:00. So I guess he should work from 12:00 to 01:00.

Artik

 

[Guy Boot]

Overtime was before the shift and for this sheets always the day itself. He started at 11 untill 24

 

[Guy Boot]

The people working from 0h untill 12 noon do overtime after midday. The people working until 18, do overtime after 18. People working 18 untill 06 do overtime after 6. Only people working from 12 untill 24 do overtime before noon.

 

[Artik]

I get the feeling that I made the code too complicated. But it works.

Sub RestSchedule()
    Dim lRow As Long
    Dim i As Long
    Dim j As Long
    Dim vArr(1 To 48)
    Dim Wh As Long
    Dim BeforeMidnight As Long
    Dim AfterMidnight As Long

    lRow = 32

    For i = 2 To lRow
        If Cells(i, "A") Like "[MADN]" Then
            If Cells(i, "B").Value > 0 Then
                
                Erase vArr
                Wh = Cells(i, "B").Value

                For j = 1 To 48
                    vArr(j) = "X"
                Next j

                Select Case UCase(Cells(i, "A").Value)
                    Case "M"
                        For j = 1 To Wh * 2
                            vArr(j) = Empty
                        Next j
                    Case "A"
                        For j = 25 - IIf(Wh - 12 > 0, (Wh - 12) * 2, 0) To Application.Min(48, Wh * 2 + 24)
                            vArr(j) = Empty
                        Next j
                    Case "D"
                        For j = 13 To 12 + Wh * 2
                            vArr(j) = Empty
                        Next j
                    Case "N"
                        BeforeMidnight = Application.Min(6, Wh) * 2
                        AfterMidnight = Wh * 2 - BeforeMidnight
                        For j = 1 To 48
                            If j > 0 And j <= AfterMidnight Or j >= 37 And j <= 36 + BeforeMidnight Then
                                vArr(j) = Empty
                            End If
                        Next j
                End Select

                Cells(i, "C").Resize(, 48).Value = vArr

            End If
        End If
    Next i

End Sub

Artik

 

[Guy Boot]

@ Artik... Sorry, I am a little bit lost here. Where or how do I Import that in my file?
This is a part of the sheet.

Date
1																																																	0
2																																																	0
3																																																	0
4																																																	0
5																																																	0
6																																																	0
7																																																	0
8																																																	0
9																																																	0
10																																																	0
11																																																	0
12																																																	0
13																																																	0
14																																																	0
15																																																	0
16																																																	0
17																																																	0

 

[Artik]

In Excel, press the keyboard shortcut Left_Alt+F11 to open the VBA editor. Then select Insert/Module from the editor menu. In the code window, paste the macro. You can run the macro by assigning it to a button or from the Macro window (Left_Alt+F8).

Artik