Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a check box to accept a calculation | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
time-clock calculation | Excel Worksheet Functions |