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

 

[Guy Boot]

Hi. Its night time here. Any ways, thank you.
Could I send you, one way or another, the workbook, were this sheet is inserted?
All is connected to a data base, which I have problems with to connect.
Please let my know.

 

[Snakehips]

@Guy Boot @Artik 's code works a treat.
If however you cannot for some reason use a VBA solution then here is a formula possibility.
It's a bit clunky but appears to work?
The limitation of XL2B will only allow me to post a small example.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1			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
2
3
4
5	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
6	M	14																													X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
7	A	13	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
8	N	15																			X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
9	D	13	X	X	X	X	X	X	X	X	X	X	X	X																											X	X	X	X	X	X	X	X	X	X
Sheet4

Cell Formulas
Range	Formula
C5:AX9	C5	=IF(AND($A5="M",C$1>(2*$B5)),"X",IF(AND($A5="A",C$1<=(48-(2*$B5))),"X",IF(AND($A5="N",C$1>12+2*($B5-12),C$1<=36),"X",IF(AND($A5="D",OR(C$1<=12,C$1>(2*$B5)+12)),"X",""))))

 

[Guy Boot]

@Guy Boot @Artik 's code works a treat.
If however you cannot for some reason use a VBA solution then here is a formula possibility.
It's a bit clunky but appears to work?
The limitation of XL2B will only allow me to post a small example.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1			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
2
3
4
5	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
6	M	14																													X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
7	A	13	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
8	N	15																			X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
9	D	13	X	X	X	X	X	X	X	X	X	X	X	X																											X	X	X	X	X	X	X	X	X	X
Sheet4

Cell Formulas
Range	Formula
C5:AX9	C5	=IF(AND($A5="M",C$1>(2*$B5)),"X",IF(AND($A5="A",C$1<=(48-(2*$B5))),"X",IF(AND($A5="N",C$1>12+2*($B5-12),C$1<=36),"X",IF(AND($A5="D",OR(C$1<=12,C$1>(2*$B5)+12)),"X",""))))

Hi.
Thank you very much. Its working perfect.
@ Artik, IF you have the Macro I am happy to see it. Just leaarning a lot of you both.

 

[Guy Boot]

Hi.
Thank you very much. Its working perfect.

 

[Joe4]

Guy Boot,
Please note: When marking a post as the solution, please mark the original post that has the solution and not your own post indicating that another post is the solution.
I have updated this for you.