1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: function (Excel03)

Thank you Steve I will do as you suggest. Fay

9. ## 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. ## Re: function (Excel03)

Thank you. Fay

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