UDFs for rotas

[mole999]

I've been trying to figure out something for work, initially to break down the component parts with formulas and then produce a result.

It finally dawned on me i might be better off writing a UDF for simplicity, and then I hit the snag I don't know enough about them to even start a small one (A good reference guide would be useful if you know of one)

The data

Excel Workbook
	A	B	C	D	E
3	Band >	4		30%	60%
4		Mon	Tue	Wed	Thu
5	1	0630-1830	0630-1830	0630-1830	0630-1830
6	2	NRD	NRD	NRD	NRD
7	3	1800-0500	NRD	NRD	NRD
8	4	NRD	1800-0500	1800-0500	1800-0500
9	5	0715-1915	0715-1915	0715-1915	0715-1915
Section 2

I need to look at the day and use that in the function, also the two percentage enteries, and for each cell take the string value, change to time values, so I can return a decimal hours between the highest / lowest (even across midnight)

I started with (which is rubbish)

Public Function Section2(rng As Range)
'Public Function Section2(start_time As String)
'Public Function Section2(DayValue As String, StartTime As String, EndTime As String)
'DayValue =
Dim Starttimetime As Date
Dim Endtimetime As Date
'Dim starttime
Dim endtime
start_time = Left(start_time, 4)
endtime = Right(endtime, 4)
Starttimetime = TimeValue(start_time)
Endtimetime = TimeValue(endtime)
End Function

can someone just fix the first part so that the values are drawn into the output cell, and I have day and percentages available in code to work from

 

[sheetspread]

It seems better to unpivot the table and split the times into separate columns (you can still have the original as an entry form). Then calculate the special conditions like:

Excel 2010
	A	B	C	D	E	F	G
1	Week	Column	Time In	Hours	After 8pm	>50%?	6pm-2am hours
2	1	Mon	0.270833	0.5	0	0	0
3	1	Tue	0.270833	0.5	0	0	0
4	1	Wed	0.270833	0.5	0	0	0
5	1	Thu	0.270833	0.5	0	0	0
6	1	Fri		0	0	0	0
7	1	Sat		0	0	0	0
8	1	Sun		0	0	0	0
9	2	Mon		0	0	0	0
10	2	Tue		0	0	0	0
11	2	Wed		0	0	0	0
12	2	Thu		0	0	0	0
13	2	Fri	0.75	0.458333	0.375	1	0.33333333
14	2	Sat	0.75	0.458333	0.375	1	0.33333333
15	2	Sun	0.75	0.458333	0.375	1	0.33333333
16	3	Mon	0.75	0.458333	0.375	1	0.33333333
17	3	Tue		0	0	0	0
18	3	Wed		0	0	0	0
19	3	Thu		0	0	0	0
20	3	Fri	0.270833	0.5	0	0	0
21	3	Sat	0.270833	0.5	0	0	0
22	3	Sun	0.270833	0.5	0	0	0
23	4	Mon		0	0	0	0
24	4	Tue	0.75	0.458333	0.375	1	0.33333333
25	4	Wed	0.75	0.458333	0.375	1	0.33333333
26	4	Thu	0.75	0.458333	0.375	1	0.33333333
27	4	Fri		0	0	0	0
28	4	Sat		0	0	0	0
29	4	Sun		0	0	0	0
30	5	Mon	0.302083	0.5	0	0	0
31	5	Tue	0.302083	0.5	0	0	0
32	5	Wed	0.302083	0.5	0	0	0
33	5	Thu	0.302083	0.5	0	0	0
34	5	Fri		0	0	0	0
35	5	Sat		0	0	0	0
36	5	Sun		0	0	0	0
37	6	Mon		0	0	0	0
38	6	Tue		0	0	0	0
39	6	Wed		0	0	0	0
40	6	Thu		0	0	0	0
41	6	Fri	0.802083	0.5	0.46875	1	0.33333333
42	6	Sat	0.802083	0.5	0.46875	1	0.33333333
43	6	Sun	0.802083	0.5	0.46875	1	0.33333333
44	7	Mon	0.802083	0.5	0.46875	1	0.33333333
45	7	Tue		0	0	0	0
46	7	Wed		0	0	0	0
47	7	Thu		0	0	0	0
48	7	Fri	0.302083	0.5	0	0	0
49	7	Sat	0.302083	0.5	0	0	0
50	7	Sun	0.302083	0.5	0	0	0
51	8	Mon		0	0	0	0
52	8	Tue	0.802083	0.5	0.46875	1	0.33333333
53	8	Wed	0.802083	0.5	0.46875	1	0.33333333
54	8	Thu	0.802083	0.5	0.46875	1	0.33333333
55	8	Fri		0	0	0	0
56	8	Sat		0	0	0	0
57	8	Sun		0	0	0	0
58	9	Mon	0.322917	0.46875	0	0	0
59	9	Tue	0.322917	0.46875	0	0	0
60	9	Wed	0.322917	0.46875	0	0	0
61	9	Thu	0.322917	0.46875	0	0	0
62	9	Fri		0	0	0	0
63	9	Sat		0	0	0	0
64	9	Sun		0	0	0	0
65	10	Mon		0	0	0	0
66	10	Tue		0	0	0	0
67	10	Wed		0	0	0	0
68	10	Thu		0	0	0	0
69	10	Fri	0.791667	0.458333	0.416666667	1	0.33333333
70	10	Sat	0.791667	0.458333	0.416666667	1	0.33333333
71	10	Sun	0.791667	0.458333	0.416666667	1	0.33333333
72	11	Mon	0.791667	0.458333	0.416666667	1	0.33333333
73	11	Tue		0	0	0	0
74	11	Wed		0	0	0	0
75	11	Thu		0	0	0	0
76	11	Fri	0.291667	0.416667	0	0	0
77	11	Sat	0.34375	0.322917	0	0	0
78	11	Sun	0.291667	0.333333	0	0	0
79	12	Mon		0	0	0	0
80	12	Tue	0.791667	0.458333	0.416666667	1	0.33333333
81	12	Wed	0.791667	0.458333	0.416666667	1	0.33333333
82	12	Thu	0.791667	0.458333	0.416666667	1	0.33333333
83	12	Fri		0	0	0	0
84	12	Sat		0	0	0	0
85	12	Sun		0	0	0	0
86	13	Mon	0.3125	0.354167	0	0	0
87	13	Tue	0.3125	0.354167	0	0	0
88	13	Wed	0.3125	0.354167	0	0	0
89	13	Thu	0.3125	0.354167	0	0	0
90	13	Fri		0	0	0	0
91	13	Sat		0	0	0	0
92	13	Sun		0	0	0	0
93	14	Mon		0	0	0	0
94	14	Tue		0	0	0	0
95	14	Wed		0	0	0	0
96	14	Thu		0	0	0	0
97	14	Fri	0.28125	0.5	0	0	0
98	14	Sat	0.28125	0.5	0	0	0
99	14	Sun	0.28125	0.5	0	0	0
100	15	Mon	0.28125	0.5	0	0	0
Sheet5

Cell Formulas
Range	Formula
E2	=MAX((C2+D2)-(20/24),0)
F2	=--(IFERROR(E2/D2,0)>0.5)
G2	=F2*MEDIAN(0,(C2+D2)-(18/24),0.33333333)

enhancing the bands and rolling sundays as needed.