Calculating Working hours in a Week

[surya prakash]

Hi,

I am wondering if it is possible to calculate Monthly working hours with following fixed weekly days work hours:

Mon ---- 8 Hrs
Tue ---- 8 Hrs
Wed ---- 4 hrs
Thurs ---- 8 Hrs
Fri ---- 5 Hrs
Sat ---- 0 hrs (weekend)
Sun ---- 0 hrs (weekend)

Based on above inputs;

May'11
Total Work Hours in June'11 = 144 Hrs

sample Calculation below:

01-Jun-11----Wed----4 hrs
02-Jun-11----Thu----8 hrs
03-Jun-11----Fri----5 hrs
04-Jun-11----Sat----0 hrs
05-Jun-11----Sun----0 hrs
06-Jun-11----Mon----8 hrs
07-Jun-11----Tue----8 hrs
08-Jun-11----Wed----4 hrs
09-Jun-11----Thu----8 hrs
10-Jun-11----Fri----5 hrs
11-Jun-11----Sat----0 hrs
12-Jun-11----Sun----0 hrs
13-Jun-11----Mon----8 hrs
14-Jun-11----Tue----8 hrs
15-Jun-11----Wed----4 hrs
16-Jun-11----Thu----8 hrs
17-Jun-11----Fri----5 hrs
18-Jun-11----Sat----0 hrs
19-Jun-11----Sun----0 hrs
20-Jun-11----Mon----8 hrs
21-Jun-11----Tue----8 hrs
22-Jun-11----Wed----4 hrs
23-Jun-11----Thu----8 hrs
24-Jun-11----Fri----5 hrs
25-Jun-11----Sat----0 hrs
26-Jun-11----Sun----0 hrs
27-Jun-11----Mon----8 hrs
28-Jun-11----Tue----8 hrs
29-Jun-11----Wed----4 hrs
30-Jun-11----Thu----8 hrs

[Paul_Hossler]

This is a bit brute force

