Results 1 to 12 of 12

20081017, 14:55 #1
 Join Date
 Apr 2008
 Posts
 75
 Thanks
 2
 Thanked 0 Times in 0 Posts
MS Excel Downtime Calculator (2003)
Hi,
I am working in a firm wherein I use to enter downtime in an excel sheet for team members.
I have a sheet in which I enter no. of downtime hours for an employee.
Also, every employee has 3 breaks in a 9 hours shift. Break Timings are fixed for every employee.
Let us take an example of employee Ankit:
My shift starts at 6:30 P.M and ends at 3:30 A.M. My break timings are at 1st break at 7:15 PM for 15 Mins, 2nd break at 10:30 PM for 30 Mins and last one at 12:00AM for 15 Mins.
Now, Due to some reasons My cab is Late and I reached my office at 7:30. Total no. of downtime hours is 1 hour. But as my break falls at 7:15 therefore my net downtime is 45 Mins.
At 10:00PM , my system suddenly stops and it will again work at 11:30. total no. of downtime is 1 hour and 30 mins. But as usual my 2nd break falls in this interval. Therefore my net downtime is 1 hour.
Total downtime for the day is 45 mins + 1 hour = 1 hour and 45 Mins.
Now I want a sheet in excel which should have a column where I can enter the names of employees,
in the 2nd, 3rd 4th,5th, 6th , 7th column( assuming that downtime may occur several times in a shift) I can enter the timings of downtime in 24 hour format,
8th and 9th for 1st break timings, 10th and 11th column for 2nd break and
12&13th column for 3rd break( Break timings I'll specify in those columns)
and lastly in the last column....Net downtime as per the calculation explained above.
I am doing this from the last 6 months but it consumes so much of time....doing calculations in excel.
I want a format for this work. If anyone can really suggest me something or provide a sheet like that I'll be very obliged to him/her.
Thanks and Regards,
Ankit

20081017, 19:42 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: MS Excel Downtime Calculator (2003)
Could you post a small sample workbook with some (dummy) data that we can use to try out ideas on? Thank you.

20081018, 02:56 #3
 Join Date
 Apr 2008
 Posts
 75
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: MS Excel Downtime Calculator (2003)
Hi Hans,
Thanks for your prompt reply.
Pls. find the attached Sample Sheet. Break timings are deducted from the downtime when they fall in the downtime range.
Regards,
Ankit

20081018, 04:06 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: MS Excel Downtime Calculator (2003)
The attached version contains a module with a custom function
<code>
Function NetDownTime(oCell As Range) As Date
Dim r As Long
Dim d As Integer
Dim b As Integer
Dim dtmShiftStart As Date
Dim dtmDownStart As Date
Dim dtmDownEnd As Date
Dim dtmBreakStart As Date
Dim dtmBreakEnd As Date
Dim dtmOverlapStart As Date
Dim dtmOverlapEnd As Date
Dim dtmDuration As Date
r = oCell.Row
dtmShiftStart = Cells(r, 2)
' Loop through downtime periods
For d = 4 To 8 Step 2
' Get out if no more downtimes
If Cells(r, d) = "" Then
Exit For
Else
' Adjust times after midnight
dtmDownStart = Cells(r, d)
If dtmDownStart < dtmShiftStart Then
dtmDownStart = dtmDownStart + 1
End If
dtmDownEnd = Cells(r, d + 1)
If dtmDownEnd < dtmShiftStart Then
dtmDownEnd = dtmDownEnd + 1
End If
' Raw duration
dtmDuration = dtmDuration + dtmDownEnd  dtmDownStart
' Loop through the break periods
For b = 10 To 14 Step 2
' Adjust times after midnight
dtmBreakStart = Cells(r,
If dtmBreakStart < dtmShiftStart Then
dtmBreakStart = dtmBreakStart + 1
End If
dtmBreakEnd = Cells(r, b + 1)
If dtmBreakEnd < dtmShiftStart Then
dtmBreakEnd = dtmBreakEnd + 1
End If
dtmOverlapStart = Application.Max(dtmDownStart, dtmBreakStart)
dtmOverlapEnd = Application.Min(dtmDownEnd, dtmBreakEnd)
' If break overlaps downtime, subtract overlap
If dtmOverlapEnd > dtmOverlapStart Then
dtmDuration = dtmDuration  (dtmOverlapEnd  dtmOverlapStart)
End If
Next b
End If
Next d
NetDownTime = dtmDuration
End Function
</code>
In one instance the function calculates a different net downtime than you indicated; I think you forgot to subtract one of the breaks.

20081018, 13:39 #5
 Join Date
 Apr 2008
 Posts
 75
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: MS Excel Downtime Calculator (2003)
Hi Hans,
Thanks a lot for your help.
Well, one concern only, when I enter a new value in the downtime or in the break I have to paste that =newdowntime() formula again ( so that it picks up new value). Its not automatically picking up new changes in the cell as all the formulas normally does.......
Thanks and Regards,
Ankit

20081018, 20:34 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: MS Excel Downtime Calculator (2003)
If you enter the formula in row 3 as =NetDowntime(B3:P3) and fill down, Excel will automatically recalculate it when you enter, change or delete data.
Note: the calculated downtime will be incorrect when you have entered a start time but not the corresponding end time; the code doesn't take that into account. As soon as you enter the end time, the calculation will be correct again.

20081019, 03:34 #7
 Join Date
 Apr 2008
 Posts
 75
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: MS Excel Downtime Calculator (2003)
Yes, I got it .....
Thanks for your whole heartedly help and unmatchable advise.
Regards,
Ankit

20081019, 18:36 #8
 Join Date
 Apr 2008
 Posts
 75
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: MS Excel Downtime Calculator (2003)
<P ID="nt"><font size=1>(No Text)</font>

20081019, 18:37 #9
 Join Date
 Apr 2008
 Posts
 75
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: MS Excel Downtime Calculator (2003)
hi.

20081019, 18:46 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: MS Excel Downtime Calculator (2003)
Excuse me? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20081019, 20:22 #11
 Join Date
 Apr 2008
 Posts
 75
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: MS Excel Downtime Calculator (2003)
Hi,
Sorry to disturb you ...Actually I was just attaching a document in the previous two messages and it wasn't attached .....
Anyways, This is my document again...No queries for this document....Just attaching this for my further use.....
Thanks and Regards,
ANkit

20081019, 20:28 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: MS Excel Downtime Calculator (2003)
Still no attachment. Maximum size is 100 KB. Zip the workbook if it is too large.