Need Help With Calculating Fatigue in Excel

[FTMess]

I'm fairly new to Excel and I need help with a spreadsheet that I use for scheduling. I need a formula to calculate fatigue days after 7 days straight of working to show up in red. There are days and nights, the seven day fatigue resets for those working days after 36 hours off, for those working nights it resets after 48 hours. What kind of formula can I use to calculate how many days a person works and knows when that person reaches the 7th day and resets after the alloted time.

Thanks for your help
Eric

 

[Smitty]

Welcome to the Board!

At first glance I'd say you could use Conditional Formatting, but you'll need to post an example of your data. Check the HTML Maker link in my sig for how to do that. Once we have more details we can get a better idea of what you need.

 

[FTMess]

Here is a sample of the spreadsheet each cell is a dropdown list of people’s names that can work that job post. Are fatigue schedule says we can work 7 straight day without being an exception. On the 8th day we are in exception and we cannot work until we reset we work shift work so the day shift will reset after 36hrs night shift reset after 48hrs and then we can work another 7 days. Here is a sample of are schedule, work 4 days, off 7, work 4 nights, off 3, work 3 days, off 1, work 3 nights, off 3,then starts over on the 4 days. So I need a formula to calculate fatigue days after 7 days straight of working to show up in red automatically. What kind of formula can I use to calculate how many days a person works and knows when that person reaches the 7th day and resets after the allotted time and looks into the future to see if that person works he won’t go into Exception on his regular shift.</SPAN>

Thanks for your help
Eric

 

[FTMess]

Im at work and can out post an Image of my spreadsheet so I will have to do it when i'm at home. sorry about this.

 

[FTMess]

Here is a sample of the spreadsheet.. I hope it works

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	OLEFINS Shift Schedule
jul14-jul27

Cell Formulas
Range	Formula
A6	FLS
A7	Ethy Hot CCB
A8	Ethy Cold CCB
A9	Area 1
A10	Area 2
A11	Area 3
A12	Pumper
A13	Extra
A16	Training
A17	Vacation
A20	VPP
B6	Kennon
B7	Clark
B8	Merriwether
B9	OT
B10	Valentine
B11	Jacks
B12	Butler
B17	Poole
B20	Barbour
C6	Kennon
C7	Clark
C8	Merriwether
C9	OT
C10	Valentine
C11	Jacks
C12	Butler
C17	Poole
C20	Barbour
D6	Kennon
D7	Clark
D8	Merriwether
D9	Jacks
D10	Valentine
D11	OT
D12	Butler
D17	Poole
D20	Barbour
E6	Kennon
E7	Clark
E8	Merriwether
E9	Jacks
E10	Valentine
E11	OT
E12	Butler
E16	Murphy
E17	Poole
E20	Barbour
F6	Piper
F7	Spurlock
F8	OT
F9	Gans
F10	Minard
F11	Stansbury
F12	Foreman
F17	Baloney
G6	Piper
G7	Spurlock
G8	OT
G9	Gans
G10	Minard
G11	Stansbury
G12	Foreman
G17	Baloney
H6	Piper
H7	Spurlock
H8	OT
H9	Gans
H10	Minard
H11	Stansbury
H12	Foreman
H17	Baloney
I6	OT
I7	OT
I8	Day
I9	Wooten
I10	Scott
I11	Montes
I12	Worth
I17	Caldwell
I20	Barbour
J6	OT
J7	OT
J8	Day
J9	Wooten
J10	Scott
J11	Montes
J12	Worth
J17	Caldwell
J20	Barbour
K6	Taylor
K7	OT
K8	Day
K9	Wooten
K10	Scott
K11	Montes
K12	Worth
K17	Caldwell
K20	Barbour
L6	Taylor
L7	OT
L8	Day
L9	Wooten
L10	Scott
L11	Montes
L12	Worth
L17	Caldwell
L20	Barbour
M6	Gage
M7	Murphy
M8	Huff
M9	Bettis
M10	Johnson
M11	Sadler
M12	Desouza
N6	Gage
N7	Murphy
N8	Huff
N9	Bettis
N10	Johnson
N11	Sadler
N12	Desouza
O6	Gage
O7	Murphy
O8	Huff
O9	Bettis
O10	Johnson
O11	Sadler
O12	Desouza

Named Ranges
Name	Refers To	Cells
FLS	='jul14-jul27'!$R$1:$AI$1