Calculate hours/per month between two dates

[Per9922]

Hello,

I wounder if someone could help me ..

I have this problem:

I have two dates, eg:
Start: 15 Sep 2011
Finished: 4 Mar 2012

I would like to spread out 200 h over the period, so I get how many hours per/month 200 will be.

quick tricky ....

Please Help!

 

[Bluejay07]

The only way I can think of doing this is if you take an average of the hours per month (unless you have a maximum of hours a single month can have).

datediff("M", DateSerial(2011,9,15),DateSerial(2012,3,4))
' Using dateserial makes the date format the same regardless of the users regional settings.
'The above result is 6.

'To get the average hours per month, hours/months.
200/6 = 33.33 (rounded to 2 decimal places)

Please note that due to rounding, you may not get exactly 200 hours. (Ex. 33.33 * 6 = 199.98) Although if the value is rounded up to the nearest whole number, you would have your 200 hours.

I hope this helps.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[strongm]

Yep, this can be done. But it requires further info. Are we talking about working hours? Or just any hours (any hours is easier)?

 

[Per9922]

working hours ...

 

[gmmastros]

What about work holidays?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Per9922]

It would be good to remove the work holidays ..

 

[strongm]

Tell you what, I'll provide a basic example of a workig hours solution, and then you see if you can add the working holidays bit. Here's that basic example, which I've knocked together fairly quickly:

[blue]Option Explicit
Private Const myMonth As Long = 0
Private Const myWorkingDays As Long = 1
Private Const myCalculatedHours As Long = 2

Private Sub Command1_Click()
    Dim myResult As Variant
    Dim lp As Long
    
    myResult = HourSpread(200, "15 sep 2011", "4 March 2012")
    For lp = LBound(myResult, 2) To UBound(myResult, 2)
        Debug.Print myResult(myMonth, lp) & " Working Days: " & myResult(myWorkingDays, lp) & " Hours: " & Round(myResult(myCalculatedHours, lp), 2)
    Next
End Sub

Public Function HourSpread(Hours As Single, Startdate As Date, EndDate As Date) As Variant
    Dim lpMonth As Long
    Dim CurrentMonth As Date
    Dim NextMonth As Date
    Dim Total As Long
    Dim EachMonth() As Variant 'Long

    EndDate = DateAdd("d", 1, EndDate) ' depending on our definition of 'between'
    CurrentMonth = Startdate

    ReDim EachMonth(2, DateDiff("m", Startdate, EndDate)) As Variant 'Long

    For lpMonth = LBound(EachMonth, 2) To UBound(EachMonth, 2)
        NextMonth = DateSerial(Year(CurrentMonth), Month(CurrentMonth) + 1, 1)
        'DateAdd("m", 1, CurrentMonth)
        If NextMonth > EndDate Then NextMonth = EndDate
        EachMonth(myWorkingDays, lpMonth) = CountWeekdaysBetweenDates(CurrentMonth, NextMonth)
        Total = Total + EachMonth(myWorkingDays, lpMonth)
        CurrentMonth = NextMonth '+ 1
    Next

    For lpMonth = LBound(EachMonth, 2) To UBound(EachMonth, 2)
        EachMonth(myMonth, lpMonth) = Format((DateAdd("m", lpMonth, Startdate)), "mmm yyyy")
        EachMonth(myCalculatedHours, lpMonth) = 200 * EachMonth(myWorkingDays, lpMonth) / Total
    Next
    HourSpread = EachMonth
End Function

' Frtom my tek-tips example in thread222-361893
' Assumes EndDate>= StartDate
Private Function CountWeekdaysBetweenDates(ByVal Startdate As Date, ByVal EndDate As Date) As Long
    If Weekday(Startdate, vbMonday) > 5 Then Startdate = DateAdd("d", 3 - Weekday(Startdate, vbSaturday), Startdate)
    If Weekday(EndDate, vbMonday) > 5 Then EndDate = DateAdd("d", -Weekday(EndDate, vbSaturday), EndDate)
    CountWeekdaysBetweenDates = DateDiff("d", Startdate, EndDate) - 2 * (DateDiff("ww", Startdate, EndDate))
End Function[/blue][

 

[Per9922]

Thanks you very much!!!