Help with functions!!!

[Guest]

Hi,

I am trying to develop a vacation time tracker which will accrue time
earned. This company accrues time based on anniversary date, not at the
beginning of the year. I have already set up columns for each pay period,
that calculate how much time should accrue for that time period only. Now,
what I want to do is, based on the cell that holds todays date, figure out
how far out to go on the columns and sum those amounts for just the columns
that fall in the correct time period.

For example,

Column M holds the accrual amount for each employer for the pay period
beginning on 1/1/05. I want a formula that compares that pay period date to
the current date, and if it is less than the current date, add the amount in
Column M, then go on to column N and do the same thing. The problem I'm
running into is that I can only go 7 levels on the if formula, but I have 24
columns to look at.

Can anyone help me out?

Thanks!
Karen

 

[Bob Phillips]

Hi Karen,

Not sure I've got your data structure, but how about

=SUM(M2:OFFSET(M2,,MONTH(TODAY())-1))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for the suggestion Bob, but I'm not sure I was clear enough.

In row 2 starting with column M, I have dates of 1/1/05, 1/15/05, 2/1/05,
2/16/05...12/16/05.

Then starting in row 4 of column M I have calculations of how many hours
should accrue for each employee (each employee has a row).

What I am trying to do is compare the current date to the dates in row 2,
and for each date that comes up less than or equal to the current date (which
is in cell K1), add the contents of cell m4 through whatever column the
formula determines is the highest date that is still less than or equal to
the current date.

For example, today is 4/18/05. The formula should figure out that the last
column that is less than 4/18/05 is column T. Then I want it to sum m4:t4
for that employee, m5:t5 for the next employee, etc. Does that make sense?
Am I hoping Excel can do more than it is capable of? It's ok if I need to
break it down into two steps... If I can return a column letter of the
highest column (in this case, T), and then have the suming formula pull from
that cell, that would be fine, or if there is a way to do it all together
that would be fine too.

This spreadsheet is for someone else's use, and they do not know Excel as
well as me... I need for them to just be able to copy and paste whole lines
if they need to add employees.

Thanks again!

 

[Bob Phillips]

I thought I didn't wuite understand .

Try this instead

=SUMPRODUCT(--(M$2:Z$2<TODAY()),M4:Z4)

Change the Z to you rightmost cell that it can possibly be

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

You said less than or equal to

=SUMPRODUCT(--(M$2:Z$2<=TODAY()),M4:Z4)


--

HTH

RP
(remove nothere from the email address if mailing direct)