Count no. of days from list fall within a certain month

[sumit16]

Hi Experts,

Your help is highly appreciated.

My problem is , I have a date range 21-Feb-2013 till 07-Mar-2013 (Col C2, Col D2 respectively) which is holiday list for a person.
I am trying to pull out the number of working days for that person in the month of Feb which should exclude weekends.


To get the total no. of working days for the month of Feb, I have used the below formula.

=NETWORKDAYS(DATE(YEAR(C2),MONTH(C2),1),EOMONTH(C2,0))

This gives me a value of 20 which is correct.

Now I need to find no. of days which fall under the month of FEB from the date range 21-Feb-2013 till 07-Mar-2013 (Col C2, Col D2) which are working days. so that I can subtract that from no. of working days for that month (FEB) to get the no. of working days which the person has actually worked.

Thanks in advance.
-SN

 

[Akashwani]

Hi and welcome to MrExcel.

Is this what you require?....

Excel Workbook
	B	C	D	E	F	G	H	I
1					Work Days in Feb	Holidays for Feb	Days Worked in Feb
2		21/02/2013	07/03/2013		20	6	14
3
Sheet5

I hope that helps.

Good luck.

Ak

 

[sumit16]

Thanks Akashwani!!

This is what I was looking for. I was able to conver this to a vba code:

'-----------------------Get working days for the range-----------------
Public Function WorkingDays(StartDate As Date, EndDate As Date, wrkDayCnt As Double) As Long
Dim intCount As Long
intCount = 0
Do While StartDate <= EndDate
Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
UserForm1.TextBox3.Value = WorkingDays ' Displaying it in a Text Box in the UserForm.
wrkDayCnt = WorkingDays
End Function


'------------------- Can be used to determind the no. of days for the leave with Start date on the reporting month ------------------
Public Function WorkingDaysMonth(StartDate As Date, EndDate As Date, lvlDayCnt As Double) As Long
Dim intCount As Long
intCount = 0
'-----------------------------Last Date of the Month-----------------------------
LastDayOfMonth = DateSerial(Year(Format(StartDate)), (Month(StartDate) + 1), 0)
'--------------------------------------------------------------------------------
Do While StartDate <= LastDayOfMonth ' EndDate
Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDaysMonth = intCount
UserForm1.TextBox6.Value = WorkingDaysMonth ' Displaying it in a Text Box in the UserForm.
levDayCnt = WorkingDaysMonth
End Function
'---------------------------------------------------------------------------------------------------------------------------------------

Currently I have a complex requirement.

If you can help me out in generating a report.

My requirement is stated below.</SPAN>

I have 4 tabs in Excel (Version 2010). </SPAN>

Tab 1 = Resources (Col C – Employee ID, Col F – Project, Col G – Team, Col Q – Location)</SPAN>
Tab 2 = Holidays (Col B – Year, Col E – Leave Date, Col F – Location)</SPAN>
Tab 3 = LeaveRequest (Col B – Employee ID, Col L – Leave Status {Approved/Pending}, Col F – Leave Start Date, Col G – Leave End Date, Col H – Location)</SPAN>
Tab 4 = Location (Col B – Location, Col C – Working Hours)</SPAN>

Selection Screen (Vbform) has 4 parameters:</SPAN>

1. Project</SPAN>
2. Team</SPAN>
3. Month</SPAN>
4. Year</SPAN>

I am trying to get the productivity report for a particular team based on the 4 tables in two ways. </SPAN>

1. Report showing productivity with Approved Leave</SPAN>
2. Report showing productivity with Approved + Pending Approval (Pending) leaves.</SPAN>

Calculation would be like this.</SPAN>

1. Get no. of resources from Tab 1 for the project and team selected</SPAN>
2. Get no. of unique locations from Tab 1 for the above selection (Location is in Col Q)</SPAN>
3. Get no. of resources per location from the step 1</SPAN>
4. Check no. of holidays for that location in Tab 2 for the month and year selected</SPAN>
5. Get no. of working days for the month and year selected (which should exclude Saturdays and Sundays)</SPAN>
6. Get no. of working hours for each location from Tab 4</SPAN>
7. To get the overall no. of working hours: [[Monthly working days for the location (Step 5) – No. of holidays for that location (Step 4)] * No. of resources for that location (Step 3) * No. of working hours for that location (Step 6)]</SPAN>
8. To get the no. of Approved Leaves for the Project, Team, Month & Year Selected, check Tab 3.</SPAN>
   • If the Leave date starts in previous month and ends in current (selected) month then calculate only those days which are corresponding to the current month.</SPAN>
   • Also check if the above filtered date has any weekends and holidays from Tab 2.</SPAN>
   • Get the loop to check it for all the Employee ID’s from Step 3 with status ‘Approved’ in column L of Tab 3 </SPAN>
   • Get the loop to check it for all the Employee ID’s from Step 3 with status ‘Pending’ in column L of Tab 3 </SPAN>
9. Subtract value from Step 7 and value from Step 8 (‘Approved’ only and ‘Approved + Pending’)</SPAN>
10. Create a new sheet with name “WorkingSheet” and display the value in columns below:</SPAN>

Col A – Project Name</SPAN>
Col B – Team Name</SPAN>
Col C – Month </SPAN>
Col D – Year </SPAN>
Col E – Total No. of resources</SPAN>
Col F –Total monthly Working hours for all Locations</SPAN>
Col G – Total no. of working hours in Approved Leave</SPAN>
Col H – Total no. of working hours in “Pending’ Leaves</SPAN>
Col I – Productivity with Approved Leaves</SPAN>
Col J – Productivity with “Approved + Pending” Leaves</SPAN>




</SPAN>

 

[Akashwani]

Hi,

If the second part of your post is an additional question, I suggest that you start a new post and break down your questions to more manageable parts.
I don't have the time to look at a big question like this, so I cannot help you further and I think there are probably too many requirements for most people to take it all on in one go!

Good luck.

Ak