[VBA]
Sub test()
MsgBox WorkingHours(#6/1/2011#)

End Sub

Function WorkingHours(MonthYear As Date) As Double
Dim i As Long
Dim N As Double
N = 0#
For i = DateSerial(Year(MonthYear), Month(MonthYear), 1) To DateSerial(Year(MonthYear), Month(MonthYear) + 1, 0)
Select Case Weekday(i)
Case vbMonday, vbTuesday, vbThursday
N = N + 8
Case vbWednesday
N = N + 4
Case vbFriday
N = N + 5
End Select
Next i
WorkingHours = N
End Function
[/VBA]

Paul

[surya prakash]

Hi Paul,

Many thanks for your prompt response.

I have slightly modified your code to following, but doesnt seem to be getting the correct results; can you please have a look...

Input: =WorkingHours2(1-6-11,8,8,4,8,5,0,0)

Public Function WorkingHours2(ByVal myMonth As Date, ByVal myMonHrs As Double, ByVal myTueHrs As Double, ByVal myWedHrs As Double, ByVal myThursHrs As Double, ByVal myFriHrs As Double, ByVal mySatHrs As Double, ByVal mySunHrs As Double) As Double
    
    Dim i As Long
    Dim N As Double
    N = 0#
    
    For i = DateSerial(Year(MonthYear), Month(MonthYear), 1) To DateSerial(Year(MonthYear), Month(MonthYear) + 1, 0)
        
        Select Case Weekday(i)
        Case vbMonday
            N = N + myMonHrs
        
        Case vbTuesday
            N = N + myTueHrs
        
        Case vbWednesday
            N = N + myWedHrs
        
        Case vbThursday
            N = N + myThursHrs
        
        Case vbFriday
            N = N + myFriHrs
            
       Case vbSaturday
            N = N + mySatHrs
            
        Case vbSunday
            N = N + mySunHrs
            
        End Select
        
    Next i
    
    WorkingHours2 = N
    
End Function

[surya prakash]

Paul, many thanks; issue resolved...

Will revert back shortly...

thanks again...

[abishekmouli]

Hello,

I have created a file that for your problem and attaching it along in this mail. Let me know if it works fine.

Thanks and Regards,
Abi

[surya prakash]

Hi Paul,

As an extension to what is done earlier; I am wondering if holiday hours can extracted from the above calculations.

The holiday list (hours) are available in one of the excel sheet.
And these cells can be give a name range - say "Holidays"

Please could you the updated code that I have provided in my previous message

Date---Holiday Name---Working Hrs
17-Jan-11---Birthday of Martin Luther King, Jr.---0 Hrs
21-Feb-11---Washington’s Birthday---0 Hrs
30-May-11---Memorial Day---4 Hrs
04-Jul-11---Independence Day---0 Hrs
05-Sep-11---Labor Day---0 Hrs
10-Oct-11---Columbus Day---4 Hrs
11-Nov-11---Veterans Day---0 Hrs
24-Nov-11---Thanksgiving Day---0 Hrs
26-Dec-11---Christmas Day---0 Hrs
31-Dec-11---New Year Day---0 Hrs

[abishekmouli]

Hello,

I have created a file that for your problem and attaching it along in this mail. Let me know if it works fine.

Thanks and Regards,
Abi

[surya prakash]

Hi Abhishek,

Just saw your post; many thanks for the sample excel file..

I am looking at a function that will just return the working hours instead of populating them in the way that you have done..

I am wondering if it is possible to incorporate the holiday list as given below

Hi Paul,

As an extension to what is done earlier; I am wondering if holiday hours can extracted from the above calculations.

The holiday list (hours) are available in one of the excel sheet.
And these cells can be give a name range - say "Holidays"

Date---Holiday Name---Working Hrs
17-Jan-11---Birthday of Martin Luther King, Jr.---0 Hrs
21-Feb-11---Washington’s Birthday---0 Hrs
30-May-11---Memorial Day---4 Hrs
04-Jul-11---Independence Day---0 Hrs
05-Sep-11---Labor Day---0 Hrs
10-Oct-11---Columbus Day---4 Hrs
11-Nov-11---Veterans Day---0 Hrs
24-Nov-11---Thanksgiving Day---0 Hrs
26-Dec-11---Christmas Day---0 Hrs
31-Dec-11---New Year Day---0 Hrs

Please could you use below sample code:

Input: =WorkingHours2(1-6-11,8,8,4,8,5,0,0)

Public Function WorkingHours2(ByVal myMonth As Date, ByVal myMonHrs As Double, ByVal myTueHrs As Double, ByVal myWedHrs As Double, ByVal myThursHrs As Double, ByVal myFriHrs As Double, ByVal mySatHrs As Double, ByVal mySunHrs As Double) As Double
    
    Dim i As Long
    Dim N As Double
    N = 0#
    
    For i = DateSerial(Year(MonthYear), Month(MonthYear), 1) To DateSerial(Year(MonthYear), Month(MonthYear) + 1, 0)
        
        Select Case Weekday(i)
        Case vbMonday
            N = N + myMonHrs
        
        Case vbTuesday
            N = N + myTueHrs
        
        Case vbWednesday
            N = N + myWedHrs
        
        Case vbThursday
            N = N + myThursHrs
        
        Case vbFriday
            N = N + myFriHrs
            
       Case vbSaturday
            N = N + mySatHrs
            
        Case vbSunday
            N = N + mySunHrs
            
        End Select
        
    Next i
    
    WorkingHours2 = N
    
End Function

[Paul_Hossler]

added code to use a worksheet list of holidays

Paul

[Chabu]

Unless this is just out of academic interest, why don't you use a project management tool (like ms project).

All the things you are trying to reinvent are available there.

[surya prakash]

Thanks a Ton Paul. Works perfectly..

I am updating the same and will revert shortly...

regards
surya

added code to use a worksheet list of holidays

Paul

[surya prakash]

Thanks chabu,

I am aware of the PM tools such Primaveera, MS Project etc;
But I am trying to build this logic in Excel for a specific requirement.

Unless this is just out of academic interest, why don't you use a project management tool (like ms project).

All the things you are trying to reinvent are available there.

[surya prakash]

added code to use a worksheet list of holidays

Paul

Hi Paul,
Many thanks for the sample workbook.

I was looking for a small variation to incorporate different "Holiday Ranges"

For example,
if the location is UK, I need to consider a different holiday list and it were US, I need to consider entirely different one.

I have updated the code to incorporate above requirements, but am getting errors when I compile; please could you have a look.

Sample Excel file enclosed for your ready reference..

Thanks again...

[Paul_Hossler]

You just need the 'Set' to assign an object (i.e. the Range) to an object variable

[VBA]
If myLocation = "UK" Then
Set myHolidays = Worksheets("Factory Calender").Range("A2:H17")
ElseIf myLocation = "PHL" Then
Set myHolidays = Worksheets("Factory Calender").Range("A19:H31")
End If
[/VBA]

Paul

[surya prakash]

Many thanks Paul; works perfectly.

I am wondering if range name can be given in the following lines:
Please could you advise..

If myLocation = "UK" Then Set myHolidays = myUKHols ElseIf myLocation = "PHL" Then Set myHolidays = myPHLHols End If

[Paul_Hossler]

The Range part is easy (sub test below)

You might try to use the Windows Regional Setting to determine the location (sub Test2)


Paul

[vba]Option Explicit
Sub test()
Dim myLocation As Range, myHolidays As Range
Dim myUKHols As Range, myPHLHols As Range
If Range("myLocation").Value = "UK" Then
Set myHolidays = myUKHols
ElseIf Range("myLocation").Value = "PHL" Then
Set myHolidays = myPHLHols
End If
End Sub
'Explaination
'http://www.rondebruin.nl/international.htm
'List of contry codes
'http://support.microsoft.com/kb/213833/en-us
'"The codes shown above are derived from the country codes used by
' the telephone system in the United States of America."
Sub test2()
'xlCountrySetting Long Current country/region setting in the Windows Control Panel.
If Application.International(xlCountrySetting) = 1 Then
MsgBox "In the United States"
ElseIf Application.International(xlCountrySetting) = 91 Then
MsgBox "In India"
ElseIf Application.International(xlCountrySetting) = 45 Then
MsgBox "In Denmark"
End If
End Sub
' Language Country code Countries/regions
' -------------------------------------------------------------
' Arabic 966 (Saudi Arabia)
' Czech 42 (Czech Republic)
' Danish 45 (Denmark)
' Dutch 31 (The Netherlands)
' English 1 (The United States of America)
' Farsi 98 (Iran)
' Finnish 358 (Finland)
' French 33 (France)
' German 49 (Germany)
' Greek 30 (Greece)
' Hebrew 972 (Israel)
' Hungarian 36 (Hungary)
' Indian 91 (India)
' Italian 39 (Italy)
' Japanese 81 (Japan)
' Korean 82 (Korea)
' Norwegian 47 (Norway)
' Polish 48 (Poland)
' Portuguese (Brazil) 55 (Brazil)
' Portuguese 351 (Portugal)
' Russian 7 (Russian Federation)
' Simplified Chinese 86 (People's Republic of China)
' Spanish 34 (Spain)
' Swedish 46 (Sweden)
' Thai 66 (Thailand)
' Traditional Chinese 886 (Taiwan)
' Turkish 90 (Turkey)
' Urdu 92 (Pakistan)
' Vietnamese 84 (Vietnam)
[/vba]

