View Single Post
  #3  
Old March 4th, 2004, 11:25 PM
external usenet poster
 
Posts: n/a
Default 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?
.

.