• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number of days excluding holidays,leave and off

T

Thomas Kuriakose

Active Member
Respected Sirs,

We have a task data where we need to calculate the following -

1. Received date(RD) - Start date(DS)
2. Start date(DS) - Issue date (DI)

The Received date(RD) - Start date(DS) is calculated in column E2:E33 and should exclude the following -
(a) Weekly Off - Friday and Saturday
(b) Holidays - J3:J14
(c) Leaves as per each in charge from K2 to V30

I am able to get the values but it includes the leaves of all in charge. How to get the values per in charge based on the holidays, leave and weekly off.

Kindly find attached the file for your reference.

Thank you very much,

with regards,
thomas
 
Respected Sir,

Thank you very much for your kind support on this.

I could not understand, correct me if I am wrong.

Each in charge has a different duration of leave (sick, emergency or vacation).

So should this be added in one column.

Thank you very much,

with regards,
thomas
 
Thats what I would do

Maybe have Holidays in Z2:Z100
Have Leaves in Z101:Z200

Then choose Z2:Z200 for the Holiday range
 
Respected Sir,

Apologies if I have not followed your suggestion.

I am getting #Value! when I changed the range to Z2: Z200

Kindly let me know where I am going wrong on this.

Thank you so much,

with regards,
thomas
 
Respected Sir,

Apologies, missed the attachment.

Kindly find attached.

Thank you very much.

with regards,
thomas
 

Attachments

  • Workdays.xlsx
    20 KB · Views: 4
Respected Sir,

Thank you so much for the solution provided. This working with respect to the holidays, leave and vacation.

Kindly let me know how to add the weekly off (Fridays and Saturdays) which also needs to be excluded from the date range.

Thank you very much once again.

with regards,
thomas
 
Thomas,

It seems to be working properly.

In the attached, I added some calculations to give the total days and to give the days excluding Friday and Saturday. These indicate that the array formula provided previously is working.

I do note that the Holiday and other leave only include dates in 2017 even though you have date ranges that begin in 2016. Perhaps that is the issue?

I hope that helps.

Regards,
Ken
 

Attachments

  • Workdays_KenU_2.xlsx
    14.5 KB · Views: 8
You must log in or register to reply here.
Back
Top