Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    function (Excel03)

    I want to simplify figuring time cards. My situation is that I have three shifts to deal with. Workers normally work 8 to 12 hours shifts. I need to account for the number of hours worked on days, evening, and nights. Days 6:30 am to 3 pm, evenings 2:30 pm to 11 pm, nights 11 to 7.

    Scenario: person works 1 pm to 2 am

    Question if I put the in and out times is there a function I can used to automatically figure that the person worked 2 hours on days, 8 hours on evenings etc? What function do I use?

    Thank you for your help. Fay

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

    Re: function (Excel03)

    There is overlap between the day and evening shifts, and between the night and day shifts. How do we determine to which shift time worked during the overlap should be assigned?

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function (Excel03)

    That is a question that I need to find an answer to, I have just gotten
    involved with this. I haven't worked with the time cards before. It may
    depend on the starting shift for example you started a 1 pm so you started
    on days, using the old numbers, this would mean days ends at 3 pm. so 2
    hours on days. Then 3 pm onward would be marked as evening for.

    For our purposes here lets use days 6:30 am to 2:30 pm, evenings 2:30 pm to
    11:30 pm, nights 11:30 pm to 6:30 am. I need a starting point. Thanks.

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

    Re: function (Excel03)

    I think you had better get it clear NOW. The calculations will become more complex, there is little sense in creating a temporary solution that will not work in thereal situation.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function (Excel03)

    Try this function. Use in a spreadsheet like:
    If A1 has the startdate/time, B1 the end date/time and C1 a string either "D", "E", or "N", the formula:

    =ShiftHours(A1,B1,C1)

    will give the hours for that particular shift

    I tried out several different cases, and the logic seems to be there, but my testing was not extensive. I would make some test cases and see if it holds true for all the scenarios. If it doesn't work, and you can provide what start and end date/times fail (what you want and what you get) I can try and rework some of the logic.

    Steve

    <pre>Option Explicit
    Function ShiftHours(dStartTime As Date, dEndtime As Date, sShift As String)
    Dim vShiftTimes(1 To 3, 1 To 3)
    Dim dStartDate As Date
    Dim dEndDate As Date
    Dim dShiftStart As Date
    Dim dShiftEnd As Date
    Dim iShift As Integer
    Dim iLastShift As Integer

    'Error checks
    'start>End
    If dStartTime > dEndtime Then
    ShiftHours = "Start>End"
    Exit Function
    End If
    'Get shift info or mark as invalid
    sShift = UCase(sShift)
    Select Case sShift
    Case "D"
    iShift = 1
    iLastShift = 3
    Case "N"
    iShift = 2
    iLastShift = 1
    Case "E"
    iShift = 3
    iLastShift = 2
    Case Else
    ShiftHours = "Shift is 'D'/'E'/'N'"
    Exit Function
    End Select

    'Fill in 3x3 shifttimes Array(Start/End/Designation for each)
    vShiftTimes(1, 1) = TimeValue("6:30 am")
    vShiftTimes(1, 2) = TimeValue("3:00 pm")
    vShiftTimes(1, 3) = "D"

    vShiftTimes(2, 1) = TimeValue("2:30 pm")
    vShiftTimes(2, 2) = TimeValue("11:00 pm")
    vShiftTimes(2, 3) = "E"

    vShiftTimes(3, 1) = TimeValue("11:00 pm")
    vShiftTimes(3, 2) = TimeValue("7:00 am")
    vShiftTimes(3, 3) = "N"

    dStartDate = Int(dStartTime)
    dEndDate = Int(dEndtime)

    'Start Day (always done)
    dShiftEnd = dStartDate + vShiftTimes(iShift, 2)
    If dEndtime < dShiftEnd Then _
    dShiftEnd = dEndtime
    dShiftStart = dStartTime
    If dShiftStart < (dStartDate + vShiftTimes(iLastShift, 2)) Then _
    dShiftStart = dStartDate + vShiftTimes(iLastShift, 2)


    ShiftHours = (dShiftEnd - dShiftStart) * 24

    'End Date different than StartDate
    If dEndDate > dStartDate Then
    dShiftEnd = dEndDate + vShiftTimes(iShift, 2)
    If dEndtime < dShiftEnd Then _
    dShiftEnd = dEndtime
    dShiftStart = dEndDate + vShiftTimes(iLastShift, 2)
    ShiftHours = ShiftHours + (dShiftEnd - dShiftStart) * 24
    End If
    'If multiple days (probably never done), but more generic
    If dEndDate - dStartDate > 1 Then
    ShiftHours = ShiftHours + _
    (dEndDate - dStartDate - 1) * 24 * _
    (vShiftTimes(iShift, 2) - vShiftTimes(iLastShift, 2))
    End If
    End Function</pre>


  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function (Excel03)

    Hans I understand the need to know what is real. My goal with the original question was to find out if there was a function that would allow me to do what I wanted to do. You indicated a need for more information and I can live with that. Sorry to make you grumpy.

    sdckapr Thank you for your input. It will take me awhile to look this over. You are taking me where I haven't been before. I appreciate this.

    Fay

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function (Excel03)

    I would suggest, before you try to wade thru the function, that you test it thoroughly and make sure that it works.

    Once you are confident that it works, try to:
    1) understand what the function does
    2) understand the logic used to arrive at the answer

    Once it works if you have any questions on the above, post a followup and we will try to answer the questions.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function (Excel03)

    Thank you Steve I will do as you suggest. Fay

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function (Excel03)

    Don't look too hard.

    I was looking at it again to do some more checking and I found some major flaws in the logic.

    I will relook at it when I get the chance and will post back with something better.

    Sorry to get your hopes up.

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function (Excel03)

    Thank you. Fay

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function (Excel03)

    Try this new (longer version) that I think gets most of the problems.
    Again check it out to see if there are any bugs that need working out. I don't know if it can be simplified, but I will think about it if I can find some more time.

    FYI, I speculate that you have overlap, since the shifts need to communicate the issues to the new shift. I don't understand why evenings don't start at 10:30PM however.

    Steve

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function (Excel03)

    Okay Steve I will admit I have never used VBA in Excel. I do know how to access VBA. But I have no idea what to do with what you sent me. I would hate not to use all the work you went to.

    Fay

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function (Excel03)

    Add the function to a module in VB:
    Open VB (Alt-F11)
    Insert -Module
    Copy the code from the file and paste into the pane.
    Close VB (Alt-q)

    I imagined a setup (change as appropriate)
    Row 1 Headers:
    A1: "Start Date/Time"
    B1: "Stasrt Date/Time"
    C1: "D"
    D1: "N"
    E1: "E"

    Then in A2 to Awhatever enter the starting date and times
    In B2 to Bwhatever enter the ending date and times

    In C2 enter the formula:
    =ShiftHours($A1,$B1,C$1)

    Copy this to formula to D2:E2
    Then Copy C2:E2 down as many rows as needed

    For each time range in col A and B it will give you the hours worked for each period.

    Steve

    will give the hours for that particular shift

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function (Excel03)

    Thank you and Bless you Steve. I will put this to the test when I get to work. Fay

Posting Permissions

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