Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with functions!!!
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 :) |
#3
|
|||
|
|||
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" wrote: 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) "Karen D" <Karen wrote in message ... 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 :) |
#4
|
|||
|
|||
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) "Karen D" wrote in message ... 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" wrote: 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) "Karen D" <Karen wrote in message ... 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 :) |
#5
|
|||
|
|||
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) "Bob Phillips" wrote in message ... 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) "Karen D" wrote in message ... 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" wrote: 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) "Karen D" <Karen wrote in message ... 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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
# of Functions per cell | Excel Worksheet Functions | |||
PivotTable canned functions | Excel Discussion (Misc queries) | |||
How to load Engineering Functions into the Fx function wizard? | Excel Worksheet Functions | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions |