Seeking Formula help to count consecutive weekday absences

D

dms11463

New Member
Joined
May 1, 2023
Messages
13
Office Version
  1. 365
Platform
  1. Windows
test worksheet.xlsx
ABCDEFGHIJK
1Employee IDWorkerTypeTime Off DateApproved Hoursneed to count consecutive WEEKDAYS absent at each change in Employee ID - using the absence dates in Column H and using the Saturday/Sunday weekendsExpected ResponseNote - actual worksheet will have over 50,000 rows
2100339AaliaVacation4/20/202381
3100339AaliaDay of Service4/21/202382
4100339AaliaVacation4/24/202383
5100339AaliaSick4/27/202321
6100339AaliaVacation4/28/202382
710136AbbiSick1/27/202381
810136AbbiSick2/17/20236.071
910136AbbiSick2/22/202331
1010136AbbiSick2/23/202332
1110136AbbiSick3/1/20231.921
1210136AbbiSick3/10/202381
1310136AbbiVacation3/15/202381
1410136AbbiVacation3/24/202381
1510136AbbiVacation3/27/202382
1610136AbbiSick3/29/202321
1710136AbbiSick4/5/202361
1810136AbbiSick4/21/20231.51
1911767AbbyVacation1/6/20233.481
2011767AbbyVacation3/13/20237.731
2111767AbbyVacation3/13/20230.021
2213215AbbySick1/4/20232.51
2313215AbbySick1/5/202382
2413215AbbySick1/6/20237.253
2513215AbbySick1/25/202371
2613215AbbyVacation2/3/202341
2713215AbbySick2/10/20237.471
2813215AbbySick3/1/20231.71
2913215AbbySick3/13/202381
3013215AbbySick3/16/202311
3113215AbbySick3/17/20230.892
3213215AbbySick3/27/20237.191
3313215AbbySick4/3/202381
3413215AbbySick4/18/202311
3513215AbbySick4/19/202382
3613215AbbyVacation4/20/202383
3713215AbbyVacation4/21/202384
3813215AbbySick4/28/202311
3914064AbigailVacation1/3/202381
4014064AbigailVacation1/31/202381
4114064AbigailVacation2/1/202382
4214064AbigailVacation2/2/20234.53
4314064AbigailVacation2/23/202341
4414064AbigailVacation2/24/202382
4514064AbigailVacation3/13/202381
4614064AbigailVacation3/14/202382
4714064AbigailVacation3/15/202383
4814064AbigailVacation3/16/202384
4914064AbigailVacation3/17/202385
5014187AddieDay of Service2/17/202381
5114187AddieVacation3/27/202381
5214187AddieVacation3/28/202382
5314187AddieVacation3/29/202383
5414187AddieVacation3/30/202384
5514187AddieVacation3/31/202385
5613457AdrianVacation2/17/202381
5713457AdrianSick2/22/202351
5813457AdrianVacation4/3/202381
5913457AdrianVacation4/4/202382
6013457AdrianVacation4/5/202383
6113457AdrianVacation4/6/202384
6213457AdrianVacation4/7/202385
6312908AdrianaSick1/16/20236.11
6412908AdrianaSick1/17/202382
6512908AdrianaSick2/1/202371
6612908AdrianaSick4/7/202371
67100356AdrianaVacation2/17/202381
68100356AdrianaVacation2/21/20232.266671
69100356AdrianaVacation3/22/202371
70100356AdrianaVacation4/18/202381
71100356AdrianaSick4/21/202371
7212290AggiVacation1/3/202381
7312290AggiSick2/2/202381
7412290AggiDay of Service2/3/202382
7512290AggiSick2/28/20230.61
7612290AggiVacation3/13/202381
7712290AggiVacation3/14/202382
7812290AggiVacation3/15/202383
7912290AggiVacation3/16/202384
8012290AggiVacation3/17/202385
8110137AileenVacation1/3/202381
8210137AileenVacation1/4/202382
8310137AileenVacation1/5/202383
8410137AileenVacation1/6/202384
8510137AileenVacation1/9/202385
8610137AileenSick1/12/20231.7166661
8710137AileenDay of Service1/30/202381
8810137AileenVacation2/2/202381
8910137AileenVacation2/3/20235.6833332
9010137AileenSick2/14/20231.4999991
9110137AileenVacation2/15/20236.3166672
9210137AileenSick2/24/20233.1666661
9310137AileenSick2/28/20232.9333341
9410137AileenVacation4/7/202381
9510137AileenSick4/11/20233.5499991
96100362AimeeVacation2/13/202381
97100362AimeeVacation2/14/202382
98100362AimeeSick2/21/202311
99100362AimeeSick3/21/20231.31
100100362AimeeVacation4/4/20231.551
101100362AimeeSick4/17/202381
10212970AJSick1/25/202311
10312970AJDay of Service1/26/202382
10412970AJSick1/31/202321
10512970AJSick2/7/202311
10612970AJVacation3/8/202381
10712970AJVacation3/9/202382
10812970AJSick3/22/202311
10912970AJSick3/31/202321
11012970AJSick4/4/202311
11112970AJVacation4/5/202382
11212970AJVacation4/6/202383
Sheet1
 
Sort by date Sort by votes
try this:
If you want to consider holidays, you willneed to put in the holiday range in the optional 4th argument of the WORKDAY.INTL function.


mr excel questions 32.xlsm
ABCDEFGH
1Employee IDWorkerTypeTime Off DateApproved Hoursneed to count consecutive WEEKDAYS absent at each change in Employee ID - using the absence dates in Column H and using the Saturday/Sunday weekendsExpected Response
2100339AaliaVacation2023-04-20811
3100339AaliaDay of Service2023-04-21822
4100339AaliaVacation2023-04-24833
5100339AaliaSick2023-04-27211
6100339AaliaVacation2023-04-28822
710136AbbiSick2023-01-27811
810136AbbiSick2023-02-176.0711
910136AbbiSick2023-02-22311
1010136AbbiSick2023-02-23322
1110136AbbiSick2023-03-011.9211
1210136AbbiSick2023-03-10811
1310136AbbiVacation2023-03-15811
1410136AbbiVacation2023-03-24811
1510136AbbiVacation2023-03-27822
1610136AbbiSick2023-03-29211
1710136AbbiSick2023-04-05611
1810136AbbiSick2023-04-211.511
1911767AbbyVacation2023-01-063.4811
2011767AbbyVacation2023-03-137.7311
2111767AbbyVacation2023-03-130.0211
2213215AbbySick2023-01-042.511
2313215AbbySick2023-01-05822
2413215AbbySick2023-01-067.2533
2513215AbbySick2023-01-25711
2613215AbbyVacation2023-02-03411
2713215AbbySick2023-02-107.4711
2813215AbbySick2023-03-011.711
2913215AbbySick2023-03-13811
3013215AbbySick2023-03-16111
3113215AbbySick2023-03-170.8922
3213215AbbySick2023-03-277.1911
3313215AbbySick2023-04-03811
3413215AbbySick2023-04-18111
3513215AbbySick2023-04-19822
3613215AbbyVacation2023-04-20833
3713215AbbyVacation2023-04-21844
3813215AbbySick2023-04-28111
3914064AbigailVacation2023-01-03811
4014064AbigailVacation2023-01-31811
4114064AbigailVacation2023-02-01822
4214064AbigailVacation2023-02-024.533
4314064AbigailVacation2023-02-23411
4414064AbigailVacation2023-02-24822
4514064AbigailVacation2023-03-13811
4614064AbigailVacation2023-03-14822
4714064AbigailVacation2023-03-15833
4814064AbigailVacation2023-03-16844
4914064AbigailVacation2023-03-17855
5014187AddieDay of Service2023-02-17811
5114187AddieVacation2023-03-27811
5214187AddieVacation2023-03-28822
5314187AddieVacation2023-03-29833
5414187AddieVacation2023-03-30844
5514187AddieVacation2023-03-31855
5613457AdrianVacation2023-02-17811
5713457AdrianSick2023-02-22511
5813457AdrianVacation2023-04-03811
5913457AdrianVacation2023-04-04822
6013457AdrianVacation2023-04-05833
6113457AdrianVacation2023-04-06844
6213457AdrianVacation2023-04-07855
6312908AdrianaSick2023-01-166.111
6412908AdrianaSick2023-01-17822
6512908AdrianaSick2023-02-01711
6612908AdrianaSick2023-04-07711
67100356AdrianaVacation2023-02-17811
68100356AdrianaVacation2023-02-212.2666711
69100356AdrianaVacation2023-03-22711
70100356AdrianaVacation2023-04-18811
71100356AdrianaSick2023-04-21711
7212290AggiVacation2023-01-03811
7312290AggiSick2023-02-02811
7412290AggiDay of Service2023-02-03822
7512290AggiSick2023-02-280.611
7612290AggiVacation2023-03-13811
7712290AggiVacation2023-03-14822
7812290AggiVacation2023-03-15833
7912290AggiVacation2023-03-16844
8012290AggiVacation2023-03-17855
8110137AileenVacation2023-01-03811
8210137AileenVacation2023-01-04822
8310137AileenVacation2023-01-05833
8410137AileenVacation2023-01-06844
8510137AileenVacation2023-01-09855
8610137AileenSick2023-01-121.71666611
8710137AileenDay of Service2023-01-30811
8810137AileenVacation2023-02-02811
8910137AileenVacation2023-02-035.68333322
9010137AileenSick2023-02-141.49999911
9110137AileenVacation2023-02-156.31666722
9210137AileenSick2023-02-243.16666611
9310137AileenSick2023-02-282.93333411
9410137AileenVacation2023-04-07811
9510137AileenSick2023-04-113.54999911
96100362AimeeVacation2023-02-13811
97100362AimeeVacation2023-02-14822
98100362AimeeSick2023-02-21111
99100362AimeeSick2023-03-211.311
100100362AimeeVacation2023-04-041.5511
101100362AimeeSick2023-04-17811
10212970AJSick2023-01-25111
10312970AJDay of Service2023-01-26822
10412970AJSick2023-01-31211
10512970AJSick2023-02-07111
10612970AJVacation2023-03-08811
10712970AJVacation2023-03-09822
10812970AJSick2023-03-22111
10912970AJSick2023-03-31211
11012970AJSick2023-04-04111
11112970AJVacation2023-04-05822
11212970AJVacation2023-04-06833
dms11463
Cell Formulas
RangeFormula
G2:G112G2=IF(AND(B2=B1,WORKDAY.INTL(D2,-1,1)=D1),N(G1)+1,1)
 
Last edited:
Upvote 1

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
awoohaw said:
try this:
If you want to consider holidays, you willneed to put in the holiday range in the optional 4th argument of the WORKDAY.INTL function.


mr excel questions 32.xlsm
ABCDEFGH
1Employee IDWorkerTypeTime Off DateApproved Hoursneed to count consecutive WEEKDAYS absent at each change in Employee ID - using the absence dates in Column H and using the Saturday/Sunday weekendsExpected Response
2100339AaliaVacation2023-04-20811
3100339AaliaDay of Service2023-04-21822
4100339AaliaVacation2023-04-24833
5100339AaliaSick2023-04-27211
6100339AaliaVacation2023-04-28822
710136AbbiSick2023-01-27811
810136AbbiSick2023-02-176.0711
910136AbbiSick2023-02-22311
1010136AbbiSick2023-02-23322
1110136AbbiSick2023-03-011.9211
1210136AbbiSick2023-03-10811
1310136AbbiVacation2023-03-15811
1410136AbbiVacation2023-03-24811
1510136AbbiVacation2023-03-27822
1610136AbbiSick2023-03-29211
1710136AbbiSick2023-04-05611
1810136AbbiSick2023-04-211.511
1911767AbbyVacation2023-01-063.4811
2011767AbbyVacation2023-03-137.7311
2111767AbbyVacation2023-03-130.0211
2213215AbbySick2023-01-042.511
2313215AbbySick2023-01-05822
2413215AbbySick2023-01-067.2533
2513215AbbySick2023-01-25711
2613215AbbyVacation2023-02-03411
2713215AbbySick2023-02-107.4711
2813215AbbySick2023-03-011.711
2913215AbbySick2023-03-13811
3013215AbbySick2023-03-16111
3113215AbbySick2023-03-170.8922
3213215AbbySick2023-03-277.1911
3313215AbbySick2023-04-03811
3413215AbbySick2023-04-18111
3513215AbbySick2023-04-19822
3613215AbbyVacation2023-04-20833
3713215AbbyVacation2023-04-21844
3813215AbbySick2023-04-28111
3914064AbigailVacation2023-01-03811
4014064AbigailVacation2023-01-31811
4114064AbigailVacation2023-02-01822
4214064AbigailVacation2023-02-024.533
4314064AbigailVacation2023-02-23411
4414064AbigailVacation2023-02-24822
4514064AbigailVacation2023-03-13811
4614064AbigailVacation2023-03-14822
4714064AbigailVacation2023-03-15833
4814064AbigailVacation2023-03-16844
4914064AbigailVacation2023-03-17855
5014187AddieDay of Service2023-02-17811
5114187AddieVacation2023-03-27811
5214187AddieVacation2023-03-28822
5314187AddieVacation2023-03-29833
5414187AddieVacation2023-03-30844
5514187AddieVacation2023-03-31855
5613457AdrianVacation2023-02-17811
5713457AdrianSick2023-02-22511
5813457AdrianVacation2023-04-03811
5913457AdrianVacation2023-04-04822
6013457AdrianVacation2023-04-05833
6113457AdrianVacation2023-04-06844
6213457AdrianVacation2023-04-07855
6312908AdrianaSick2023-01-166.111
6412908AdrianaSick2023-01-17822
6512908AdrianaSick2023-02-01711
6612908AdrianaSick2023-04-07711
67100356AdrianaVacation2023-02-17811
68100356AdrianaVacation2023-02-212.2666711
69100356AdrianaVacation2023-03-22711
70100356AdrianaVacation2023-04-18811
71100356AdrianaSick2023-04-21711
7212290AggiVacation2023-01-03811
7312290AggiSick2023-02-02811
7412290AggiDay of Service2023-02-03822
7512290AggiSick2023-02-280.611
7612290AggiVacation2023-03-13811
7712290AggiVacation2023-03-14822
7812290AggiVacation2023-03-15833
7912290AggiVacation2023-03-16844
8012290AggiVacation2023-03-17855
8110137AileenVacation2023-01-03811
8210137AileenVacation2023-01-04822
8310137AileenVacation2023-01-05833
8410137AileenVacation2023-01-06844
8510137AileenVacation2023-01-09855
8610137AileenSick2023-01-121.71666611
8710137AileenDay of Service2023-01-30811
8810137AileenVacation2023-02-02811
8910137AileenVacation2023-02-035.68333322
9010137AileenSick2023-02-141.49999911
9110137AileenVacation2023-02-156.31666722
9210137AileenSick2023-02-243.16666611
9310137AileenSick2023-02-282.93333411
9410137AileenVacation2023-04-07811
9510137AileenSick2023-04-113.54999911
96100362AimeeVacation2023-02-13811
97100362AimeeVacation2023-02-14822
98100362AimeeSick2023-02-21111
99100362AimeeSick2023-03-211.311
100100362AimeeVacation2023-04-041.5511
101100362AimeeSick2023-04-17811
10212970AJSick2023-01-25111
10312970AJDay of Service2023-01-26822
10412970AJSick2023-01-31211
10512970AJSick2023-02-07111
10612970AJVacation2023-03-08811
10712970AJVacation2023-03-09822
10812970AJSick2023-03-22111
10912970AJSick2023-03-31211
11012970AJSick2023-04-04111
11112970AJVacation2023-04-05822
11212970AJVacation2023-04-06833
dms11463
Cell Formulas
RangeFormula
G2:G112G2=IF(AND(B2=B1,WORKDAY.INTL(D2,-1,1)=D1),N(G1)+1,1)
Click to expand...
I believe this WORKS! I was able to recreate this with my full data (many additional columns - so I adjusted the formula accordingly) and in my 11,000 line test file - most of the results were as expected. I just need to work on the file to remove absence corrections.
 
