Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to calculate difference between two date & time along with working hours and days
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 ?.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate difference between two date & time along with working hours and days
On Thu, 13 Sep 2012 11:13:49 +0000, tarpan9444
wrote: 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/te...030008309.aspx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate difference between two date & time along withworking hours and days
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help - Time Difference in Days, Hours Mins & Seconds | Excel Programming | |||
calculate difference in time spanning a day, during office hours o | Excel Programming | |||
Calculate difference in time spanning a day, during office hours o | Excel Discussion (Misc queries) | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
How do I calculate difference in days & hours between two dates e. | Excel Worksheet Functions |