PDA

View Full Version : Calculating Working hours in a Week



surya prakash
06-01-2011, 05:26 AM
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
06-01-2011, 06:36 AM
This is a bit brute force


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

surya prakash
06-01-2011, 07:10 AM
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
06-01-2011, 07:22 AM
Paul, many thanks; issue resolved...

Will revert back shortly...

thanks again... :)

abishekmouli
06-01-2011, 02:16 PM
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
06-01-2011, 02:27 PM
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
06-01-2011, 02:32 PM
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
06-01-2011, 02:39 PM
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
06-01-2011, 04:05 PM
added code to use a worksheet list of holidays

Paul

Chabu
06-02-2011, 02:25 AM
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
06-08-2011, 04:44 AM
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
06-08-2011, 04:49 AM
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
06-15-2011, 10:00 PM
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
06-16-2011, 07:06 AM
You just need the 'Set' to assign an object (i.e. the Range) to an object variable


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

surya prakash
06-16-2011, 09:48 PM
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
06-17-2011, 05:54 AM
The Range part is easy (sub test below)

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


Paul

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)

surya prakash
06-17-2011, 09:57 PM
That was superb Paul; thanks very much indeed....

surya prakash
06-27-2011, 02:41 AM
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
06-27-2011, 04:58 AM
try this, assuming that myLocation = a cell named 'myLocation'


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

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

End If



Paul

surya prakash
06-28-2011, 01:37 AM
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'


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

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

End If


Paul

frank_m
06-28-2011, 05:49 AM
A stab in the dark
' 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

CatDaddy
06-28-2011, 11:52 AM
why dont you just declare your ranges in the sub to avoid confusion?


dim Hols_WHIL As Range
set Hols_WHIL = Range("...somerangevalues...")

If myLocation = "WHUK" Then
myHolidays = Hols_WHIL

Paul_Hossler
06-28-2011, 06:21 PM
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


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


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


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

surya prakash
07-03-2011, 11:24 PM
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


A stab in the dark
' 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

surya prakash
07-03-2011, 11:34 PM
Paul,
thanks a ton; will revert shortly...


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


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


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


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

surya prakash
07-04-2011, 01:20 AM
Many many thanks a TON Paul. Works just fine...

Thank you everyone for responses..

surya




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


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


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


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