Create annual leaves chart

Panoos64 · Jul 31, 2015

Hi all, i would like to create a VBA code so that to create a chart report for employees annual leaves. At F.1. i present how are the data when i download from payroll system and in F.2. is the expected result by filling the blank cells with grey colour for days annual leave. ( i couldn't fill it on the below spreadsheet F.2.). Note that the rows are change each time when i download them and are more than one thousand. In addition, due to the short space at F.2. i entered the half month of the chart. I would be greatly appreciate any support and help for my project. Thanking you in advance.

F.1

	A	B	C
1	Employee Name	Start Date	End Date
2	AAAAAA AAAAAA	1/8/2015	3/8/2015
3		10/8/2015	14/8/2015
4		28/8/2015	31/8/2015
5	BBBB BBBBBBB	12/8/2015	18/8/2015
6		25/8/2015	27/8/2015
7	CCCCCC CCCCCCC	3/8/2015	7/8/2015
8	DDDD DDDDD	9/8/2015	14/8/2015

F.2

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

1

Employee Name

Start Date

End Date

1/8

2/8

3/8

4/8

5/8

6/8

7/8

8/8

9/8

10/8

11/8

12/8

13/8

14/8

15/8

16/8

17/8

18/8

19/8

20/8

21/8

22/8

23/8

24/8

25/8

26/8

27/8

28/8

29/8

30/8

31/8

2

AAAAAA AAAAAA

1/8/2015

3/8/2015

3

10/8/2015

14/8/2015

4

28/8/2015

31/8/2015

5

BBBB BBBBBBB

12/8/2015

18/8/2015

6

25/8/2015

27/8/2015

7

CCCCCC CCCCCCC

3/8/2015

7/8/2015

8

DDDD DDDDD

9/8/2015

14/8/2015

Steve=True · Jul 31, 2015

Hi Panoos,

Not sure if you need a VBA for this, but maybe I misunderstood.

I created a formula in cell D2 and copied it down and across the dates.

Then you can create conditional format to fill in the worksheet cell if the value =1

Here is a tutorial on Conditional formatting:
the-tricks-to-writing-a-conditional-formatting-rule-formula

This is the only formula you would need and then copy down and across your date table but I have provided all formulas for all cells farther below:
<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">D$1>=$B2,D$1<=$C2</font>),1,""</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2010

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Employee Name

Start Date

End Date

8/1

8/2

8/3

8/4

8/5

8/6

8/7

8/8

8/9

8/10

8/11

8/12

8/13

8/14

8/15

8/16

8/17

2

AAAAAA AAAAAA

8/1/2015

8/3/2015

1

3

8/10/2015

8/14/2015

1

4

8/28/2015

8/31/2015

5

BBBB BBBBBBB

8/12/2015

8/8/2015

6

8/25/2015

8/27/2015

7

CCCCCC CCCCCCC

8/3/2015

8/7/2015

1

8

DDDD DDDDD

8/9/2015

8/14/2015

1

Sheet1

Cell Formulas
Range	Formula
D2	=IF(AND(D$1>=$B2,D$1<=$C2),1,"")
D3	=IF(AND(D$1>=$B3,D$1<=$C3),1,"")
D4	=IF(AND(D$1>=$B4,D$1<=$C4),1,"")
D5	=IF(AND(D$1>=$B5,D$1<=$C5),1,"")
D6	=IF(AND(D$1>=$B6,D$1<=$C6),1,"")
D7	=IF(AND(D$1>=$B7,D$1<=$C7),1,"")
D8	=IF(AND(D$1>=$B8,D$1<=$C8),1,"")
E2	=IF(AND(E$1>=$B2,E$1<=$C2),1,"")
E3	=IF(AND(E$1>=$B3,E$1<=$C3),1,"")
E4	=IF(AND(E$1>=$B4,E$1<=$C4),1,"")
E5	=IF(AND(E$1>=$B5,E$1<=$C5),1,"")
E6	=IF(AND(E$1>=$B6,E$1<=$C6),1,"")
E7	=IF(AND(E$1>=$B7,E$1<=$C7),1,"")
E8	=IF(AND(E$1>=$B8,E$1<=$C8),1,"")
F2	=IF(AND(F$1>=$B2,F$1<=$C2),1,"")
F3	=IF(AND(F$1>=$B3,F$1<=$C3),1,"")
F4	=IF(AND(F$1>=$B4,F$1<=$C4),1,"")
F5	=IF(AND(F$1>=$B5,F$1<=$C5),1,"")
F6	=IF(AND(F$1>=$B6,F$1<=$C6),1,"")
F7	=IF(AND(F$1>=$B7,F$1<=$C7),1,"")
F8	=IF(AND(F$1>=$B8,F$1<=$C8),1,"")
G2	=IF(AND(G$1>=$B2,G$1<=$C2),1,"")
G3	=IF(AND(G$1>=$B3,G$1<=$C3),1,"")
G4	=IF(AND(G$1>=$B4,G$1<=$C4),1,"")
G5	=IF(AND(G$1>=$B5,G$1<=$C5),1,"")
G6	=IF(AND(G$1>=$B6,G$1<=$C6),1,"")
G7	=IF(AND(G$1>=$B7,G$1<=$C7),1,"")
G8	=IF(AND(G$1>=$B8,G$1<=$C8),1,"")
H2	=IF(AND(H$1>=$B2,H$1<=$C2),1,"")
H3	=IF(AND(H$1>=$B3,H$1<=$C3),1,"")
H4	=IF(AND(H$1>=$B4,H$1<=$C4),1,"")
H5	=IF(AND(H$1>=$B5,H$1<=$C5),1,"")
H6	=IF(AND(H$1>=$B6,H$1<=$C6),1,"")
H7	=IF(AND(H$1>=$B7,H$1<=$C7),1,"")
H8	=IF(AND(H$1>=$B8,H$1<=$C8),1,"")
I2	=IF(AND(I$1>=$B2,I$1<=$C2),1,"")
I3	=IF(AND(I$1>=$B3,I$1<=$C3),1,"")
I4	=IF(AND(I$1>=$B4,I$1<=$C4),1,"")
I5	=IF(AND(I$1>=$B5,I$1<=$C5),1,"")
I6	=IF(AND(I$1>=$B6,I$1<=$C6),1,"")
I7	=IF(AND(I$1>=$B7,I$1<=$C7),1,"")
I8	=IF(AND(I$1>=$B8,I$1<=$C8),1,"")
J2	=IF(AND(J$1>=$B2,J$1<=$C2),1,"")
J3	=IF(AND(J$1>=$B3,J$1<=$C3),1,"")
J4	=IF(AND(J$1>=$B4,J$1<=$C4),1,"")
J5	=IF(AND(J$1>=$B5,J$1<=$C5),1,"")
J6	=IF(AND(J$1>=$B6,J$1<=$C6),1,"")
J7	=IF(AND(J$1>=$B7,J$1<=$C7),1,"")
J8	=IF(AND(J$1>=$B8,J$1<=$C8),1,"")
K2	=IF(AND(K$1>=$B2,K$1<=$C2),1,"")
K3	=IF(AND(K$1>=$B3,K$1<=$C3),1,"")
K4	=IF(AND(K$1>=$B4,K$1<=$C4),1,"")
K5	=IF(AND(K$1>=$B5,K$1<=$C5),1,"")
K6	=IF(AND(K$1>=$B6,K$1<=$C6),1,"")
K7	=IF(AND(K$1>=$B7,K$1<=$C7),1,"")
K8	=IF(AND(K$1>=$B8,K$1<=$C8),1,"")
L2	=IF(AND(L$1>=$B2,L$1<=$C2),1,"")
L3	=IF(AND(L$1>=$B3,L$1<=$C3),1,"")
L4	=IF(AND(L$1>=$B4,L$1<=$C4),1,"")
L5	=IF(AND(L$1>=$B5,L$1<=$C5),1,"")
L6	=IF(AND(L$1>=$B6,L$1<=$C6),1,"")
L7	=IF(AND(L$1>=$B7,L$1<=$C7),1,"")
L8	=IF(AND(L$1>=$B8,L$1<=$C8),1,"")
M2	=IF(AND(M$1>=$B2,M$1<=$C2),1,"")
M3	=IF(AND(M$1>=$B3,M$1<=$C3),1,"")
M4	=IF(AND(M$1>=$B4,M$1<=$C4),1,"")
M5	=IF(AND(M$1>=$B5,M$1<=$C5),1,"")
M6	=IF(AND(M$1>=$B6,M$1<=$C6),1,"")
M7	=IF(AND(M$1>=$B7,M$1<=$C7),1,"")
M8	=IF(AND(M$1>=$B8,M$1<=$C8),1,"")
N2	=IF(AND(N$1>=$B2,N$1<=$C2),1,"")
N3	=IF(AND(N$1>=$B3,N$1<=$C3),1,"")
N4	=IF(AND(N$1>=$B4,N$1<=$C4),1,"")
N5	=IF(AND(N$1>=$B5,N$1<=$C5),1,"")
N6	=IF(AND(N$1>=$B6,N$1<=$C6),1,"")
N7	=IF(AND(N$1>=$B7,N$1<=$C7),1,"")
N8	=IF(AND(N$1>=$B8,N$1<=$C8),1,"")
O2	=IF(AND(O$1>=$B2,O$1<=$C2),1,"")
O3	=IF(AND(O$1>=$B3,O$1<=$C3),1,"")
O4	=IF(AND(O$1>=$B4,O$1<=$C4),1,"")
O5	=IF(AND(O$1>=$B5,O$1<=$C5),1,"")
O6	=IF(AND(O$1>=$B6,O$1<=$C6),1,"")
O7	=IF(AND(O$1>=$B7,O$1<=$C7),1,"")
O8	=IF(AND(O$1>=$B8,O$1<=$C8),1,"")
P2	=IF(AND(P$1>=$B2,P$1<=$C2),1,"")
P3	=IF(AND(P$1>=$B3,P$1<=$C3),1,"")
P4	=IF(AND(P$1>=$B4,P$1<=$C4),1,"")
P5	=IF(AND(P$1>=$B5,P$1<=$C5),1,"")
P6	=IF(AND(P$1>=$B6,P$1<=$C6),1,"")
P7	=IF(AND(P$1>=$B7,P$1<=$C7),1,"")
P8	=IF(AND(P$1>=$B8,P$1<=$C8),1,"")
Q2	=IF(AND(Q$1>=$B2,Q$1<=$C2),1,"")
Q3	=IF(AND(Q$1>=$B3,Q$1<=$C3),1,"")
Q4	=IF(AND(Q$1>=$B4,Q$1<=$C4),1,"")
Q5	=IF(AND(Q$1>=$B5,Q$1<=$C5),1,"")
Q6	=IF(AND(Q$1>=$B6,Q$1<=$C6),1,"")
Q7	=IF(AND(Q$1>=$B7,Q$1<=$C7),1,"")
Q8	=IF(AND(Q$1>=$B8,Q$1<=$C8),1,"")
R2	=IF(AND(R$1>=$B2,R$1<=$C2),1,"")
R3	=IF(AND(R$1>=$B3,R$1<=$C3),1,"")
R4	=IF(AND(R$1>=$B4,R$1<=$C4),1,"")
R5	=IF(AND(R$1>=$B5,R$1<=$C5),1,"")
R6	=IF(AND(R$1>=$B6,R$1<=$C6),1,"")
R7	=IF(AND(R$1>=$B7,R$1<=$C7),1,"")
R8	=IF(AND(R$1>=$B8,R$1<=$C8),1,"")
S2	=IF(AND(S$1>=$B2,S$1<=$C2),1,"")
S3	=IF(AND(S$1>=$B3,S$1<=$C3),1,"")
S4	=IF(AND(S$1>=$B4,S$1<=$C4),1,"")
S5	=IF(AND(S$1>=$B5,S$1<=$C5),1,"")
S6	=IF(AND(S$1>=$B6,S$1<=$C6),1,"")
S7	=IF(AND(S$1>=$B7,S$1<=$C7),1,"")
S8	=IF(AND(S$1>=$B8,S$1<=$C8),1,"")
T2	=IF(AND(T$1>=$B2,T$1<=$C2),1,"")
T3	=IF(AND(T$1>=$B3,T$1<=$C3),1,"")
T4	=IF(AND(T$1>=$B4,T$1<=$C4),1,"")
T5	=IF(AND(T$1>=$B5,T$1<=$C5),1,"")
T6	=IF(AND(T$1>=$B6,T$1<=$C6),1,"")
T7	=IF(AND(T$1>=$B7,T$1<=$C7),1,"")
T8	=IF(AND(T$1>=$B8,T$1<=$C8),1,"")

Steve=True

Panoos64 · Jul 31, 2015

Hi Steve, thank you so much for your support. You done a good job for me but i would like to ask if i have to create the dates by manual method, (8/1, 8/2, 8/3, 8/4 e.t.c) and second is it possible to to fill the cells with grey colour instead of number "1" ? Thanks once again.

Steve=True · Aug 4, 2015

Hi Panoos,

You can create a formula in cell D1 that =Min(C:C) and in every cell to the right +1

For filling in the cells with a shading,

Here is a tutorial on Conditional formatting:
the-tricks-to-writing-a-conditional-formatting-rule-formula
Your formula would =d2=1 then set the format to fill with grey color.

Steve=True

Panoos64 · Aug 5, 2015

Hi Steve, Thanks you for your advice and your support. It works perfect now. Thanks once again. Kind regards

Panoos

Steve=True · Aug 5, 2015

Awesome, so glad to help. Thanks for letting me know.

Create annual leaves chart

Panoos64

Well-known Member

Excel Facts

Steve=True

Well-known Member

Panoos64

Well-known Member

Steve=True

Well-known Member

Panoos64

Well-known Member

Steve=True

Well-known Member

Similar threads

Forum statistics

Share this page

Create annual leaves chart

Panoos64

Well-known Member

Excel Facts

Steve=True

Well-known Member

Panoos64

Well-known Member

Steve=True

Well-known Member

Panoos64

Well-known Member

Steve=True

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock