Calculation in a Row

[Guest]

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!

 

[Biff]

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

 

[Guest]

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-B1>1,1,C1+1)

Does that help?

 

[Guest]

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

 

[Guest]

This is very helpful.

Thank you,
Martha

 

[Biff]

Hi!

Ooops!

Slight correction in the first formula:

Should be:

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

Biff