If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
log book
Ok here's one that's been killing me This version works fine
B10 to 40 are numbered 1thru 31 C10 thru 40 represent the days of the month with C3 thru 9 representing the 7 previous days of last month. Hours on duty are entered into C3 - 40 for each day worked D9 - 40 are the accumulated hours for the past 7 days D9's formula is =SUM(C3,C4,C5,C6,,C7,C8,C9) this is only for D9 D 10 - 40 is =IF(AND(ISBLANK(C10)),"",(F10-C3)) the cell references progress down to D40 E9 is the number of hours available for the next day ( limited to 70 and below) E9=70-D9 E10 - 40 =IF(AND(ISBLANK(C10)),"",(70-D10)) As I said it works perfectly. The problem is they changed the rules on me!! Now when you take a consecutive 34 hours off you can reset your available hours back to 70. What I need is a way to do this in the sheet. I think I need to divide the cell values by 24 or use a count or count if, but alas I'm not on that level yet. Can anyone help me out please. I am also just starting with Macros so if one is involved please be explicit Answer in group or e-mail |
#2
|
|||
|
|||
log book
Joe,
I've found a solution, but it's not pretty. I turned your spreadsheet upside down, then I added two "helper" columns which will tell you when a "reset" occurs (someone takes 34 hours off). B3 to B33 numbered from 31 down to 1 C3 to C40 available to accept hours on duty for each day D3 to D40 displays the number of hours worked in the last 7 days, OR since the last "reset" (when 34 hours off occurred) E3 to E40 the number of hors available to be on duty for the next day Assuming people can be on duty for 24 hours in a day, then their clock "resets" when they don't work for a whole day AND they didn't work more than 14 hours for the previous day. Therefore, if you make column G a reset indicator: G3=IF(AND(C3=0,C4=14),"R","") copied down to G40 Now, Add a second helper column, column H, which will tell you how many days ago a reset occured, if it occurred within the last 7 days. H3=MATCH("R",G3:G10,0) copied down to H40 Next, create column D (hours so far). If column C is blank, enter "" otherwise, calculate the answer. To calculate the answer, you will see if there was a reset in the last 7 days. If there wasn't, you'll have a #N/A value in column G. So if there was no reset, just sum the last 7 days, otherwise, sum the last H days (actually, row H counted today as the first day, so we subtract 1 from the value in H). D3=IF(ISBLANK(C3),"",IF(ISERROR(H3),SUM(C3:C9),SUM (OFFSET(C3,0,0,H3-1)))) and copy down to D33. Change the formula slightly D34=IF(ISBLANK(C34),"",IF(ISERROR(H34),SUM(C34:C$4 0),SUM(OFFSET(C34,0,0,H34- 1)))) and copy down to D40 Finally, create column E (hours available for tomorrow). This is simple, now, because column D is a running total of the last 7 days or since the last reset, so simply subtract that from 70. E3=IF(ISBLANK(C3),"",70-D3) and copy down to E40 Feel free to hide columns G and H, as they are ugly. Trevor PS - Note that your spreadsheet deals with whole days. This can cause a problem if someone is on duty for 14 hours from 10:00am to midnight on Monday, but does not work at all on Tuesday. As far as the spreadsheet is concerned, they will have one empty day and the prior day will be 14 hours, which does match the test that within those 2 days, there were 34 hours during which they were not on duty. So the spreadsheet resets their clock. When in reality, the person was not off duty for a 34-hour block. You'll need to change to tracking by the hour, instead of the day, if you want to be accurate. "Joe" wrote in message ... Ok here's one that's been killing me This version works fine B10 to 40 are numbered 1thru 31 C10 thru 40 represent the days of the month with C3 thru 9 representing the 7 previous days of last month. Hours on duty are entered into C3 - 40 for each day worked D9 - 40 are the accumulated hours for the past 7 days D9's formula is =SUM(C3,C4,C5,C6,,C7,C8,C9) this is only for D9 D 10 - 40 is =IF(AND(ISBLANK(C10)),"",(F10-C3)) the cell references progress down to D40 E9 is the number of hours available for the next day ( limited to 70 and below) E9=70-D9 E10 - 40 =IF(AND(ISBLANK(C10)),"",(70-D10)) As I said it works perfectly. The problem is they changed the rules on me!! Now when you take a consecutive 34 hours off you can reset your available hours back to 70. What I need is a way to do this in the sheet. I think I need to divide the cell values by 24 or use a count or count if, but alas I'm not on that level yet. Can anyone help me out please. I am also just starting with Macros so if one is involved please be explicit Answer in group or e-mail |
#3
|
|||
|
|||
log book
Joe,
The MATCH command looks at a range of cells, and I don't know how to get Excel to stop from automatically putting a range reference into the format G3:G10, even though you type G10:G3. And I want it to find the most recent reset. And for some reason, Excel didn't like the syntax of MATCH("R",{G10;G9;G8;G7;G6;G5;G4;G3},0) which is what I really wanted to do. So with the spraedsheet the way you had it, I could use the MATCH command but change the third parameter to give me the last occurance of a reset (the most recent one). However, the problem with that is that if no reset occured within the last 7 days, that version of the MATCH command still returns the number 7. Which incorrectly indicates that a reset occurd on the 7th day. So, rather than fixing the whole problem, I just found it easier to turn within the last 7 days, but then MATCH will return 7 when no reset at all occured in the Trevor "Joe" wrote in message ... Thanx Trevor, Ill get back and let you know how it worked BTW what was the reason for turning it upside down? "Trevor" wrote in message ... Joe, I've found a solution, but it's not pretty. I turned your spreadsheet upside down, then I added two "helper" columns which will tell you when a "reset" occurs (someone takes 34 hours off). B3 to B33 numbered from 31 down to 1 C3 to C40 available to accept hours on duty for each day D3 to D40 displays the number of hours worked in the last 7 days, OR since the last "reset" (when 34 hours off occurred) E3 to E40 the number of hors available to be on duty for the next day Assuming people can be on duty for 24 hours in a day, then their clock "resets" when they don't work for a whole day AND they didn't work more than 14 hours for the previous day. Therefore, if you make column G a reset indicator: G3=IF(AND(C3=0,C4=14),"R","") copied down to G40 Now, Add a second helper column, column H, which will tell you how many days ago a reset occured, if it occurred within the last 7 days. H3=MATCH("R",G3:G10,0) copied down to H40 Next, create column D (hours so far). If column C is blank, enter "" otherwise, calculate the answer. To calculate the answer, you will see if there was a reset in the last 7 days. If there wasn't, you'll have a #N/A value in column G. So if there was no reset, just sum the last 7 days, otherwise, sum the last H days (actually, row H counted today as the first day, so we subtract 1 from the value in H). D3=IF(ISBLANK(C3),"",IF(ISERROR(H3),SUM(C3:C9),SUM (OFFSET(C3,0,0,H3-1)))) and copy down to D33. Change the formula slightly D34=IF(ISBLANK(C34),"",IF(ISERROR(H34),SUM(C34:C$4 0),SUM(OFFSET(C34,0,0,H34- 1)))) and copy down to D40 Finally, create column E (hours available for tomorrow). This is simple, now, because column D is a running total of the last 7 days or since the last reset, so simply subtract that from 70. E3=IF(ISBLANK(C3),"",70-D3) and copy down to E40 Feel free to hide columns G and H, as they are ugly. Trevor PS - Note that your spreadsheet deals with whole days. This can cause a problem if someone is on duty for 14 hours from 10:00am to midnight on Monday, but does not work at all on Tuesday. As far as the spreadsheet is concerned, they will have one empty day and the prior day will be 14 hours, which does match the test that within those 2 days, there were 34 hours during which they were not on duty. So the spreadsheet resets their clock. When in reality, the person was not off duty for a 34-hour block. You'll need to change to tracking by the hour, instead of the day, if you want to be accurate. "Joe" wrote in message ... Ok here's one that's been killing me This version works fine B10 to 40 are numbered 1thru 31 C10 thru 40 represent the days of the month with C3 thru 9 representing the 7 previous days of last month. Hours on duty are entered into C3 - 40 for each day worked D9 - 40 are the accumulated hours for the past 7 days D9's formula is =SUM(C3,C4,C5,C6,,C7,C8,C9) this is only for D9 D 10 - 40 is =IF(AND(ISBLANK(C10)),"",(F10-C3)) the cell references progress down to D40 E9 is the number of hours available for the next day ( limited to 70 and below) E9=70-D9 E10 - 40 =IF(AND(ISBLANK(C10)),"",(70-D10)) As I said it works perfectly. The problem is they changed the rules on me!! Now when you take a consecutive 34 hours off you can reset your available hours back to 70. What I need is a way to do this in the sheet. I think I need to divide the cell values by 24 or use a count or count if, but alas I'm not on that level yet. Can anyone help me out please. I am also just starting with Macros so if one is involved please be explicit Answer in group or e-mail |
Thread Tools | |
Display Modes | |
|
|