Need assitance w/ creating a running tally

[Ian]

Ive been asked at work to modify an existing worksheet that tracks the
personnel that are currently taking and scheduled to take a leave of
absence from the office. When a member is requesting days off, I need
to figure it into the spreadsheet to find out if the total is under
15% of total members on leave for the requested days.

My knowledge on excel is pretty good, but I really havent used macros
and indepth functions.

The basic information that will be used in the spreadsheet is the
members name, date departing and expected date of return

 

[Myrna Larson]

Am I correct in assuming that if a person requests two weeks off, you need to
get a tally for each day in that period, and if you are over the 15% limit on
any of those days, the leave is denied?

If the names are in column A, departing date in B, returning date in C, and
you want to allow for 40 leave entries, so the list occupies the range A2:C41.

You could set up a column on the right that lists all of the dates, in order,
for the period of interest, say 9/1/2004 through 12/31/2004. Let's say you put
that list of dates in H2:H123. In I2, put this formula

=SUMPRODUCT((H2>=$B$2:$B$41)*(H2<$C$2:$C$41))

and copy it down through I123. It will tell you how many people are on leave
on each day. You could use conditional formatting to change the color of the
cell if the total exceeds your maximum.

If you add a name at the bottom of the list, the counts for each day will be
updated.