Simple Question

[gosman19]

I am relatively new to excel so sorry for the noob question...

I am doing a rota/target calculator for work, i have the formula to calculate the targets but i need my rotas to populate the first part of the formula.

soo.....


rota worksheet:

Name Monday Tuesday
Nick 0900-1800 Off
Tom 1100-1600....etc

I need a rota like the one above to populate an hourly timetable that will simply state whether or not the person is in at that hour based on the rota above...

Daily Rota

Name 0900-1000 1000-1100 1100-1200 ... etc
Nick 1 1 1
Tom 0 0 1

(1 or a true value for the hours he is in and then a 0 or false value for the hours he is not....)

I know what i need do just cant type the formula!

once i have that i can do the rest its just that little bit......

Any response would be appreciated

Thank you.

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Monday</td><td style="font-weight: bold;;">Tuesday </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Nick</td><td style=";">0900-1800</td><td style=";">Off</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tom</td><td style=";">1100-1600</td><td style=";">Off</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">MONDAY</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;text-align: right;;">0900</td><td style="font-weight: bold;text-align: right;;">1000</td><td style="font-weight: bold;text-align: right;;">1100</td><td style="font-weight: bold;text-align: right;;">1200</td><td style="font-weight: bold;text-align: right;;">1300</td><td style="font-weight: bold;text-align: right;;">1400</td><td style="font-weight: bold;text-align: right;;">1500</td><td style="font-weight: bold;text-align: right;;">1600</td><td style="font-weight: bold;text-align: right;;">1700</td><td style="font-weight: bold;text-align: right;;">1800</td><td style="font-weight: bold;text-align: right;;">1900</td><td style="font-weight: bold;text-align: right;;">2000</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Nick</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Tom</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=IF(<font color="Blue">$B2="OFF","",(<font color="Red">AND(<font color="Green">B$6>=LEFT(<font color="Purple">$B2,4</font>)*1,B$6<=RIGHT(<font color="Purple">$B2,4</font>)*1</font>)*1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[gosman19]

Brilliant thank you very much works a treat!

 

[AlphaFrog]

You're welcome.

A possible formula correction:
The End-time for a shift is say 1800. 1700 should have a 1 but 1800 should have a 0 because the shift ends at the begining of the 1800 hour. The hour "1800" is not actually worked. If this is correct for your situation, the minor formula correction is to omit one equal sign...

B7
=IF($B2="OFF","",(AND(B$6>=LEFT($B2,4)*1,B$6<RIGHT($B2,4)*1)*1))

 

[gosman19]

good spot thank you again!