Upvote 0
I'
dms11463 said:
I believe this WORKS! I was able to recreate this with my full data (many additional columns - so I adjusted the formula accordingly) and in my 11,000 line test file - most of the results were as expected. I just need to work on the file to remove absence corrections.
Click to expand...
I'm pleased you found an answer here in the forum! And welcome.

Best wishes!
 
Upvote 0
dms11463 said:
I believe this WORKS! I was able to recreate this with my full data (many additional columns - so I adjusted the formula accordingly) and in my 11,000 line test file - most of the results were as expected. I just need to work on the file to remove absence corrections.
Click to expand...
I'm not sure I understand the holiday -
awoohaw said:
try this:
If you want to consider holidays, you willneed to put in the holiday range in the optional 4th argument of the WORKDAY.INTL function.


mr excel questions 32.xlsm
ABCDEFGH
1Employee IDWorkerTypeTime Off DateApproved Hoursneed to count consecutive WEEKDAYS absent at each change in Employee ID - using the absence dates in Column H and using the Saturday/Sunday weekendsExpected Response
2100339AaliaVacation2023-04-20811
3100339AaliaDay of Service2023-04-21822
4100339AaliaVacation2023-04-24833
5100339AaliaSick2023-04-27211
6100339AaliaVacation2023-04-28822
710136AbbiSick2023-01-27811
810136AbbiSick2023-02-176.0711
910136AbbiSick2023-02-22311
1010136AbbiSick2023-02-23322
1110136AbbiSick2023-03-011.9211
1210136AbbiSick2023-03-10811
1310136AbbiVacation2023-03-15811
1410136AbbiVacation2023-03-24811
1510136AbbiVacation2023-03-27822
1610136AbbiSick2023-03-29211
1710136AbbiSick2023-04-05611
1810136AbbiSick2023-04-211.511
1911767AbbyVacation2023-01-063.4811
2011767AbbyVacation2023-03-137.7311
2111767AbbyVacation2023-03-130.0211
2213215AbbySick2023-01-042.511
2313215AbbySick2023-01-05822
2413215AbbySick2023-01-067.2533
2513215AbbySick2023-01-25711
2613215AbbyVacation2023-02-03411
2713215AbbySick2023-02-107.4711
2813215AbbySick2023-03-011.711
2913215AbbySick2023-03-13811
3013215AbbySick2023-03-16111
3113215AbbySick2023-03-170.8922
3213215AbbySick2023-03-277.1911
3313215AbbySick2023-04-03811
3413215AbbySick2023-04-18111
3513215AbbySick2023-04-19822
3613215AbbyVacation2023-04-20833
3713215AbbyVacation2023-04-21844
3813215AbbySick2023-04-28111
3914064AbigailVacation2023-01-03811
4014064AbigailVacation2023-01-31811
4114064AbigailVacation2023-02-01822
4214064AbigailVacation2023-02-024.533
4314064AbigailVacation2023-02-23411
4414064AbigailVacation2023-02-24822
4514064AbigailVacation2023-03-13811
4614064AbigailVacation2023-03-14822
4714064AbigailVacation2023-03-15833
4814064AbigailVacation2023-03-16844
4914064AbigailVacation2023-03-17855
5014187AddieDay of Service2023-02-17811
5114187AddieVacation2023-03-27811
5214187AddieVacation2023-03-28822
5314187AddieVacation2023-03-29833
5414187AddieVacation2023-03-30844
5514187AddieVacation2023-03-31855
5613457AdrianVacation2023-02-17811
5713457AdrianSick2023-02-22511
5813457AdrianVacation2023-04-03811
5913457AdrianVacation2023-04-04822
6013457AdrianVacation2023-04-05833
6113457AdrianVacation2023-04-06844
6213457AdrianVacation2023-04-07855
6312908AdrianaSick2023-01-166.111
6412908AdrianaSick2023-01-17822
6512908AdrianaSick2023-02-01711
6612908AdrianaSick2023-04-07711
67100356AdrianaVacation2023-02-17811
68100356AdrianaVacation2023-02-212.2666711
69100356AdrianaVacation2023-03-22711
70100356AdrianaVacation2023-04-18811
71100356AdrianaSick2023-04-21711
7212290AggiVacation2023-01-03811
7312290AggiSick2023-02-02811
7412290AggiDay of Service2023-02-03822
7512290AggiSick2023-02-280.611
7612290AggiVacation2023-03-13811
7712290AggiVacation2023-03-14822
7812290AggiVacation2023-03-15833
7912290AggiVacation2023-03-16844
8012290AggiVacation2023-03-17855
8110137AileenVacation2023-01-03811
8210137AileenVacation2023-01-04822
8310137AileenVacation2023-01-05833
8410137AileenVacation2023-01-06844
8510137AileenVacation2023-01-09855
8610137AileenSick2023-01-121.71666611
8710137AileenDay of Service2023-01-30811
8810137AileenVacation2023-02-02811
8910137AileenVacation2023-02-035.68333322
9010137AileenSick2023-02-141.49999911
9110137AileenVacation2023-02-156.31666722
9210137AileenSick2023-02-243.16666611
9310137AileenSick2023-02-282.93333411
9410137AileenVacation2023-04-07811
9510137AileenSick2023-04-113.54999911
96100362AimeeVacation2023-02-13811
97100362AimeeVacation2023-02-14822
98100362AimeeSick2023-02-21111
99100362AimeeSick2023-03-211.311
100100362AimeeVacation2023-04-041.5511
101100362AimeeSick2023-04-17811
10212970AJSick2023-01-25111
10312970AJDay of Service2023-01-26822
10412970AJSick2023-01-31211
10512970AJSick2023-02-07111
10612970AJVacation2023-03-08811
10712970AJVacation2023-03-09822
10812970AJSick2023-03-22111
10912970AJSick2023-03-31211
11012970AJSick2023-04-04111
11112970AJVacation2023-04-05822
11212970AJVacation2023-04-06833
dms11463
Cell Formulas
RangeFormula
G2:G112G2=IF(AND(B2=B1,WORKDAY.INTL(D2,-1,1)=D1),N(G1)+1,1)
Click to expand...
Ok - I DO want to also include holidays as a consecutive date off work - but I'm not sure I understand how to add that into the formula.....this is pretty much over my skillset
 
Upvote 0
dms11463 said:
I'm not sure I understand the holiday -

Ok - I DO want to also include holidays as a consecutive date off work - but I'm not sure I understand how to add that into the formula.....this is pretty much over my skillset
Click to expand...
i'll put an example in... hold on.
 
Upvote 0
with a small holiday schedule. the holidays in this list are consecutive, but i hope you get the idea:

mr excel questions 32.xlsm
ABCDEFGHIJ
1Employee IDWorkerTypeTime Off DateApproved Hoursneed to count consecutive WEEKDAYS absent at each change in Employee ID - using the absence dates in Column H and using the Saturday/Sunday weekendsExpected ResponseNote - actual worksheet will have over 50,000 rowsHOLIDAYS
2100339AaliaVacation2023-04-208112023-02-20
3100339AaliaDay of Service2023-04-218222023-02-21
4100339AaliaVacation2023-04-24833
5100339AaliaSick2023-04-27211
6100339AaliaVacation2023-04-28822
710136AbbiSick2023-01-27811
810136AbbiSick2023-02-176.0711
910136AbbiSick2023-02-22321
1010136AbbiSick2023-02-23332
1110136AbbiSick2023-03-011.9211
dms11463
Cell Formulas
RangeFormula
G2:G11G2=IF(AND(B2=B1,WORKDAY.INTL(D2,-1,1,$J$2:$J$3)=D1),N(G1)+1,1)
 
