Holiday report (Days between two date range)

[arunkumargf]

Hi Everyone,

my name is Arun, I have recently come across a holiday sheet which has holiday start date - end date and I need to be able to find out how many holiday days fall in a particular date range (pay period) based on the employees work pattern. Example file attached.
I am not an excel expert and would really appreciate some help.

Many thanks,
Arun

 

[p45cal]

Test this in cell L2 and copy down; it should go some way to getting what you're after:

=NETWORKDAYS.INTL(MAX($N$2,I2),MIN(J2,$O$2),SUBSTITUTE(SUBSTITUTE(E2,"-","1"),"Y","0"))

I've assumed the Work pattern column starts on a Monday, if it doesn't it'll need tweaking, perhaps to:

=NETWORKDAYS.INTL(MAX($N$2,I2),MIN(J2,$O$2),SUBSTITUTE(SUBSTITUTE(RIGHT(E2,6)&LEFT(E2,1),"-","1"),"Y","0"))

but you'll need to tell us how that column describes the work pattern.
Each entry in the Work pattern column must be 7 characters long (one wasn't).

You can include statutory holidays if you want as the 4th argument of the NETWORKDAYS.INTL function.

What version of Excel are you using?

 

[arunkumargf]

Test this in cell L2 and copy down; it should go some way to getting what you're after:

=NETWORKDAYS.INTL(MAX($N$2,I2),MIN(J2,$O$2),SUBSTITUTE(SUBSTITUTE(E2,"-","1"),"Y","0"))

I've assumed the Work pattern column starts on a Monday, if it doesn't it'll need tweaking, perhaps to:

=NETWORKDAYS.INTL(MAX($N$2,I2),MIN(J2,$O$2),SUBSTITUTE(SUBSTITUTE(RIGHT(E2,6)&LEFT(E2,1),"-","1"),"Y","0"))

but you'll need to tell us how that column describes the work pattern.
Each entry in the Work pattern column must be 7 characters long (one wasn't).

You can include statutory holidays if you want as the 4th argument of the NETWORKDAYS.INTL function.

What version of Excel are you using?

Test this in cell L2 and copy down; it should go some way to getting what you're after:

=NETWORKDAYS.INTL(MAX($N$2,I2),MIN(J2,$O$2),SUBSTITUTE(SUBSTITUTE(E2,"-","1"),"Y","0"))

I've assumed the Work pattern column starts on a Monday, if it doesn't it'll need tweaking, perhaps to:

=NETWORKDAYS.INTL(MAX($N$2,I2),MIN(J2,$O$2),SUBSTITUTE(SUBSTITUTE(RIGHT(E2,6)&LEFT(E2,1),"-","1"),"Y","0"))

but you'll need to tell us how that column describes the work pattern.
Each entry in the Work pattern column must be 7 characters long (one wasn't).

You can include statutory holidays if you want as the 4th argument of the NETWORKDAYS.INTL function.

What version of Excel are you using?

Hello,

Thank you for the prompt response.

I did try both the formula's and was getting a name error, removed the .INTL bit from networkdays and it did populate results but incorrect.

I did mention the work pattern (Starts from Sunday) on column E1 but could have been easily missed; sorry for not mentioning it on my post.
I have updated the employee with a day missing in the work pattern and have put more examples, the file is attached to this post.

I did try both the formula's and was getting a name error, screenshot attached.

I am using excel 2007.

Once again, many thanks for your help and prompt response.

Regards,

Arun

 

[bosco_yip]

Since the OP using Excel 2007, and don 't have the NETWORKDAYS.INTL function.

Here is an old school formula solution as in.

In M2, array formula copied down :

=SUM(INT(((MIN(J2,$O$2)-MAX(I2,$N$2))+7-MOD(COLUMN(INDIRECT("C"&MATCH("Y",MID(E2,COLUMN(A:G),1),0)&":C"&F2+MATCH("Y",MID(E2,COLUMN(A:G),1),0)-1,0))-MAX(I2,$N$2),7))/7))

Array formula to be confirmed by pressing "Ctrl+Shift+Enter" 3 keystrokes to enter.

 

[Excel Wizard]

Please try

=SUMPRODUCT((MID(E2,ROW(Z$1:Z$7),1)="Y")*INT(((MIN(J2,$O$2)-MAX($N$2,I2))+MOD(MAX($N$2,I2)-ROW(Z$2:Z$8),7)+1)/7))

 

[airaldom]

Hi,
Another solutión:
=SUM((WEEKDAY(SEQUENCE(J2-I2+1,,I2),2)=(MID(E2,SEQUENCE(,7),1)="y")*SEQUENCE(,7))*1)

 

[arunkumargf]

Please try

=SUMPRODUCT((MID(E2,ROW(Z$1:Z$7),1)="Y")*INT(((MIN(J2,$O$2)-MAX($N$2,I2))+MOD(MAX($N$2,I2)-ROW(Z$2:Z$8),7)+1)/7))

Thanks @Excel Wizard,

This has been the most helpful so far, I am still testing different dates etc to see if anything changes and will revert back by next week.

Once again, thank you for taking the time to look into this.

Yours sincerely,

Arun Kumar