Yearly Attendance Record - Formula Help!

[Justinian]

I do payroll for 100 people. Each person has an attendance record with one full year of data (only used to note vacation, sick, leave of absence, tardiness, etc). Up until now, this was all done by hand and I am trying to make my lief easier by creating the sheet in Excel to do some math for me. I need help with two things and hope someone can assist!

1. When I insert a slash or format a cell with a slash, I need a formula or some CF that sees that slash and highlights a cell for that specific date. When an employee receives a slash, they were either late or left early and the manager needs to see a note for that day.

2. When an employee's hire date hits, they re-up their benefit hours (sick, vacation, and holidays). I already have a running total and an amount for theuir re-up but I want to incorporate a formula that knows when that anniversay date is and adds the new package amounts to the existing hours that employee has.

Can anyone help?

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:59px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:33px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td><td >AD</td><td >AE</td><td >AF</td><td >AG</td><td >AH</td><td >AI</td><td >AJ</td><td >AK</td></tr><tr style="height:14px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#008080; font-family:Arial; ">NAME OF EMPLOYEE</td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td style="color:#008080; font-family:Arial; ">DEPARTMENT</td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td style="color:#008080; font-family:Arial; ">NUMBER</td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td style="color:#008080; font-family:Arial; ">YEAR</td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:27px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td style="color:#008080; font-family:Arial; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:13px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:33px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">MONTH</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">1</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">2</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">3</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">4</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">5</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">6</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">7</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">8</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">9</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">10</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">11</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">12</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">13</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">14</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">15</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">16</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">17</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">18</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">19</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">20</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">21</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">22</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">23</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">24</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">25</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">26</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">27</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">28</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">29</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">30</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">31</td><td > </td><td style="text-align:center; ">Vacation</td><td style="text-align:center; ">Sick</td><td style="text-align:center; ">Floating</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#008080; text-align:center; ">Jan</td><td > </td><td style="text-align:center; ">s</td><td > </td><td > </td><td style="text-align:center; ">s</td><td > </td><td > </td><td style="text-align:center; ">v</td><td > </td><td > </td><td style="text-align:center; ">fh</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">s</td><td > </td><td > </td><td > </td><td style="text-align:center; ">fh</td><td > </td><td > </td><td style="text-align:center; ">v</td><td style="text-align:center; ">v</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">3</td><td style="text-align:center; ">3</td><td style="text-align:center; ">2</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">Feb</td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#008080; text-align:center; ">Mar</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ccffcc; color:#008080; text-align:center; ">Apr</td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td style="background-color:#ccffcc; color:#008080; "> </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >AI6</td><td >=IF(COUNTA<span style=' color:008000; '>(C6:AG6)</span>=0,"",COUNTIF<span style=' color:008000; '>(C6:AG6,"v")</span>)</td></tr><tr><td >AJ6</td><td >=IF(COUNTA<span style=' color:008000; '>(C6:AG6)</span>=0,"",COUNTIF<span style=' color:008000; '>(C6:AG6,"s")</span>)</td></tr><tr><td >AK6</td><td >=IF(COUNTA<span style=' color:008000; '>(C6:AG6)</span>=0,"",COUNTIF<span style=' color:008000; '>(C6:AG6,"fh")</span>)</td></tr><tr><td >AI7</td><td >=IF(COUNT<span style=' color:008000; '>(C7:AG7)</span>=0,"",COUNTIF<span style=' color:008000; '>(C7:AG7,"v")</span>)</td></tr><tr><td >AJ7</td><td >=IF(COUNTA<span style=' color:008000; '>(C7:AG7)</span>=0,"",COUNTIF<span style=' color:008000; '>(C7:AG7,"s")</span>)</td></tr><tr><td >AK7</td><td >=IF(COUNTA<span style=' color:008000; '>(C7:AG7)</span>=0,"",COUNTIF<span style=' color:008000; '>(C7:AG7,"fh")</span>)</td></tr><tr><td >AI8</td><td >=IF(COUNT<span style=' color:008000; '>(C8:AG8)</span>=0,"",COUNTIF<span style=' color:008000; '>(C8:AG8,"v")</span>)</td></tr><tr><td >AJ8</td><td >=IF(COUNTA<span style=' color:008000; '>(C8:AG8)</span>=0,"",COUNTIF<span style=' color:008000; '>(C8:AG8,"s")</span>)</td></tr><tr><td >AK8</td><td >=IF(COUNTA<span style=' color:008000; '>(C8:AG8)</span>=0,"",COUNTIF<span style=' color:008000; '>(C8:AG8,"fh")</span>)</td></tr><tr><td >AI9</td><td >=IF(COUNT<span style=' color:008000; '>(C9:AG9)</span>=0,"",COUNTIF<span style=' color:008000; '>(C9:AG9,"v")</span>)</td></tr><tr><td >AJ9</td><td >=IF(COUNTA<span style=' color:008000; '>(C9:AG9)</span>=0,"",COUNTIF<span style=' color:008000; '>(C9:AG9,"s")</span>)</td></tr><tr><td >AK9</td><td >=IF(COUNTA<span style=' color:008000; '>(C9:AG9)</span>=0,"",COUNTIF<span style=' color:008000; '>(C9:AG9,"fh")</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="Excel Jeanie Html

 

[Justinian]

That works but if I enter S or FH, it shows a zero. I am trying to have the formula run when a V is entered but leaving a blank cell when any other letter is entered. When I do my formulas from above, entering an S shows a 0 in the cell but I want the cell to remain blank unless a v is entered.

 

[bschwartz]

It shouldn't show a zero when there are no Vs. It should show the inverse of whatever is in cell AI21. However, if AI21 is empty, and there are no Vs it would show zero. To avoid this, try:

=IF($AI$21<>"",COUNTIF(C6:AG6,"v")*8-$AI$21,"")

 

[Justinian]

That kinda worked. When I enter S, it still shows a number. I use =IF(COUNTA(C6:AG6)=0,"" to get an empty cell if any other letter than S is entered. The point here is that I only want a cell to show a number is a V is entered. If an S is entered, I want the target cell (AI21) to remain blank and empty.

 

[bschwartz]

Justinian,

The thing that confuses me is that there can be many entries in the same row. You say if an S is entered, you want an empty cell, but if a V is entered, you want the formula to show. What if a row has both Vs and Ss?

 

[Justinian]

Great question. Column AI is the vacation column and houses this formula. If an S is entered, I will have a similar formula in the next column, column AJ, for sick time. Column AK will be floating holiday hours.

 

[bschwartz]

And is AI supposed to be the vacation time remaining? Because the formula seems to be backwards. Anyway, try this...

=IF(COUNTIF(C6:AG6,"v")=0,"",$AI$21-COUNTIF(C6:AG6,"v")*8)