color code automatically.

[bestdad]

Ok I think i have the solution to my problem just need help for the formula/validation rules.

I am creating an employee schedule Sunday thru Saturday. It has a time in and a time out cell.

On a new worksheet I want the time in and time out to duplicate it self then from cell E8:S8 a break out per hr. I want the formula to look at the timein and timeout then color in the correct amount of hrs for that shift.

Example
So if employee A worked from 8 Am to 10 AM then total hrs would be 2 and it be color coded for that shift.

<TABLE style="WIDTH: 1002pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1334><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" span=3 width=63><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" span=2 width=57><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; HEIGHT: 30pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=40 rowSpan=2 width=35>[FONT=Calibri]ID[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 rowSpan=2 width=142>[FONT=Calibri]Name[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 rowSpan=2 width=64>[FONT=Calibri]Time In[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 rowSpan=2 width=64>[FONT=Calibri]Time Out[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=58>[FONT=Calibri]8:00 AM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=61>[FONT=Calibri]9:00 AM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=63>[FONT=Calibri]10:00 AM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=63>[FONT=Calibri]11:00 AM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=63>[FONT=Calibri]12:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=58>[FONT=Calibri]1:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=57>[FONT=Calibri]2:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=55>[FONT=Calibri]3:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=56>[FONT=Calibri]4:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=54>[FONT=Calibri]5:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=56>[FONT=Calibri]6:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=55>[FONT=Calibri]7:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=57>[FONT=Calibri]8:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=57>[FONT=Calibri]9:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 rowSpan=2 width=63>[FONT=Calibri]10:00 PM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 rowSpan=2 width=54>[FONT=Calibri]Tot Hrs[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 rowSpan=2 width=99>[FONT=Calibri]Employee Cost[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=20>[FONT=Calibri]1001[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">[FONT=Calibri]Employee A[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>[FONT=Calibri]8:00 AM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>[FONT=Calibri]10:00 AM[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>[FONT=Calibri]2[/FONT]</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>[FONT=Calibri]$17.34[/FONT]</TD></TR></TBODY></TABLE>

Thanks for the help. I know this should be done. on my previous attepts I was trying to over complicate my goal.

 

[HalfAce]

I put your example data in row 1 with 8:00 AM in D1, 9:00 AM in E1, etc.
The time in entry in B2 with the time out entry in C2.

You can use conditional formatting in D2, using Formula Is with the formula: =$C2>D$1 and format to suit. You can then copy the format to the rest of your range of interest.

All it does is look at the time out entry (in each row) and if the value there is greater than the hour header at the top of the column, the format gets invoked.

Hope it helps.