How to calculate difference between two date & time along with working hours and days

[tarpan9444]

Hi Champs,

I have following requirement.
I want to calculate difference between two date & time considerin
working days and hours of an organization.

example:

1) Problem logged on : 10.08.2012 11:00:00
2) Problem solved on : 13.08.2012 16:00:00

I need to calculate number of hours taken to solve the problem where m
organization's working time is 9.30 to 6.30 (Monday to Friday), 9.30 t
2.00 (Saturday) and Sunday is holiday.

My expected result should be : 24 hours

Can any one suggest me which formula should I use to get the correc
result ?.

 

[CellShocked]

Hi Champs,

I have following requirement.
I want to calculate difference between two date & time considering
working days and hours of an organization.

example:

1) Problem logged on : 10.08.2012 11:00:00
2) Problem solved on : 13.08.2012 16:00:00

I need to calculate number of hours taken to solve the problem where my
organization's working time is 9.30 to 6.30 (Monday to Friday), 9.30 to
2.00 (Saturday) and Sunday is holiday.

My expected result should be : 24 hours

Can any one suggest me which formula should I use to get the correct
result ?..

http://office.microsoft.com/en-us/t...-expenditure-log-job-tracker-TC030008309.aspx

 

[plinius]

Il 13/09/2012 13:13, tarpan9444 ha scritto:

Hi Champs,

I have following requirement.
I want to calculate difference between two date & time considering
working days and hours of an organization.

example:

1) Problem logged on : 10.08.2012 11:00:00
2) Problem solved on : 13.08.2012 16:00:00

I need to calculate number of hours taken to solve the problem where my
organization's working time is 9.30 to 6.30 (Monday to Friday), 9.30 to
2.00 (Saturday) and Sunday is holiday.

My expected result should be : 24 hours

Can any one suggest me which formula should I use to get the correct
result ?..

Try this UDF:
----------------------
Function TimeCalc(TBeg As Date, TEnd As Date) As Single
Dim WorkBeg(1 To 7) As Date, WorkEnd(1 To 7) As Date
Dim i As Long, b As Single, e As Single
If TBeg >= TEnd Then Exit Function
WorkBeg(1) = #12:00:00 AM#: WorkEnd(1) = #12:00:00 AM#
For i = 2 To 6
WorkBeg(i) = #9:30:00 AM#: WorkEnd(i) = #6:30:00 PM#
Next
WorkBeg(7) = #9:30:00 AM#: WorkEnd(7) = #2:00:00 PM#
For i = Int(TBeg) To Int(TEnd)
If i = Int(TBeg) Then b = TBeg - Int(TBeg) Else b = 0
If i = Int(TEnd) Then e = TEnd - Int(TEnd) Else e = 0.99999
b = WorksheetFunction.Max(WorkBeg(Weekday(i)), b)
e = WorksheetFunction.Min(WorkEnd(Weekday(i)), e)
TimeCalc = TimeCalc + (e - b)
Next
End Function
----------------------
If start-time is in A1 and end-time in B2, put in C2
=TimeCalc(A2, B2)

Hi,
E.