Results 1 to 12 of 12
  1. #1
    Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Star Lounger
    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
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.
    Attached Files Attached Files

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    Star Lounger
    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

  8. #8
    Star Lounger
    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>

  9. #9
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: MS Excel Downtime Calculator (2003)

    hi.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Excel Downtime Calculator (2003)

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

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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •