ThanksAlot
New Member
- Joined
- Apr 29, 2013
- Messages
- 13
I am trying to build a formula that will register the number of consecutive days a given task is either completed or not completed. The most recent cell (C1) should show a positive or negative value. A positive value will be the number of consecutive days the task wasc ompleted. A negative value will be the number of consecutive days the task was not completed.
As an example, I want to track the consecutive number of days that Joe went to work and stayed on the job at least 7 hours.
Holidays and other off days are disregarded. Every day he does go to work, Joe can work any numbers of hours he wishes. The only calculations desired is the number of consecutive days that he did and did notwork at least 7 hours.
Working fewer than seven hours adds minus (-) 1 to the table. Working 7, 8, 9, 10, etc. adds +1 to the table.
Assuming that every day is a work day, if Joe worked Mon, Tue, Wed, Thurs, Fri, Sat, Sun, etc., and he worked 7 hours on Monday, cell C1 will = +1. Working 8 hours on Tuesday also = +1, and it is added to cell C1 for a running total of +2. Joe works 10 hours on Wed, so cell C1 = +3.<o></o>
Joe worked 1 hour on Thurs, and cell C1 now = minus (-)1. He worked 6 hours on Fri, and cell C1now = minus (-) 2. Joe worked 4 hours on Saturday, and so cell C1= minus (-) 3.
Joe worked 9 hours on Sunday, and cell C1 is back to+1, and so on.<o></o>
The data is copied and pasted into the table. Column A shows the running number of daysworked. Column B shows the total numberof hours worked year to date. <o></o>
___<o></o>
I don’t know if the following helps, or not.<o></o>
IF Today's Cell A1 (TA1) is greater thanYesterday's Cell A1 (YA1)…and…<o></o>
If Today's Cell B1 (TB1) is greater thanYesterday's Cell B1 (YB1)…where…<o></o>
Cell TB1 = YB1+ 1, or YB1 + 2, or YB1 + any no. greaterthan zero = YB1 + 1…and…
Yesterday's Cell B1 (YB1) = any negative number…then…
Today’s Cell C1 = +1<o></o>
Continuing…<o></o>
If Today's Cell A1 (TA1) is greater than Yesterday'sCell A1 (YA1)…and…
If Today's Cell B1 (TB1) is greater thanYesterday's Cell B1 (YB1)…where…
<o></o>
Cell TB1 = YB1+ 1, or YB1 + 2, or YB1 + any numbergreater than zero = YB1 + 1…and…
Yesterday's Cell B1 (YB1) = any positive number…then…
Then Today’s Cell C1 = YB1 + 1<o></o>
<o></o>
ThanksAlot, Jon<o></o>
As an example, I want to track the consecutive number of days that Joe went to work and stayed on the job at least 7 hours.
Holidays and other off days are disregarded. Every day he does go to work, Joe can work any numbers of hours he wishes. The only calculations desired is the number of consecutive days that he did and did notwork at least 7 hours.
Working fewer than seven hours adds minus (-) 1 to the table. Working 7, 8, 9, 10, etc. adds +1 to the table.
Assuming that every day is a work day, if Joe worked Mon, Tue, Wed, Thurs, Fri, Sat, Sun, etc., and he worked 7 hours on Monday, cell C1 will = +1. Working 8 hours on Tuesday also = +1, and it is added to cell C1 for a running total of +2. Joe works 10 hours on Wed, so cell C1 = +3.<o></o>
Joe worked 1 hour on Thurs, and cell C1 now = minus (-)1. He worked 6 hours on Fri, and cell C1now = minus (-) 2. Joe worked 4 hours on Saturday, and so cell C1= minus (-) 3.
Joe worked 9 hours on Sunday, and cell C1 is back to+1, and so on.<o></o>
The data is copied and pasted into the table. Column A shows the running number of daysworked. Column B shows the total numberof hours worked year to date. <o></o>
___<o></o>
I don’t know if the following helps, or not.<o></o>
IF Today's Cell A1 (TA1) is greater thanYesterday's Cell A1 (YA1)…and…<o></o>
If Today's Cell B1 (TB1) is greater thanYesterday's Cell B1 (YB1)…where…<o></o>
Cell TB1 = YB1+ 1, or YB1 + 2, or YB1 + any no. greaterthan zero = YB1 + 1…and…
Yesterday's Cell B1 (YB1) = any negative number…then…
Today’s Cell C1 = +1<o></o>
Continuing…<o></o>
If Today's Cell A1 (TA1) is greater than Yesterday'sCell A1 (YA1)…and…
If Today's Cell B1 (TB1) is greater thanYesterday's Cell B1 (YB1)…where…
<o></o>
Cell TB1 = YB1+ 1, or YB1 + 2, or YB1 + any numbergreater than zero = YB1 + 1…and…
Yesterday's Cell B1 (YB1) = any positive number…then…
Then Today’s Cell C1 = YB1 + 1<o></o>
<o></o>
ThanksAlot, Jon<o></o>
Last edited: