Consecutive Days Off Assistance

ZvK

New Member
Joined
Nov 24, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

For my company, employees are required to take 10 consecutive days off. I can't seem to get the formula to count consecutive days (including weekend & holidays working). For example, Employee 2 should be 7 days, counting 12/25, 12/26, and 12/27. Here's the formula I tried to use: {=SUMPRODUCT(--($A$2:$A$28119=D4),--($B$3:$B$28120*($A$3:$A$28120=D4)<>WORKDAY(N(IF({1},$B$2:$B$28119)),1)))}. Is it possible to also identify the start of the consecutive date and end with a formula? Thank you!!

NAMETIME OFF DATENAME# of Days ConsecutiveStart DateEnd Date
Employee 112/01/2020Employee 12
Employee 112/02/2020Employee 25
Employee 212/14/2020Employee 35
Employee 212/22/2020Employee 41
Employee 212/23/2020Employee 57
Employee 212/24/2020Employee 62
Employee 212/28/2020Employee 74
Employee 312/21/2020Employee 89
Employee 312/22/2020Employee 96
Employee 312/23/2020Employee 106
Employee 312/24/2020Employee 119
Employee 312/28/2020Employee 124
Employee 412/04/2020
Employee 512/02/2020
Employee 512/03/2020
Employee 512/09/2020
Employee 512/14/2020
Employee 512/18/2020
Employee 512/24/2020
Employee 512/31/2020
Employee 612/30/2020
Employee 612/31/2020
Employee 712/28/2020
Employee 712/29/2020
Employee 712/30/2020
Employee 712/31/2020
Employee 812/01/2020
Employee 812/02/2020
Employee 812/03/2020
Employee 812/04/2020
Employee 812/07/2020
Employee 812/08/2020
Employee 812/09/2020
Employee 812/10/2020
Employee 812/11/2020
Employee 912/07/2020
Employee 912/08/2020
Employee 912/09/2020
Employee 912/10/2020
Employee 912/11/2020
Employee 912/24/2020
Employee 1012/23/2020
Employee 1012/24/2020
Employee 1012/28/2020
Employee 1012/29/2020
Employee 1012/30/2020
Employee 1012/31/2020
Employee 1112/18/2020
Employee 1112/21/2020
Employee 1112/22/2020
Employee 1112/23/2020
Employee 1112/24/2020
Employee 1112/28/2020
Employee 1112/29/2020
Employee 1112/30/2020
Employee 1112/31/2020
Employee 1212/21/2020
Employee 1212/22/2020
Employee 1212/23/2020
Employee 1212/24/2020
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,118,209
Messages
5,570,911
Members
412,348
Latest member
NATTS
Top