Calculation in a Row
 

Is there a calculation (function) that will look in a row and return the
number of continuous dates in number format?

Sample:

Name Date of Absense In A Row
Doe, John 12/17/04 4
Doe, John 12/18/04 4
Doe, John 12/19/04 4
Doe, John 12/20/04 4
Doe, John 12/24/04 1
Doe, John 12/27/04 1

The number in the "In A Row" column should calculate off the "Date of
Absense" column as it looks down the row and sees the date change.

Any suggestion would be helpful.

Thanks!

Hi!

Here's one way but requires a helper column in addition to the column "In a
Row" Also requires an empty row immediately above the data.

Dates in column B. B1 is the header "Date of Absense" Row 2 is empty and the
actual dates are in the range B3:Bn

Helper column will be column C. "In a Row" will be column D.

Enter this formula in C3:

=IF(B3=B2+1,B2+1,1)

Enter this formula in D3:

=IF(B3+1=B4,D4,C3)

Select both C3 and D3 then copy down as needed.

Biff

"kingpenguin58" wrote in message
...
Is there a calculation (function) that will look in a row and return the
number of continuous dates in number format?

Sample:

Name Date of Absense In A Row
Doe, John 12/17/04 4
Doe, John 12/18/04 4
Doe, John 12/19/04 4
Doe, John 12/20/04 4
Doe, John 12/24/04 1
Doe, John 12/27/04 1

The number in the "In A Row" column should calculate off the "Date of
Absense" column as it looks down the row and sees the date change.

Any suggestion would be helpful.

Thanks!

Assuming if someone is off on a Friday and a Monday, you want that to count
as 2 consecutive days.

Try this:
=IF(WORKDAY(B2,-1)=B1,C1+1,1)

(If the WORKDAY function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.)

Otherwise:
=IF(B2-B11,1,C1+1)

Does that help?

--
Regards,
Ron

Biff,

I did not get the number of days. The calculation returned the date number.
Is there a way to change it from date number to number of days?

Name DateofAbsense Helper InaRow

56810 2/26/2004 1 38046 should be 4
56810 2/27/2004 38044 38046 should be 4
56810 2/28/2004 38045 38046 should be 4
56810 2/29/2004 38046 38046 should be 4
56810 10/17/2004 1 1
56810 10/21/2004 1 1
56810 12/16/2004 1 1
11015 9/29/2004 1 1
11015 10/27/2004 1 1
11015 11/10/2004 1 1
11015 11/24/2004 1 1
11017 10/6/2004 1 1
52562 1/1/2004 1 1
52562 6/24/2004 1 1
52562 7/22/2004 1 1
52562 9/30/2004 1 1
60400 2/11/2004 1 38029 should be 2
60400 2/12/2004 38029 38029 should be 2
60400 3/31/2004 1 1
60400 4/21/2004 1 38100 should be 3
60400 4/22/2004 38099 38100 should be 3
60400 4/23/2004 38100 38100 should be 3

Thanks,
Martha

"Biff" wrote:

Hi!

Here's one way but requires a helper column in addition to the column "In a
Row" Also requires an empty row immediately above the data.

Dates in column B. B1 is the header "Date of Absense" Row 2 is empty and the
actual dates are in the range B3:Bn

Helper column will be column C. "In a Row" will be column D.

Enter this formula in C3:

=IF(B3=B2+1,B2+1,1)

Enter this formula in D3:

=IF(B3+1=B4,D4,C3)

Select both C3 and D3 then copy down as needed.

Biff

"kingpenguin58" wrote in message
...
Is there a calculation (function) that will look in a row and return the
number of continuous dates in number format?

Sample:

Name Date of Absense In A Row
Doe, John 12/17/04 4
Doe, John 12/18/04 4
Doe, John 12/19/04 4
Doe, John 12/20/04 4
Doe, John 12/24/04 1
Doe, John 12/27/04 1

The number in the "In A Row" column should calculate off the "Date of
Absense" column as it looks down the row and sees the date change.

Any suggestion would be helpful.

Thanks!

This is very helpful.

Thank you,
Martha

"kingpenguin58" wrote:

Is there a calculation (function) that will look in a row and return the
number of continuous dates in number format?

Sample:

Name Date of Absense In A Row
Doe, John 12/17/04 4
Doe, John 12/18/04 4
Doe, John 12/19/04 4
Doe, John 12/20/04 4
Doe, John 12/24/04 1
Doe, John 12/27/04 1

The number in the "In A Row" column should calculate off the "Date of
Absense" column as it looks down the row and sees the date change.

Any suggestion would be helpful.

Thanks!

Hi!

Ooops!

Slight correction in the first formula:

=IF(B3=B2+1,B2+1,1)

Should be:

=IF(B3=B2+1,C2+1,1)

Biff

"kingpenguin58" wrote in message
...
Biff,

I did not get the number of days. The calculation returned the date
number.
Is there a way to change it from date number to number of days?

Name DateofAbsense Helper InaRow

56810 2/26/2004 1 38046 should be 4
56810 2/27/2004 38044 38046 should be 4
56810 2/28/2004 38045 38046 should be 4
56810 2/29/2004 38046 38046 should be 4
56810 10/17/2004 1 1
56810 10/21/2004 1 1
56810 12/16/2004 1 1
11015 9/29/2004 1 1
11015 10/27/2004 1 1
11015 11/10/2004 1 1
11015 11/24/2004 1 1
11017 10/6/2004 1 1
52562 1/1/2004 1 1
52562 6/24/2004 1 1
52562 7/22/2004 1 1
52562 9/30/2004 1 1
60400 2/11/2004 1 38029 should be 2
60400 2/12/2004 38029 38029 should be 2
60400 3/31/2004 1 1
60400 4/21/2004 1 38100 should be 3
60400 4/22/2004 38099 38100 should be 3
60400 4/23/2004 38100 38100 should be 3

Thanks,
Martha

"Biff" wrote:

Hi!

Here's one way but requires a helper column in addition to the column "In
a
Row" Also requires an empty row immediately above the data.

Dates in column B. B1 is the header "Date of Absense" Row 2 is empty and
the
actual dates are in the range B3:Bn

Helper column will be column C. "In a Row" will be column D.

Enter this formula in C3:

=IF(B3=B2+1,B2+1,1)

Enter this formula in D3:

=IF(B3+1=B4,D4,C3)

Select both C3 and D3 then copy down as needed.

Biff

"kingpenguin58" wrote in
message
...
Is there a calculation (function) that will look in a row and return
the
number of continuous dates in number format?

Sample:

Name Date of Absense In A Row
Doe, John 12/17/04 4
Doe, John 12/18/04 4
Doe, John 12/19/04 4
Doe, John 12/20/04 4
Doe, John 12/24/04 1
Doe, John 12/27/04 1

The number in the "In A Row" column should calculate off the "Date of
Absense" column as it looks down the row and sees the date change.

Any suggestion would be helpful.

Thanks!