Automated Rotating Overtime Rosta

[bazza778]

Hi Everyone, im a newbie at excel and would really appreciate your help with a Overtime rosta im creating, thanks.

Im trying to create an overtime rosta at work that will drop a name on the overtime list to the bottom of the list at the end of the day the overtime was worked. see below...

Name Date Job Accepted
Joe 14 Mar SST Yes
Steve 16 March SMD Yes
Matt 17 March NN Yes
John 20 March ST yes

So what I need is for the name Joe to drop to the position under the name John at around 11:59m on the 14th March, is this in any way possible

Thanks again

Barry

 

[cb12]

You could try something like...

Sub overtime()
Dim j, k As Integer
Dim i

    i = ActiveCell.Value
    j = Selection.CurrentRegion.Rows.Count
    
    For k = 1 To j - 1
        If ActiveCell.Offset(0, 1).Value = Date Then
            ActiveCell.EntireRow.Copy Destination:=Range("A" & j + 1)
            ActiveCell.EntireRow.Delete
        End If
    Next k

End Sub

This assumes your data starts in column A and that it is in the format presented above. Obviously adjust as necessary. To get the sheet to automatically calculate it at 23:59 use

Private Sub Workbook_Open()
     Application.OnTime TimeValue("23:59:00"), "overtime"
End Sub

again adjust as per your needs. Although remember, if you are setting it for the next day, you need to change = Date to < Date

HTH