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]

You're trying to return 30% and 60% of the time difference of each cell or each column?

 

[mole999]

You're trying to return 30% and 60% of the time difference of each cell or each column?

First off, I just trying to return to the UDF A day, a left and right split of the time formatted as time. and the cells containing .3 and .6 so I can work with them in the UDF (Its going to get complex when rolled over night) and put a decimal time value back in the cell when its been processed. The longest rota is 43 weeks, If I can get the days to work appropriately then I stand some chance

 

[sheetspread]

You can split the strings into an array and format them to time, then calculate the difference or multiply them by the percents

 

[mole999]

You can split the strings into an array and format them to time, then calculate the difference or multiply them by the percents

Far more complex than that, yes the percentage will be applied to the whole hours. I don't know enough to just pick up the bits i want efficiently so i can take that forward

 

[sheetspread]

This returned the day differences on your sample (easily converted to hours, and can include the percentages once I understand their purpose):

Excel 2010
	A	B	C	D	E	F	G
1	Band >	4	<	30%	60%
2		Mon	Tue	Wed	Thu
3	1	0630-1830	0630-1830	0630-1830	0630-1830		0.5
4	2	NRD	NRD	NRD	NRD
5	3	1800-0500	NRD	NRD	NRD		12
6	4	NRD	1800-0500	1800-0500	1800-0500
7	5	0715-1915	0715-1915	0715-1915	0715-1915
Sheet2

Cell Formulas
Range	Formula
G3	=daterangeconv(C7)
G5	=G3*24

Function daterangeconv(rng As Range)
Dim arr As Variant, t0 As Double, t1 As Double
arr = Split(rng, "-")
t0 = TimeValue(Format(arr(0), "0:00"))
t1 = TimeValue(Format(arr(1), "0:00"))
If t1 < t0 And t1 < 0.5 Then t1 = t1 + 1
daterangeconv = t1 - t0
End Function

 

[mole999]

OK the rules
depending on staff grade in the NHS a different overtime enhancement is applied, the lower the grade the higher the uplift

Excel Workbook
	A	B	C
2	Band	Time Plus
3	1	50%	100%
4	2	44%	88%
5	3	37%	74%
6	4	30%	60%
7	5	30%	60%
8	6	30%	60%
9	7	30%	60%
10	8	30%	60%
11	9	30%	60%
Bands Enhancements

8pm to 6am monday to friday is enhanced hours (first uplift), all day saturday and sunday moves to the next banding.

then the wrinkle, any weekday shift where more than 50% occurs after 8pm, has the pre eight pm start enhanced so 18:00 to 02:00 qualifies (75% after 20:00, but the enhancement is now paid from 18:00 rather than 20:00.

And the flip side is highest enhancement on a sunday that rolls into a monday night where the value drops, its going to take ages to do it all, I'm looking to rough out as much as possible, before looking for cases were it falls over.

I need to hand encode a complete set so i can then test against a calculted formula

and one other wrinkle, it has to roll sunday into monday, bearing in mind that the last row of the formula will have to then reflect from the first row on the Monday

 

[sheetspread]

Is the table in post #1 for a single person? If so does that mean they had no weekends?

 

[mole999]

24/7/365 its part of one rota of 43 weeks. would it help if i posted this rota in full. each person would work round this rota and repeat

 

[mole999]

Excel Workbook
	A	B	C	D	E	F	G	H
4		Mon	Tue	Wed	Thu	Fri	Sat	Sun
5	1	0630-1830	0630-1830	0630-1830	0630-1830	NRD	NRD	NRD
6	2	NRD	NRD	NRD	NRD	1800-0500	1800-0500	1800-0500
7	3	1800-0500	NRD	NRD	NRD	0630-1830	0630-1830	0630-1830
8	4	NRD	1800-0500	1800-0500	1800-0500	NRD	NRD	NRD
9	5	0715-1915	0715-1915	0715-1915	0715-1915	NRD	NRD	NRD
10	6	NRD	NRD	NRD	NRD	1915-0715	1915-0715	1915-0715
11	7	1915-0715	NRD	NRD	NRD	0715-1915	0715-1915	0715-1915
12	8	NRD	1915-0715	1915-0715	1915-0715	NRD	NRD	NRD
13	9	0745-1900	0745-1900	0745-1900	0745-1900	NRD	NRD	NRD
14	10	NRD	NRD	NRD	NRD	1900-0600	1900-0600	1900-0600
15	11	1900-0600	NRD	NRD	NRD	0700-1700	0815-1600	0700-1500
16	12	NRD	1900-0600	1900-0600	1900-0600	NRD	NRD	NRD
17	13	0730-1600	0730-1600	0730-1600	0730-1600	NRD	NRD	NRD
18	14	NRD	NRD	NRD	NRD	0645-1845	0645-1845	0645-1845
19	15	0645-1845	NRD	NRD	NRD	0730-1600	0900-1700	0900-1700
20	16	NRD	0645-1845	0645-1845	0645-1845	NRD	NRD	NRD
21	17	0700-1545	0700-1545	0700-1545	0700-1545	0700-1545	NRD	NRD
22	18	1600-0200	1600-0200	1600-0200	1600-0200	NRD	NRD	NRD
23	19	1100-2000	1100-2000	1545-0000	NRD	NRD	0845-1800	0845-1800
24	20	1545-0000	1545-0000	NRD	NRD	1600-0100	1600-0100	1500-0000
25	21	NRD	NRD	NRD	1545-0000	1545-0100	1700-0100	1700-0000
26	22	NRD	NRD	1100-2000	1100-2000	1100-2000	NRD	NRD
27	23	1845-0645	1845-0645	1845-0645	1845-0645	NRD	NRD	NRD
28	24	NRD	NRD	NRD	NRD	1845-0645	1845-0645	1845-0645
29	25	1815-0615	NRD	NRD	NRD	1815-0615	1815-0615	1815-0615
30	26	NRD	1815-0615	1815-0615	1815-0615	NRD	NRD	NRD
31	27	0700-1800	0700-1500	NRD	NRD	1700-0200	1700-0200	1715-0200
32	28	NRD	NRD	1500-0000	1500-0000	NRD	NRD	NRD
33	29	1830-0630	1830-0630	1830-0630	NRD	NRD	NRD	NRD
34	30	0600-1800	0600-1800	0600-1800	NRD	NRD	0900-1700	0900-1900
35	31	0700-1500	NRD	NRD	NRD	0615-1815	0615-1815	0615-1815
36	32	NRD	NRD	NRD	1830-0630	1830-0630	1830-0630	1830-0630
37	33	NRD	NRD	NRD	1800-0500	1800-0500	NRD	NRD
38	34	NRD	0615-1815	0615-1815	0615-1815	NRD	NRD	NRD
39	35	1800-0500	1800-0500	1800-0500	NRD	NRD	NRD	NRD
40	36	1500-0000	1500-0000	NRD	NRD	NRD	0600-1800	0600-1800
41	37	0615-1815	NRD	NRD	NRD	0700-1900	0700-1900	0830-1715
42	38	NRD	NRD	NRD	0730-1800	0730-1800	1800-0500	1800-0500
43	39	NRD	NRD	0730-1800	0600-1800	0600-1800	NRD	NRD
44	40	NRD	0700-1800	0700-1500	0700-1500	NRD	NRD	NRD
45	41	NRD	NRD	NRD	0700-1900	1900-0700	1900-0700	1900-0700
46	42	NRD	NRD	1615-0200	1548-0200	NRD	NRD	NRD
47	43	1900-0700	1900-0700	1900-0700	1900-0700	NRD	NRD	NRD
Expected