Upvote 0
Solution
awoohaw said:
with a small holiday schedule. the holidays in this list are consecutive, but i hope you get the idea:

mr excel questions 32.xlsm
ABCDEFGHIJ
1Employee IDWorkerTypeTime Off DateApproved Hoursneed to count consecutive WEEKDAYS absent at each change in Employee ID - using the absence dates in Column H and using the Saturday/Sunday weekendsExpected ResponseNote - actual worksheet will have over 50,000 rowsHOLIDAYS
2100339AaliaVacation2023-04-208112023-02-20
3100339AaliaDay of Service2023-04-218222023-02-21
4100339AaliaVacation2023-04-24833
5100339AaliaSick2023-04-27211
6100339AaliaVacation2023-04-28822
710136AbbiSick2023-01-27811
810136AbbiSick2023-02-176.0711
910136AbbiSick2023-02-22321
1010136AbbiSick2023-02-23332
1110136AbbiSick2023-03-011.9211
dms11463
Cell Formulas
RangeFormula
G2:G11G2=IF(AND(B2=B1,WORKDAY.INTL(D2,-1,1,$J$2:$J$3)=D1),N(G1)+1,1)
Click to expand...
That did not seem to work with the holidays. In your example, I would then expect row 9 to equal 4 (2/17 = 1, 2/20 holiday = 2, 2/21 holiday = 3, 2/22 absence = 4)
 
