Calendar Help

Liverlee · Dec 20, 2019

On row 2 – I’d like to group all the cells for each month, January 2020, feb 2020, march 2020, I can’t click and drag as there are a different number of cells for each month, some consisting of 31 days, some 30, some 28 etc & for it be formatted as month and year.

Book1

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

3

01/01/2020

4

Name

Team

01/01/2020

5

joe

TL

T6

Annual Leave

Meeting

6

mary

TL

T2

Annual Leave

7

anne

T2

NWD

8

joan

T2

Annual Leave

9

Nicola

T2

10

kelly

AT

T2

Annual Leave

11

sandra

AT

T2

NWD

12

nik

AT

T2

Annual Leave

13

mary

TL

T3

Annual Leave

14

sue

T3

15

mary

T3

16

paul

T3

Annual Leave

17

jen

T3

18

steve

T3

Annual Leave

19

gary

TL

T4

NWD

20

marty

T4

21

sue

T4

Annual Leave

22

emm

T4

23

eve

T1

Annual Leave

NWD

Annual Leave

NWD

24

lisa

T1

25

Team Total

26

1

27

Group Totals - Number of Staff

28

Team Leaders

29

Team 1

30

dean

31

mark

32

gary

33

Admin Team

34

Team Total

Calendar

Cell Formulas
Range	Formula
F3:T4	F3	=E3+1
D25:T25	D25	=SUBTOTAL(103,D5:D$24)
C28	C28	=SUMPRODUCT(SUBTOTAL(3,OFFSET(C5:C24,ROW(C5:C24)-MIN(ROW(C5:C24)),,1)),ISNUMBER(SEARCH("TL",C5:C24))+0)
D28	D28	=SUMPRODUCT(SUBTOTAL(3,OFFSET(C5:C25,ROW(C5:C25)-MIN(ROW(C5:C25)),,1)),ISNUMBER(SEARCH("TL",C5:C25))+0)
E28:T28	E28	=COUNTIFS($C$5:$C$24,"TL",E$5:E$24,"annual Leave")+COUNTIFS($C$5:$C$24,"TL",E$5:E$24,"NWD")+COUNTIFS($C$5:$C$24,"TL",E$5:E$24,"MEETING")+COUNTIFS($C$5:$C$24,"TL",E$5:E$24,"TRAINING")+COUNTIFS($C$5:$C$24,"TL",E$5:E$24,"OTHER")
D29	D29	=SUMPRODUCT(SUBTOTAL(3,OFFSET(D5:D25,ROW(D6:D25)-MIN(ROW(D6:D25)),,1)),ISNUMBER(SEARCH("T1",D6:D25))+0)
E29:T29	E29	=COUNTIFS($D$5:$D$24,"T1",E$5:E$24,"annual Leave")+COUNTIFS($D$5:$D$24,"T1",E$5:E$24,"NWD")+COUNTIFS($D$5:$D$24,"T1",E$5:E$24,"MEETING")+COUNTIFS($D$5:$D$24,"T1",E$5:E$24,"TRAINING")+COUNTIFS($D$5:$D$24,"T1",E$5:E$24,"OTHER")
D30	D30	=SUMPRODUCT(SUBTOTAL(3,OFFSET(D6:D26,ROW(D6:D26)-MIN(ROW(D6:D26)),,1)),ISNUMBER(SEARCH("T2",D6:D26))+0)
E30:T30	E30	=COUNTIFS($D$5:$D$24,"T2",E$5:E$24,"annual Leave")+COUNTIFS($D$5:$D$24,"T2",E$5:E$24,"NWD")+COUNTIFS($D$5:$D$24,"T2",E$5:E$24,"MEETING")+COUNTIFS($D$5:$D$24,"T2",E$5:E$24,"TRAINING")+COUNTIFS($D$5:$D$24,"T2",E$5:E$24,"OTHER")
D31	D31	=SUMPRODUCT(SUBTOTAL(3,OFFSET(D6:D28,ROW(D13:D28)-MIN(ROW(D13:D28)),,1)),ISNUMBER(SEARCH("T3",D13:D28))+0)
E31:T31	E31	=COUNTIFS($D$5:$D$24,"T3",E$5:E$24,"annual Leave")+COUNTIFS($D$5:$D$24,"T3",E$5:E$24,"NWD")+COUNTIFS($D$5:$D$24,"T3",E$5:E$24,"MEETING")+COUNTIFS($D$5:$D$24,"T3",E$5:E$24,"TRAINING")+COUNTIFS($D$5:$D$24,"T3",E$5:E$24,"OTHER")
D32	D32	=SUMPRODUCT(SUBTOTAL(3,OFFSET(D13:D29,ROW(D19:D29)-MIN(ROW(D19:D29)),,1)),ISNUMBER(SEARCH("T4",D19:D29))+0)
E32:T32	E32	=COUNTIFS($D$5:$D$24,"T4",E$5:E$24,"annual Leave")+COUNTIFS($D$5:$D$24,"T4",E$5:E$24,"NWD")+COUNTIFS($D$5:$D$24,"T4",E$5:E$24,"MEETING")+COUNTIFS($D$5:$D$24,"T4",E$5:E$24,"OTHER")
D33	D33	=SUMPRODUCT(SUBTOTAL(3,OFFSET(C5:C24,ROW(C5:C24)-MIN(ROW(C5:C24)),,1)),ISNUMBER(SEARCH("AT",C5:C24))+0)
E33:T33	E33	=COUNTIFS($C$5:$C$24,"AT",E$5:E$24,"annual Leave")+COUNTIFS($C$5:$C$24,"AT",E$5:E$24,"NWD")+COUNTIFS($C$5:$C$24,"AT",E$5:E$24,"MEETING")+COUNTIFS($C$5:$C$24,"AT",E$5:E$24,"TRAINING")+COUNTIFS($C$5:$C$24,"AT",E$5:E$24,"OTHER")
D34	D34	=D29+D30+D31+D32+1
E34:T34	E34	=SUM(E29:E32)
C33	C33	=SUMPRODUCT(SUBTOTAL(3,OFFSET(C5:C24,ROW(C5:C24)-MIN(ROW(C5:C24)),,1)),ISNUMBER(SEARCH("AT",C5:C24))+0)

