SUM based on Date < TODAY()
 

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

SUM based on Date < TODAY()
 

Hi Mick,

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

HTH,
Bernd

SUM based on Date < TODAY()
 

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

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

Copy down as required

" wrote:

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

SUM based on Date < TODAY()
 

Wrote:
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


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=562382