# Thread: MS Excel Downtime Calculator (2003)

1. ## 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

2. ## 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.

3. ## Re: MS Excel Downtime Calculator (2003)

Hi Hans,

Pls. find the attached Sample Sheet. Break timings are deducted from the downtime when they fall in the downtime range.

Regards,
Ankit

4. ## 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
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
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.

5. ## 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

6. ## 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.

7. ## Re: MS Excel Downtime Calculator (2003)

Yes, I got it .....

Regards,
Ankit

8. ## Re: MS Excel Downtime Calculator (2003)

<P ID="nt"><font size=-1>(No Text)</font>

hi.

10. ## Re: MS Excel Downtime Calculator (2003)

Excuse me? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

11. ## 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

12. ## Re: MS Excel Downtime Calculator (2003)

Still no attachment. Maximum size is 100 KB. Zip the workbook if it is too large.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•