Named Ranges
Name	Refers To	Cells
_1_JAN_2020	=Calendar!$E$3:$AI$34	F3:T4, E25:T25, E28:T34
_FilterDatabase	=Calendar!$B$4:$D$24	D25, C28:T28, D29:T32, C33:T33

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E28:NR28	Other Type	Icon set		NO
E23:AS23,AU23:NR23	Cell Value	contains "Meeting"	text	NO
E23:AS23,AU23:NR23	Cell Value	contains "Other"	text	NO
E23:AS23,AU23:NR23	Cell Value	contains "Annual Leave"	text	NO
E23:AS23,AU23:NR23	Cell Value	contains "Training"	text	NO
E23:AS23,AU23:NR23	Cell Value	contains "NWD"	text	NO
E23:AS23,AU23:NR23	Expression	=MATCH(E$4,'BH Dates'!$B$3:$B$11,0)	text	NO
E23:AS23,AU23:NR23	Expression	=WEEKDAY(E$4)=7	text	NO
E23:AS23,AU23:NR23	Expression	=WEEKDAY(E$4)=1	text	NO
E18:AS18,AU18:NR18	Cell Value	contains "Meeting"	text	NO
E18:AS18,AU18:NR18	Cell Value	contains "Other"	text	NO
E18:AS18,AU18:NR18	Cell Value	contains "Annual Leave"	text	NO
E18:AS18,AU18:NR18	Cell Value	contains "Training"	text	NO
E18:AS18,AU18:NR18	Cell Value	contains "NWD"	text	NO
E18:AS18,AU18:NR18	Expression	=MATCH(E$4,'BH Dates'!$B$3:$B$11,0)	text	NO
E18:AS18,AU18:NR18	Expression	=WEEKDAY(E$4)=7	text	NO
E18:AS18,AU18:NR18	Expression	=WEEKDAY(E$4)=1	text	NO
E30:NR31	Other Type	Icon set		NO
E33:NR33	Other Type	Icon set		NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Cell Value	contains "Meeting"	text	NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Cell Value	contains "Other"	text	NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Cell Value	contains "Annual Leave"	text	NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Cell Value	contains "Training"	text	NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Cell Value	contains "NWD"	text	NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Expression	=MATCH(E$4,'BH Dates'!$B$3:$B$11,0)	text	NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Expression	=WEEKDAY(E$4)=7	text	NO
E24:NR24,E5:NR5,E19:NR20,E6:AS17,AU6:NR17,AT6:AT18,E21:AS22,AU21:NR22,AT21:AT23	Expression	=WEEKDAY(E$4)=1	text	NO
E32:NR32	Other Type	Icon set		NO