Upvote 0
Okay, holidays a
dms11463 said:
That did not seem to work with the holidays. In your example, I would then expect row 9 to equal 4 (2/17 = 1, 2/20 holiday = 2, 2/21 holiday = 3, 2/22 absence = 4)
Click to expand...
Holidays are dates that are EXCLUDED from the workday calculation. I'm not sure what your after then just take the holiday argument out of the formula if you want to count non weekend holidays where employees are absent.
 
Upvote 0
Thanks - for clarity - the ideal formula would include a holiday as a consecutive absence.

So if the employee was off Thursday/Friday, then Monday is a holiday, and the employee is off again on Tuesday, Tuesday would equal the 4th consecutive day of the absence.

This might not be possible for this solution - I don't know.
awoohaw said:
Okay, holidays a

Holidays are dates that are EXCLUDED from the workday calculation. I'm not sure what your after then just take the holiday argument out of the formula if you want to count non weekend holidays where employees are absent.
Click to expand...
 
Upvote 0
well, if you listed that monday holiday in the column of dates then it would count if you did not use a holiday list.
 
Upvote 1
You must log in or register to reply here.

Similar threads

S
  • Solved
Individual Ranking
2
Replies
11
Views
455
sksanjeev786
S
K
  • Question Question
Compress consecutive numbers
Replies
9
Views
371
Fuji
F
G
  • Locked
  • Question Question
VBA Userform - Change from 1900 Date Format to Long Date
Replies
1
Views
58
Peter_SSs
Peter_SSs
F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
188
Peter_SSs
Peter_SSs
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top