Days per Month

[OddProject]

Hi guys,

Easy one for you here, I just cant think of a way to do it.

I have a list of dates (Sample):

17/02/2009 07/04/2009
17/02/2009 16/03/2009
19/02/2009 17/03/2009
20/02/2009 17/03/2009
24/02/2009 15/04/2009
26/02/2009 19/03/2009

All I need is the number of days (Networkdays) per month.

Using the top row for example:

Feb 09__Mar 09__Apr 09
__9______20______5


I could do this myself manually, but I have over 200 rows. I just the number of network days per month between the two dates so I can use the data in graph (Days per month).

Many thanks guys.

 

[OddProject]

Please help, I need a solution ASAP.

Thanks

 

[OddProject]

Please someone help, im sure there is an easy solution...

 

[OddProject]

arghhhhhhhhhhhhhhhhhhhh! Have I not explained myself clearly? please someone at least tell me that much...

 

[Brianwarnock]

Sorry but we are not all sitting at our terminals waiting for your call.
Perhaps if you had spent your time doing a search of the forum you would have found Raskew's code as I did with little effort.
Btw it is a good job that you are not doing it manually as there are 26 days in March.

This code was written for Access but can be used with no alteration for Excel.
Your input would be =datediffexclude2(A1,B1,"17")

Brian

Function DateDiffExclude2(pstartdte As Date, _
                         penddte As Date, _
                         pexclude As String) As Integer
'*********************************************
'Purpose:   Return number of days between two
'              dates, excluding weekdays(Sun = 1
'              thru Sat = 7) specified by user
'Coded by:  raskew
'Inputs:    from debug (immediate) window:
'           -excludes Saturday (7) and Sunday (1) from count
'           ? DateDiffExclude2(#2/10/06#, #7/13/06#, "17")
'Output:    110
'*********************************************

Dim WeekHold  As String
Dim WeekKeep  As String
Dim FullWeek  As Integer
Dim OddDays   As Integer
Dim n         As Integer

    WeekHold = "1234567123456"
    'get # of full weeks (7 days) & convert to # of days
    FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
    'get # of days remaining after FullWeek is determined
    OddDays = (penddte - pstartdte + 1) Mod 7
    'string representation of the weekdays contained in OddDays
    WeekKeep = Mid(WeekHold, Weekday(pstartdte), OddDays)
    'use boolean statement to reduce OddDays by 1 for each
    'pexclude weekday found in WeekKeep
    For n = 1 To Len(pexclude)
      OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
    Next n
    
    DateDiffExclude2 = FullWeek + OddDays

End Function

 

[HaHoBe]

Hi, OddProject,

anything agianst activating the Analysis Toolpack AddIn and use NetWorkDays? The workbook shows an example of this function.

Ciao,
Holger

 

[OddProject]

Hi, OddProject,

anything agianst activating the Analysis Toolpack AddIn and use NetWorkDays? The workbook shows an example of this function.

Ciao,
Holger

Yes I use networkdays alot. But I couldnt find an easy way to break down the network days by month. Thanks for your reply, I dont suppose you could quickly write up a workbook with the month ranges from Feb 2009 - Feb 2010?

I have over 200 dates that need sorting by month ranging from the sample I provided to dates >01/02/2010. Some ranges span over several months too, and its getting very messy trrying to add the formulae to the next month etc etc... Your help is highly appreciated.

Your solution is pretty much what I need (after changing the NETTOAREBEIT thing to network days).

Thanks

 

[OddProject]

Sorry but we are not all sitting at our terminals waiting for your call.
Perhaps if you had spent your time doing a search of the forum you would have found Raskew's code as I did with little effort.
Btw it is a good job that you are not doing it manually as there are 26 days in March.

This code was written for Access but can be used with no alteration for Excel.
Your input would be =datediffexclude2(A1,B1,"17")

Brian

Function DateDiffExclude2(pstartdte As Date, _
                         penddte As Date, _
                         pexclude As String) As Integer
'*********************************************
'Purpose:   Return number of days between two
'              dates, excluding weekdays(Sun = 1
'              thru Sat = 7) specified by user
'Coded by:  raskew
'Inputs:    from debug (immediate) window:
'           -excludes Saturday (7) and Sunday (1) from count
'           ? DateDiffExclude2(#2/10/06#, #7/13/06#, "17")
'Output:    110
'*********************************************
 
Dim WeekHold  As String
Dim WeekKeep  As String
Dim FullWeek  As Integer
Dim OddDays   As Integer
Dim n         As Integer
 
    WeekHold = "1234567123456"
    'get # of full weeks (7 days) & convert to # of days
    FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
    'get # of days remaining after FullWeek is determined
    OddDays = (penddte - pstartdte + 1) Mod 7
    'string representation of the weekdays contained in OddDays
    WeekKeep = Mid(WeekHold, Weekday(pstartdte), OddDays)
    'use boolean statement to reduce OddDays by 1 for each
    'pexclude weekday found in WeekKeep
    For n = 1 To Len(pexclude)
      OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
    Next n
 
    DateDiffExclude2 = FullWeek + OddDays
 
End Function

Sorry for being impatient, it was doing my nut trying to work out a simple way of breaking the date ranges down into network days by month. I've never used code in excel either, so I wouldn't have searched for it.

Thanks, the solution provided after yours was pretty much what I was after, but even that gets a bit messy when I add the months throughout the year...

Thanks guys

 

[OddProject]

K, this is what I need for further analysis. I just cant seem to do it myself so Im kinda relying on you guys.

Again sorry for my posts yesturday, you guys help me out alot on here and I should show my appreciation.

Many thanks,

OddPro

 

[DCrake]

1 problem I can see is that the column heads cannot be defined until the last row has been read. If you reversed the first and last entries or sorted descending it would not know what month intervals to use as column headings.

To me you would need your data split by month then apply the network days to each columns data.

Having said that if you calculate the number of days betwen the first date in the range and the last day of the month for the start date, this will give you value A.
Do the same for the last date in the range to give you value Z

Example

15/03/2009 - 25/05/2009

Value A = 16 (15th - 31st March) - weekends+holidays
Value Z = 6 (25th - 31st May) - weekends+holidays

All the remaining month are whole months and should be static, so the values of the network days could obtained using a vlookup in a static dataset.

 

[OddProject]

Thanks for the reply, It's all starting to sound very complicated... to me this sounded simple (in my head) but just couldn't find a way of implementing it. If you could throw up a sample xls that would be sweet.

Thanks again.

 

[OddProject]

Any chance of a response/solution please?

 

[Brianwarnock]

In your sample database the months did not cover the full range of dates in cols A and B.
If the row heads, ie Row 1 is in place then filling the cells is fairly simple.
I could not get Networkdays to work in VBA either as Application.worksheetfunction.Networkdays or as a VBA addin, guess I haven't figured those out yet. So I used Raskews function.

Explanation as to how to use it in the macro in module1

I changed some of the dates in Sheet2 to enable the testing of the various scenarios.

Brian

 

[OddProject]

Nice one Brian, exactly what I needed

Many thanks!

OddPro

 

[Brianwarnock]

Glad I could help, I enjoyed the challenge.

Brian