Calculate Elapsed Time

[MissyEd]

Hi

I need to calculate how long a job took to do in Excel. Our working hours are 08:30 to 17:00 and we dont normally work weekends. I need to take into account bank/public holidays and overtime e.g. if we worked outside normal hours on that day to catch up.

Can anyone point me at an add-in or function that could do this. I was planning to fill each bank/public holiday into a seperate sheet on the same workbook, but Im crap at the formulae's. Im also willing to pay a reasonable amount for an add-in as life is too short Missy Ed - Bolton, UK

 

[danomaniac]

Missy,
Are you going to have multiple categories for outside normal hours? i.e, Holiday pay, weekend/O.T? or just Normal vs Not Normal? I've done something similar...let me dig it up and I'll post it...

 

[MissyEd]

Hmm, probably multiple. Im looking at a helpdesk system if thats any help. Missy Ed - Bolton, UK

 

[danomaniac]

Okay Missy. I've dug it out.. Here goes.

Copy the following into a blank code module...

Public Function ThanksgivingDate(Yr As Integer) As Date
ThanksgivingDate = DateSerial(Yr, 11, 29 - _
Weekday(DateSerial(Yr, 11, 1), vbFriday))
End Function

Public Function NDow(Y As Integer, M As Integer, _
N As Integer, DOW As Integer) As Date

NDow = DateSerial(Y, M, (8 - Weekday(DateSerial(Y, M, 1), _
(DOW + 1) Mod 8)) + ((N - 1) * 7))

End Function

Public Function DOWsInMonth(Yr As Integer, M As Integer, _
DOW As Integer) As Integer

On Error GoTo EndFunction

Dim I As Integer
Dim Lim As Integer
Lim = Day(DateSerial(Yr, M + 1, 0))
DOWsInMonth = 0
For I = 1 To Lim
If Weekday(DateSerial(Yr, M, I)) = DOW Then
DOWsInMonth = DOWsInMonth + 1
End If
Next I
Exit Function
EndFunction:
DOWsInMonth = 0
End Function


'Stop Copying Here - End of code Module

Then, on a blank worksheet, enter the current year in a given cell, i.e, B2.

Now, Starting in column A row 4 and working down, enter the names of the holidays, i.e, "New Years day" in Cell A4, "Martin Luther King" day in Cell A5, "Easter" is cell A6, etc.

Then in column B starting in row 4, enter the following formulae:

New Year's Day: =DATE(YEAR(NOW()),1,1)

Martin Luther King Day: =NDow(B2,1,3,2)
(B2 = Cell Ref. with Year, 1 = Month, 3 = 3rd, 2 = Tuesday)

Easter: =FLOOR("5/"&DAY(MINUTE(B2/38)/2+56)&"/"&B2,7)-34

Memorial Day: =NDow(B2,5,DOWsInMonth(B2,5,2),2)
(actually calls two functions)

4th of July: =DATE(YEAR(NOW()),7,4)

Labor Day: =NDow(B2,9,1,2)

Thanksgiving: =Thanksgivingdate(B2)

Christmas: =DATE(YEAR(NOW()),12,25)

Any Floating Holiday: =NDow(B2,6,2,3)
(2nd Tuesday in June)

weekends are simple. =WEEKDAY(5/10/03)
This returns a 1, 1=Sunday, 2=Monday, etc.
You can also reference a cell, i.e, =WEEKDAY(B4)



If you'd like, I can throw it together in a spreadsheet for you and email it.

 

[danomaniac]

I just realized you're in the UK. Sorry, some of those holidays probably don't mean much to you. Hopefully, you can manipulate the formulae enough to get what you need. If not, let me know any specific holidays and I'll write a formula.

Dan

 

[MissyEd]

Thanks for the help. I will try this out and get back to you tommorow Missy Ed - Bolton, UK