Yearly Attendance Record - Formula Help!

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,551
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Well your first questions is relatively easy. Your second question doesn't seem like a typical Excel function, since you want to "reset" inputted values when a certain criteria is hit. Maybe a macro can do this?

Regarding the conditional formatting, can you give an example from the above, where you might enter a slash, and which cells should be highlighted when that happens?
 
Upvote 0
Sorry, I left out one important fact. When an employee hits their anniversary, they don't reset their benefits package; they add the new package to their current running total. So if Employee #1 has 100 hours vacation and 30 hours sick when they hit their anniversary, their new package hours add on to their existing 100 and 30 hours.
 
Upvote 0
If the employee was late on 10/15, I have to enter a slash in the cell containing the 10/15 date. When the slash is entered, I need a row or cell somewhere to automatically highlight, telling the user that they need to enter why they employee was late.
 
Upvote 0
I don't quite understand. If you enter a slash where there is a date, wouldn't that override (remove) the date?
 
Upvote 0
No, the cells are blank unless an employee is sick, on vacation, or gets a slash. If none of thsoe things apply, the employee's sheet remains blank.
 
Upvote 0
Try this:

1) Select cell A1.
2) Use Ctrl+A on the keyboard to highlight all cells.
3) Go to conditional formatting --> New Rule --> Use Formula
4) Enter this formula: =A1="/"
5) Select format --> Fill --> Select Fill color --> Hit OK.
6) Hit OK again.

Does that do what you want?
 
Upvote 0
Sorry, but that did not work. That will highlight the table. WHat I was trying to do is have a cell off the grid highlight when a cell inside the grid shows a /.
 
Upvote 0
Sorry, but that did not work. That will highlight the table. WHat I was trying to do is have a cell off the grid highlight when a cell inside the grid shows a /.

So let me see if I have this right. If ANY cells inside a specific range of cells contains a forward slash, you want another cell to highlight? Which range should we look for slashes in?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top