Calculating Working hours in a Week

Printable View

06-01-2011, 05:26 AM
surya prakash

Calculating Working hours in a Week

Hi,

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

Quote:

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:

Quote:

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
06-01-2011, 06:36 AM
Paul_Hossler

This is a bit brute force

Code:

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

Paul
06-01-2011, 07:10 AM
surya prakash

Hi Paul,

Many thanks for your prompt response.

I have slightly modified your code to following, but doesn't 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)

Code:

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
06-01-2011, 07:22 AM
surya prakash

Paul, many thanks; issue resolved...

Will revert back shortly...

thanks again... :)
06-01-2011, 02:16 PM
abishekmouli

1 Attachment(s)

File

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
06-01-2011, 02:27 PM
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

Quote:

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
06-01-2011, 02:32 PM
abishekmouli

Quote:

Originally Posted by 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
06-01-2011, 02:39 PM
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

Quote:

Originally Posted by 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"

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:

Quote:

Originally Posted by surya prakash

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

HTML Code:

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
06-01-2011, 04:05 PM
Paul_Hossler

1 Attachment(s)

added code to use a worksheet list of holidays

Paul
06-02-2011, 02:25 AM
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.
06-08-2011, 04:44 AM
surya prakash

Thanks a Ton Paul. Works perfectly..

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

regards
surya

Quote:

Originally Posted by Paul_Hossler

added code to use a worksheet list of holidays

Paul
06-08-2011, 04:49 AM
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.

Quote:

Originally Posted by 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.
06-15-2011, 10:00 PM
surya prakash

1 Attachment(s)

Quote:

Originally Posted by Paul_Hossler

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...
06-16-2011, 07:06 AM
Paul_Hossler

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

Code:

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

Paul
06-16-2011, 09:48 PM
surya prakash

Many thanks Paul; works perfectly.

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

Quote:

If myLocation = "UK" Then
Set myHolidays = myUKHols
ElseIf myLocation = "PHL"
Then Set myHolidays = myPHLHols
End If
06-17-2011, 05:54 AM
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

Code:

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)
06-17-2011, 09:57 PM
surya prakash

That was superb Paul; thanks very much indeed....
06-27-2011, 02:41 AM
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:

Quote:

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

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

End If

Old Code

Quote:

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
06-27-2011, 04:58 AM
Paul_Hossler

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

Code:

If myLocation = "WHUK" Then Set myHolidays = Range("Hols_WHIL") ElseIf myLocation = "WHPHL" Then Set myHolidays = Range("Hols_PH") End If

Paul
06-28-2011, 01:37 AM
surya prakash

1 Attachment(s)

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

Quote:

Originally Posted by Paul_Hossler

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

Code:

If myLocation = "WHUK" Then Set myHolidays = Range("Hols_WHIL") ElseIf myLocation = "WHPHL" Then Set myHolidays = Range("Hols_PH") End If

Paul
06-28-2011, 05:49 AM
frank_m

A stab in the dark

Code:

' other code Dim myHolidays As String ' other code If myLocation = "WHUK" Then myHolidays = Names("Hols_WHIL").RefersTo ElseIf myLocation = "WHPHL" Then myHolidays = Names("Hols_PH").RefersTo End If ' other code
06-28-2011, 11:52 AM
CatDaddy

why don't you just declare your ranges in the sub to avoid confusion?

Code:

dim Hols_WHIL As Range set Hols_WHIL = Range("...somerangevalues...") If myLocation = "WHUK" Then myHolidays = Hols_WHIL
06-28-2011, 06:21 PM
Paul_Hossler

1 Attachment(s)

Couple of things I see

1. In the test sub and in the Function definition, 'myLocation' is a string, and there is no named range called 'myLocation' so Range("myLocation') won't work

2. Hols_WHIL and Hols_WHUAE have #REF errors

Code:

Sub test() MsgBox WorkingHours3("WHUK", #6/1/2011#, 8, 8, 4, 8, 5, 0, 0) End Sub Public Function WorkingHours3(ByVal myLocation As String, 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 myDateLoop As Long, myHolidayHours As Long Dim myHoursWorked As Double Dim myHolidays As Range myHoursWorked = 0# If Range("myLocation").Value = "WHUK" Then myHolidays = Range("Hols_WHUK")

3. Since myHolidays is Dim-ed as a range object you need to use Set

Code:

Set myHolidays = Range("Hols_WHUK")

4. On sheet 'Working hrs calc', B4 and B5 you have UK and PHL, but that's not one of the cases in the If/Then/Elseif so it keeps on going

5. Suggest you have a final 'Else' to make a error condition

Code:

If myLocation = "WHUK" Then ' tested as string Set myHolidays = Range("Hols_WHUK") ' added Set ElseIf myLocation = "WHIL" Then Set myHolidays = Range("Hols_WHIL") ' range is not defined ElseIf myLocation = "WHUAE" Then Set myHolidays = Range("Hols_WHUAE") ' range is not defined ElseIf myLocation = "WHPHL" Then Set myHolidays = Range("Hols_WHPH") Else ' added a error catcher MsgBox "bad parameter" WorkingHours3 = -999 Exit Function End If

Paul
07-03-2011, 11:24 PM
surya prakash

1 Attachment(s)

Hi Frank,
I am still getting the same error.
Updated excel file with vba code enclosed for your ready reference..

thanks for looking into..

regards

Quote:

Originally Posted by frank_m

A stab in the dark

Code:

' other code Dim myHolidays As String ' other code If myLocation = "WHUK" Then myHolidays = Names("Hols_WHIL").RefersTo ElseIf myLocation = "WHPHL" Then myHolidays = Names("Hols_PH").RefersTo End If ' other code
07-03-2011, 11:34 PM
surya prakash

Paul,
thanks a ton; will revert shortly...

Quote:

Originally Posted by Paul_Hossler

Couple of things I see

1. In the test sub and in the Function definition, 'myLocation' is a string, and there is no named range called 'myLocation' so Range("myLocation') won't work

2. Hols_WHIL and Hols_WHUAE have #REF errors

Code:

Sub test() MsgBox WorkingHours3("WHUK", #6/1/2011#, 8, 8, 4, 8, 5, 0, 0) End Sub Public Function WorkingHours3(ByVal myLocation As String, 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 myDateLoop As Long, myHolidayHours As Long Dim myHoursWorked As Double Dim myHolidays As Range myHoursWorked = 0# If Range("myLocation").Value = "WHUK" Then myHolidays = Range("Hols_WHUK")

3. Since myHolidays is Dim-ed as a range object you need to use Set

Code:

Set myHolidays = Range("Hols_WHUK")

4. On sheet 'Working hrs calc', B4 and B5 you have UK and PHL, but that's not one of the cases in the If/Then/Elseif so it keeps on going

5. Suggest you have a final 'Else' to make a error condition

Code:

If myLocation = "WHUK" Then ' tested as string Set myHolidays = Range("Hols_WHUK") ' added Set ElseIf myLocation = "WHIL" Then Set myHolidays = Range("Hols_WHIL") ' range is not defined ElseIf myLocation = "WHUAE" Then Set myHolidays = Range("Hols_WHUAE") ' range is not defined ElseIf myLocation = "WHPHL" Then Set myHolidays = Range("Hols_WHPH") Else ' added a error catcher MsgBox "bad parameter" WorkingHours3 = -999 Exit Function End If

Paul
07-04-2011, 01:20 AM
surya prakash

Many many thanks a TON Paul. Works just fine...

Thank you everyone for responses..

surya