Need a formula that counts the shifts and gives me a total

[Ramballah]

Hi,

So I have a roster in excel, and it works wonders for me, however my boss wants to have a total hours per person in the sheet aswell.
So how my sheet works, there are a few different shifts that we deal with and so I use a numbering system to fill it in. It'll make sense if you see the sheet.
Anyway, what I need is a total of hours each person works for the month. But there are a few issues which makes it hard for me to write my own formula.
Here's a picture first:

So a little translation for the hours:
1 = 09:30 AM to 3:30 PM
2 = 1 PM to 9 PM
3 = 2 PM to 10 PM
4 = 15 PM to 10:30 AM next day
And now what makes the issue is that when number 3 is on a saturday or sunday, the time will be 3PM to 10PM (my colleagues know this but its for the hours formula)
And 4 as you can see, I place it on 2 days but its just 1 shift. But when its on a saturday or sunday the end time is 11AM instead of 10:30 AM.
And with the number 4 shifts, there is also 4 hours own time that needs to be substracted.
So for the example:
Michelle works 9 day shifts (number 1) which is 6 hours each so thats 54 hours
She also works 2 number 3 shifts which is 16 hours (2x 8h) and none of them are in the weekend otherwise it would be a 7hr shift
She also works 4 number 2 shifts which is 32 hours (4x 8h)
But she also works 3 sleep shifts, 1 of them is a regular time so thats 19:30 hours, but minus the 4 hours so thats 15:30 hours
but theres also 2 in the weekend so that would be 20 hours, but minus the 4 again 16 hours. times 2 is 32
So in total she works 32+15,5+32+16+54 = 149,5 hours.

So I hope this clears it up what I want and why I have a difficult time with my small brain lol. Also this is office 365
Thanks in advance!
Ramballah

 

[bobaol]

Hallo, try this... assume you are able to change your spreadsheet. The issue is you're treating Maandag to Vrijda the same as Zaturday and Zonday. For your example, i would treat Maandag to Vrijda differently than Zaturday and Zonday. I would add a Z to the end of the number to indicate it is a Zaturday or Zonday. Also, the sleep shift of 4 needs to be addressed. It seems sleep shift 4 always occur in two days. Because of that, take sleep shift 4 and divide by two to get the hours for each day. (the Nederlandness people work while sleeping?!?! that's an incredible work ethic!)

You have a big spreadsheet with a lot of space to play around. You don't need to do all your work in one cell. Ask one calculation in one cell, then ask another calculation in another cell, and so on, until you get to your answer. it is much easier to ask one calculation at a time, get the answer, then ask another calculation based on that answer, and so on, until you get your final answer. Again, you don't have to ask a hundred questions in one formula, you can ask one question at a time in one cell, then another single question in another cell until you get your final answer.

finally, the spreadsheet is changed so it has a point of reference. notice rows 50 to 85 are laid out in each single day and is used as a reference. You enter your data in B7 to AH12. you can use B7 to AH49, if you need to. The formulas are done in rows 107 to 149. it is in row 107 so it is easy to see it was from row 7, and so forth. Enclosed are the images of the spreadsheet, and the formulas are below. just copy the entire grid, and paste it to Cell A1. Proost!

MrExcel does not allow over 10,000 characters, so I'll post the formulas in 3 parts.

'Cell A1	'Need a formula that counts the shifts and gives me a total
'
'
'	Weekday in numbers		1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3
'	Day of month		1	=D5+1	=E5+1	=F5+1	=G5+1	=H5+1	=I5+1	=J5+1	=K5+1	=L5+1	=M5+1	=N5+1	=O5+1	=P5+1	=Q5+1	=R5+1	=S5+1	=T5+1	=U5+1	=V5+1	=W5+1	=X5+1	=Y5+1	=Z5+1	=AA5+1	=AB5+1	=AC5+1	=AD5+1	=AE5+1	=AF5+1	=AG5+1
'	Weekday		Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo
'	Corine
'	Jaco		1	1	3	3		3z				1	1			3z		4	4	1	1			2	2	2	2				1	1	1
'	Miriam		1	1	3	3		4Z	4Z			1	1		4Z	4Z		4	4	1	1				2	2	2						1
'Cell A10	Michelle		1	1	3	3		4Z	4Z			1	1		4Z	4Z		4	4	1	1			2	2	2	2				1	1	1
'	Aaron		1	1	3	3		4Z	4Z			1	1		4Z	4Z				1	1			2	2		2				1	1	1
;	Moses		1	1	3	3		4Z	4Z			1	1		4Z	4Z		4	4	1	1			2	2	2	2				1	1	1
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'Cell A48

 

[bobaol]

part2.

''Cell A49

'reference

day

Shift

re-name Shift

shift begin

Shift end

hours worked

hr worked

min worked

hr subtrated

Net hr workded

