I need to track days/hours absent from work using one column for dates and a
second column for total hours absent. Each absence is considered an
occurrence, but consecutive days of absence are only considered to be a
single occurrence. In other words, if an employee is absent on Monday, it is
one occurrence, if s/he is out Monday and Tuesday it is one occurrence, but
if s/he is absent Monday, comes to work on Tuesday and is out again on
Wednesday, each absence would be considered a single occurrence for a total
of two that week. Is there a formula that will check the cells in the hours
column, and if they are not empty, check the dates in the date column to
determine if they are consecutive dates, thus determining the number of
occurrences? Thanks in advance€¦

Joe,

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Note the one row offset of the two ranges used...

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I need to track days/hours absent from work using one column for dates and a
second column for total hours absent. Each absence is considered an
occurrence, but consecutive days of absence are only considered to be a
single occurrence. In other words, if an employee is absent on Monday, it is
one occurrence, if s/he is out Monday and Tuesday it is one occurrence, but
if s/he is absent Monday, comes to work on Tuesday and is out again on
Wednesday, each absence would be considered a single occurrence for a total
of two that week. Is there a formula that will check the cells in the hours
column, and if they are not empty, check the dates in the date column to
determine if they are consecutive dates, thus determining the number of
occurrences? Thanks in advance.

I appreciate the effort, Bernie, but when I tested the formula as follows it
didn't work.

I first entered two consecutive dates (12/12/2006 & 12/13/2006) on two rows,
but the value remained at zero when it should have changed to 1. Then I
skipped two rows and entered a new date (12/17/2006). That caused the value
to change to 1, but then I skipped a date and entered 12/19/2006 on the next
row. No change. Then I skipped two rows, entered 12/22/2006 and the value
changed to 2. I entered 12/27/2006 under that and nothing happened. Skipped
a row and entered 12/29/2006 and the value changed to 3. Of the seven dates
entered only two of them are consectutive,which should result in six
occurrences. If I take out the blank rows the value changes to zero.

"Bernie Deitrick" wrote:

Joe,

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Note the one row offset of the two ranges used...

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I need to track days/hours absent from work using one column for dates and a
second column for total hours absent. Each absence is considered an
occurrence, but consecutive days of absence are only considered to be a
single occurrence. In other words, if an employee is absent on Monday, it is
one occurrence, if s/he is out Monday and Tuesday it is one occurrence, but
if s/he is absent Monday, comes to work on Tuesday and is out again on
Wednesday, each absence would be considered a single occurrence for a total
of two that week. Is there a formula that will check the cells in the hours
column, and if they are not empty, check the dates in the date column to
determine if they are consecutive dates, thus determining the number of
occurrences? Thanks in advance.

Joe,

It worked for me, obviuosly. It wasn't going on dates, just consecutive rows being filled, assuming
that you had preset dates.

But what I didn't note was that the first cell with an entry should be the first cell of the first
range: B2 in this example, and B1 needs to remain empty.

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Also, your blank cells need to be truly blank - no spaces, which some people use to blank a cell,
but which Excel treats as filled.

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I appreciate the effort, Bernie, but when I tested the formula as follows it
didn't work.

I first entered two consecutive dates (12/12/2006 & 12/13/2006) on two rows,
but the value remained at zero when it should have changed to 1. Then I
skipped two rows and entered a new date (12/17/2006). That caused the value
to change to 1, but then I skipped a date and entered 12/19/2006 on the next
row. No change. Then I skipped two rows, entered 12/22/2006 and the value
changed to 2. I entered 12/27/2006 under that and nothing happened. Skipped
a row and entered 12/29/2006 and the value changed to 3. Of the seven dates
entered only two of them are consectutive,which should result in six
occurrences. If I take out the blank rows the value changes to zero.

"Bernie Deitrick" wrote:

Joe,

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Note the one row offset of the two ranges used...

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I need to track days/hours absent from work using one column for dates and a
second column for total hours absent. Each absence is considered an
occurrence, but consecutive days of absence are only considered to be a
single occurrence. In other words, if an employee is absent on Monday, it is
one occurrence, if s/he is out Monday and Tuesday it is one occurrence, but
if s/he is absent Monday, comes to work on Tuesday and is out again on
Wednesday, each absence would be considered a single occurrence for a total
of two that week. Is there a formula that will check the cells in the hours
column, and if they are not empty, check the dates in the date column to
determine if they are consecutive dates, thus determining the number of
occurrences? Thanks in advance.

