If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Calculating a rolling calendar year
I am trying to keep track of employee attendance (small
company - under 50 people) in Excel. I have created a workbook that has a sheet for each employee. In Column A, I have listed the months and in B-AF I have the numbers 1- 31. I am using the corresponding cells (month/day) to keep track of attendance using codes (V-Vacation, etc.). I have set-up the sheets to calculate a cummulative total for the year. The problem is that our attendance works on a rolling calendar year - for disciplinary purposes, we look from the current date back 90 days and count those attendance occurances. Is there a way to calculate this? |
#2
|
|||
|
|||
Calculating a rolling calendar year
Is there any way you can get by with recording data only
for those days on which the employee is not at work, instead of recording data for every work day (I assume that's what you're doing). -----Original Message----- I am trying to keep track of employee attendance (small company - under 50 people) in Excel. I have created a workbook that has a sheet for each employee. In Column A, I have listed the months and in B-AF I have the numbers 1- 31. I am using the corresponding cells (month/day) to keep track of attendance using codes (V-Vacation, etc.). I have set-up the sheets to calculate a cummulative total for the year. The problem is that our attendance works on a rolling calendar year - for disciplinary purposes, we look from the current date back 90 days and count those attendance occurances. Is there a way to calculate this? . |
#3
|
|||
|
|||
Calculating a rolling calendar year
I have an array formula that will work if all the dates
of the year are in a single column, and the corresponding codes are in the next colum to the right, or if the dates are in one row, and the codes are in the rwo beneath it. I cannot get the formula to work with the arrangement you seem to have in your spreadsheet. Here is the formula {=SUM(IF(TODAY()-dates=90,1,0)*(IF (codes="u",1,0)))}. You get the curly braces around the formula by pressing SHIFT-CONTROL-ENTER, instead of just the ENTER key. Pressing SHIFT-CONTROL-ENTER enters the formula as an array formula. I gave the column containing the date values the name of "dates" (without the quotes). I named the column containg the attendance codes as "codes." I used the code"u" to represent an unexcused absence. You would substitute the attendance code that you use. Here's how the code works. The first "If" statement creates an array (think of it as a list) of ones and zeroes in memory. A one is entered into the list for each date that is within the past 90 days, and a zero is entered for dates more than 90 days ago. The second "If " statement performs a similar function, entering a one for each cell that contains a "u", and a zero for cells containing some other value. Then the two arrays are multiplied by each other (first value in the first array times the first value in the second array, second value in first array times second value in the second array,etc.) to create a third array. Finally, the "SUM" functions adds up all the values in the third array, to get the total number of "unexcused absences. If this is confusing, I will try to explain more in another message. LEB -----Original Message----- Is there any way you can get by with recording data only for those days on which the employee is not at work, instead of recording data for every work day (I assume that's what you're doing). -----Original Message----- I am trying to keep track of employee attendance (small company - under 50 people) in Excel. I have created a workbook that has a sheet for each employee. In Column A, I have listed the months and in B-AF I have the numbers 1- 31. I am using the corresponding cells (month/day) to keep track of attendance using codes (V-Vacation, etc.). I have set-up the sheets to calculate a cummulative total for the year. The problem is that our attendance works on a rolling calendar year - for disciplinary purposes, we look from the current date back 90 days and count those attendance occurances. Is there a way to calculate this? . . |
Thread Tools | |
Display Modes | |
|
|