Net hr and min worked

convert to number

convert to number2

use for calculation

'

Ma

1

1

32663.3958333333

32663.6458333333

=F51-E51

=HOUR(G51)

=MINUTE(G51)

0

=H51-J51

=K51&":"&RIGHT("0"&I51,2)

=IF(I51=30,0.5,I51)

=K51+M51

=N51

'

Di

1

1

32663.3958333333

32663.6458333333

=F52-E52

=HOUR(G52)

=MINUTE(G52)

0

=H52-J52

=K52&":"&RIGHT("0"&I52,2)

=IF(I52=30,0.5,I52)

=K52+M52

=N52

'

Wo

1

1

32663.3958333333

32663.6458333333

=F53-E53

=HOUR(G53)

=MINUTE(G53)

0

=H53-J53

=K53&":"&RIGHT("0"&I53,2)

=IF(I53=30,0.5,I53)

=K53+M53

=N53

'

Do

1

1

32663.3958333333

32663.6458333333

=F54-E54

=HOUR(G54)

=MINUTE(G54)

0

=H54-J54

=K54&":"&RIGHT("0"&I54,2)

=IF(I54=30,0.5,I54)

=K54+M54

=N54

'

Vr

1

1

32663.3958333333

32663.6458333333

=F55-E55

=HOUR(G55)

=MINUTE(G55)

0

=H55-J55

=K55&":"&RIGHT("0"&I55,2)

=IF(I55=30,0.5,I55)

=K55+M55

=N55

'

Za

1

1Z

32663.3958333333

32663.6458333333

=F56-E56

=HOUR(G56)

=MINUTE(G56)

0

=H56-J56

=K56&":"&RIGHT("0"&I56,2)

=IF(I56=30,0.5,I56)

=K56+M56

=N56

'

Zo

1

1Z

32663.3958333333

32663.6458333333

=F57-E57

=HOUR(G57)

=MINUTE(G57)

0

=H57-J57

=K57&":"&RIGHT("0"&I57,2)

=IF(I57=30,0.5,I57)

=K57+M57

=N57

'

Ma

2

2

32663.5416666667

32663.875

=F58-E58

=HOUR(G58)

=MINUTE(G58)

0

=H58-J58

=K58&":"&RIGHT("0"&I58,2)

=IF(I58=30,0.5,I58)

=K58+M58

=N58

'

Di

=C58

=D58

32663.5416666667

32663.875

=F59-E59

=HOUR(G59)

=MINUTE(G59)

0

=H59-J59

=K59&":"&RIGHT("0"&I59,2)

=IF(I59=30,0.5,I59)

=K59+M59

=N59

'

Wo

=C59

=D59

32663.5416666667

32663.875

=F60-E60

=HOUR(G60)

=MINUTE(G60)

0

=H60-J60

=K60&":"&RIGHT("0"&I60,2)

=IF(I60=30,0.5,I60)

=K60+M60

=N60

'

Do

=C60

=D60

32663.5416666667

32663.875

=F61-E61

=HOUR(G61)

=MINUTE(G61)

0

=H61-J61

=K61&":"&RIGHT("0"&I61,2)

=IF(I61=30,0.5,I61)

=K61+M61

=N61

'

Vr

=C61

=D61

32663.5416666667

32663.875

=F62-E62

=HOUR(G62)

=MINUTE(G62)

0

=H62-J62

=K62&":"&RIGHT("0"&I62,2)

=IF(I62=30,0.5,I62)

=K62+M62

=N62

'

Za

=C62

2Z

32663.5416666667

32663.875

=F63-E63

=HOUR(G63)

=MINUTE(G63)

0

=H63-J63

=K63&":"&RIGHT("0"&I63,2)

=IF(I63=30,0.5,I63)

=K63+M63

=N63

'

Zo

=C63

=D63

32663.5416666667

32663.875

=F64-E64

=HOUR(G64)

=MINUTE(G64)

0

=H64-J64

=K64&":"&RIGHT("0"&I64,2)

=IF(I64=30,0.5,I64)

=K64+M64

=N64

'

Ma

3

3

32663.5833333333

32663.9166666667

=F65-E65

=HOUR(G65)

=MINUTE(G65)

0

=H65-J65

=K65&":"&RIGHT("0"&I65,2)

=IF(I65=30,0.5,I65)

=K65+M65

=N65

'

Di

=C65

=D65

32663.5833333333

32663.9166666667

=F66-E66

=HOUR(G66)

=MINUTE(G66)

0

=H66-J66

=K66&":"&RIGHT("0"&I66,2)

=IF(I66=30,0.5,I66)

=K66+M66

=N66

'

Wo

=C66

=D66

32663.5833333333

32663.9166666667

=F67-E67

=HOUR(G67)

=MINUTE(G67)

0

=H67-J67

=K67&":"&RIGHT("0"&I67,2)