[surya prakash]

That was superb Paul; thanks very much indeed....

[surya prakash]

Hi Paul,

Apologies for delayed response..

I was wondering if "Range Name" that I have set in the excel sheet to
range variable can be assigned to "Range Variable" in VBA


For example,

I have given the range_name in excel sheet as "UKHols" for cells in "B2:E40" in "Factory Calender" worksheet and "PHHols" being "E59:E71"

I am getting "Type Mismatch error" when I set the following:

If myLocation = "WHUK" Then
Set myHolidays = "Hols_WHIL"

ElseIf myLocation = "WHPHL" Then
Set myHolidays = "Hols_PH"

End If

Old Code

If myLocation = "WHUK" Then
Set myHolidays = Worksheets("Factory Calender").Range("B2:H40")

ElseIf myLocation = "WHPHL" Then
Set myHolidays = Worksheets("Factory Calender").Range("B73:H113")

End If

[Paul_Hossler]

try this, assuming that myLocation = a cell named 'myLocation'

[VBA]
If myLocation = "WHUK" Then
Set myHolidays = Range("Hols_WHIL")

ElseIf myLocation = "WHPHL" Then
Set myHolidays = Range("Hols_PH")

End If

[/VBA]

Paul

[surya prakash]

Hi Paul,

Many thanks for your prompt response.

I am getting "Method "Range' ofobject'_Global" failed" error when i add "range" ;

Please could you have a look at attached sample file.

regards

try this, assuming that myLocation = a cell named 'myLocation'

[vba]
If myLocation = "WHUK" Then
Set myHolidays = Range("Hols_WHIL")

ElseIf myLocation = "WHPHL" Then
Set myHolidays = Range("Hols_PH")

End If

[/vba]
Paul