Thing is, I don't have preset dates. I'll only be entering the date when an
absence occurs, which means each row in the date column will be filled in on
an as needed basis.

"Bernie Deitrick" wrote:

Joe,

It worked for me, obviuosly. It wasn't going on dates, just consecutive rows being filled, assuming
that you had preset dates.

But what I didn't note was that the first cell with an entry should be the first cell of the first
range: B2 in this example, and B1 needs to remain empty.

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Also, your blank cells need to be truly blank - no spaces, which some people use to blank a cell,
but which Excel treats as filled.

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I appreciate the effort, Bernie, but when I tested the formula as follows it
didn't work.

I first entered two consecutive dates (12/12/2006 & 12/13/2006) on two rows,
but the value remained at zero when it should have changed to 1. Then I
skipped two rows and entered a new date (12/17/2006). That caused the value
to change to 1, but then I skipped a date and entered 12/19/2006 on the next
row. No change. Then I skipped two rows, entered 12/22/2006 and the value
changed to 2. I entered 12/27/2006 under that and nothing happened. Skipped
a row and entered 12/29/2006 and the value changed to 3. Of the seven dates
entered only two of them are consectutive,which should result in six
occurrences. If I take out the blank rows the value changes to zero.

"Bernie Deitrick" wrote:

Joe,

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Note the one row offset of the two ranges used...

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I need to track days/hours absent from work using one column for dates and a
second column for total hours absent. Each absence is considered an
occurrence, but consecutive days of absence are only considered to be a
single occurrence. In other words, if an employee is absent on Monday, it is
one occurrence, if s/he is out Monday and Tuesday it is one occurrence, but
if s/he is absent Monday, comes to work on Tuesday and is out again on
Wednesday, each absence would be considered a single occurrence for a total
of two that week. Is there a formula that will check the cells in the hours
column, and if they are not empty, check the dates in the date column to
determine if they are consecutive dates, thus determining the number of
occurrences? Thanks in advance.

Joe,

Then why did you write?

Is there a formula that will check the cells in the hours
column, and if they are not empty....


Anyway, if you have a column of dates, then with the first date in B4, and B3 empty:

=SUMPRODUCT((B4:B100<"")*(B4:B100<(B3:B99+1)))

Again, note the one row offset of the last range.

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
Thing is, I don't have preset dates. I'll only be entering the date when an
absence occurs, which means each row in the date column will be filled in on
an as needed basis.

"Bernie Deitrick" wrote:

Joe,

It worked for me, obviuosly. It wasn't going on dates, just consecutive rows being filled,
assuming
that you had preset dates.

But what I didn't note was that the first cell with an entry should be the first cell of the
first
range: B2 in this example, and B1 needs to remain empty.

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Also, your blank cells need to be truly blank - no spaces, which some people use to blank a cell,
but which Excel treats as filled.

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I appreciate the effort, Bernie, but when I tested the formula as follows it
didn't work.

I first entered two consecutive dates (12/12/2006 & 12/13/2006) on two rows,
but the value remained at zero when it should have changed to 1. Then I
skipped two rows and entered a new date (12/17/2006). That caused the value
to change to 1, but then I skipped a date and entered 12/19/2006 on the next
row. No change. Then I skipped two rows, entered 12/22/2006 and the value
changed to 2. I entered 12/27/2006 under that and nothing happened. Skipped
a row and entered 12/29/2006 and the value changed to 3. Of the seven dates
entered only two of them are consectutive,which should result in six
occurrences. If I take out the blank rows the value changes to zero.

"Bernie Deitrick" wrote:

Joe,

=SUMPRODUCT((B2:B16<"")*(B1:B15=""))

Note the one row offset of the two ranges used...

HTH,
Bernie
MS Excel MVP


"JoeFriday" wrote in message
...
I need to track days/hours absent from work using one column for dates and a
second column for total hours absent. Each absence is considered an
occurrence, but consecutive days of absence are only considered to be a
single occurrence. In other words, if an employee is absent on Monday, it is
one occurrence, if s/he is out Monday and Tuesday it is one occurrence, but
if s/he is absent Monday, comes to work on Tuesday and is out again on
Wednesday, each absence would be considered a single occurrence for a total
of two that week. Is there a formula that will check the cells in the hours
column, and if they are not empty, check the dates in the date column to
determine if they are consecutive dates, thus determining the number of
occurrences? Thanks in advance.