=IF(I67=30,0.5,I67)

=K67+M67

=N67

'

Do

=C67

=D67

32663.5833333333

32663.9166666667

=F68-E68

=HOUR(G68)

=MINUTE(G68)

0

=H68-J68

=K68&":"&RIGHT("0"&I68,2)

=IF(I68=30,0.5,I68)

=K68+M68

=N68

'

Vr

=C68

=D68

32663.5833333333

32663.9166666667

=F69-E69

=HOUR(G69)

=MINUTE(G69)

0

=H69-J69

=K69&":"&RIGHT("0"&I69,2)

=IF(I69=30,0.5,I69)

=K69+M69

=N69

'

Za

=C69

3Z

32663.625

32663.9166666667

=F70-E70

=HOUR(G70)

=MINUTE(G70)

0

=H70-J70

=K70&":"&RIGHT("0"&I70,2)

=IF(I70=30,0.5,I70)

=K70+M70

=N70

'

Zo

=C70

=D70

32663.625

32663.9166666667

=F71-E71

=HOUR(G71)

=MINUTE(G71)

0

=H71-J71

=K71&":"&RIGHT("0"&I71,2)

=IF(I71=30,0.5,I71)

=K71+M71

=N71

'

Ma

4

4

32663.625

32664.4375

=F72-E72

=HOUR(G72)

=MINUTE(G72)

4

=H72-J72

=K72&":"&RIGHT("0"&I72,2)

=IF(I72=30,0.5,I72)

=K72+M72

=N72/2

'

Di

=C72

=D72

32663.625

32664.4375

=F73-E73

=HOUR(G73)

=MINUTE(G73)

4

=H73-J73

=K73&":"&RIGHT("0"&I73,2)

=IF(I73=30,0.5,I73)

=K73+M73

=N73/2

'

Wo

=C73

=D73

32663.625

32664.4375

=F74-E74

=HOUR(G74)

=MINUTE(G74)

4

=H74-J74

=K74&":"&RIGHT("0"&I74,2)

=IF(I74=30,0.5,I74)

=K74+M74

=N74/2

'

Do

=C74

=D74

32663.625

32664.4375

=F75-E75

=HOUR(G75)

=MINUTE(G75)

4

=H75-J75

=K75&":"&RIGHT("0"&I75,2)

=IF(I75=30,0.5,I75)

=K75+M75

=N75/2

'

Vr

=C75

=D75

32663.625

32664.4375

=F76-E76

=HOUR(G76)

=MINUTE(G76)

4

=H76-J76

=K76&":"&RIGHT("0"&I76,2)

=IF(I76=30,0.5,I76)

=K76+M76

=N76/2

'

Za

=C76

4Z

32663.625

32664.4583333333

=F77-E77

=HOUR(G77)

=MINUTE(G77)

4

=H77-J77

=K77&":"&RIGHT("0"&I77,2)

=IF(I77=30,0.5,I77)

=K77+M77

=N77/2

'

Zo

=C77

=D77

32663.625

32664.4583333333

=F78-E78

=HOUR(G78)

=MINUTE(G78)

4

=H78-J78

=K78&":"&RIGHT("0"&I78,2)

=IF(I78=30,0.5,I78)

=K78+M78

=N78/2

'

Ma

5

5

=HOUR(G79)

=MINUTE(G79)

=K79&":"&RIGHT("0"&I79,2)

=IF(I79=30,0.5,I79)

=K79+M79

=N79

'

Di

=C79

=D79

=HOUR(G80)

=MINUTE(G80)

=K80&":"&RIGHT("0"&I80,2)

=IF(I80=30,0.5,I80)

=K80+M80

=N80

'

Wo

=C80

=D80

=HOUR(G81)

=MINUTE(G81)

=K81&":"&RIGHT("0"&I81,2)

=IF(I81=30,0.5,I81)

=K81+M81

=N81

'

Do

=C81

=D81

=HOUR(G82)

=MINUTE(G82)

=K82&":"&RIGHT("0"&I82,2)

=IF(I82=30,0.5,I82)

=K82+M82

=N82

'

Vr

=C82

=D82

=HOUR(G83)

=MINUTE(G83)

=K83&":"&RIGHT("0"&I83,2)

=IF(I83=30,0.5,I83)

=K83+M83

=N83

'

Za

=C83

5Z

=HOUR(G84)

=MINUTE(G84)

=K84&":"&RIGHT("0"&I84,2)

=IF(I84=30,0.5,I84)

=K84+M84

=N84

'

Zo

=C84

=D84

=HOUR(G85)

=MINUTE(G85)

=K85&":"&RIGHT("0"&I85,2)

=IF(I85=30,0.5,I85)

=K85+M85

=N85

'

'

'

'

'

'

'

'

'

'

'

'

'

'

'Cell A100