SUM based on Date < TODAY()

[mick.walker]

Hi All,

I need some help on a worksheet I am creating, basically it is a simple
sheet to keep track of employees holidays.

I simply have a row at the top containing a list of dates, (individual
dates) until the end of the current year.

Then I have a column containing the employees name, and under each day,
if the employee has booked holidays for that day, then I simply mark it
with either a 1 or a 0.5 representing full or half days.

What I want to do is create a cell which calculates the number of days
the employee has already taken. (i.e Previously) How can I do a sum of
the row, which includes the holiday information, but only upto TODAY()?

If I am not clear I appologise, if I am i look forward to your
suggestions.

Kind Regards
Mick Walker

 

[bplumhoff]

Hi Mick,

If you need an example:
http://www.sulprobil.com/html/vacation_plan.html

HTH,
Bernd

 

[Toppers]

Assuming dates in row 1 and holidays in row 2 (onwards):

=SUMPRODUCT(--($A$1:$A$F1<TODAY()),(A2:AF2))

Copy down as required

 

[Maistrye]

Hi All,

I need some help on a worksheet I am creating, basically it is a
simple
sheet to keep track of employees holidays.

I simply have a row at the top containing a list of dates, (individual
dates) until the end of the current year.

Then I have a column containing the employees name, and under each
day,
if the employee has booked holidays for that day, then I simply mark
it
with either a 1 or a 0.5 representing full or half days.

What I want to do is create a cell which calculates the number of days
the employee has already taken. (i.e Previously) How can I do a sum of
the row, which includes the holiday information, but only upto
TODAY()?

If I am not clear I appologise, if I am i look forward to your
suggestions.

Kind Regards
Mick Walker

You indicated your date info is in the first row

=SUMPRODUCT(--($A$1:$Z$1<TODAY()),A2:Z2)

If you want to include today, change the "<" to "<="

Scott