A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating a rolling calendar year



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2004, 05:32 PM
Dana
external usenet poster
 
Posts: n/a
Default 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  
Old March 4th, 2004, 10:40 PM
external usenet poster
 
Posts: n/a
Default 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  
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?
.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.