Leave Database Time Tracking to equal full day after 8 hours

[Roderique]

Good day,

I trust all is well.

I have a excel leave tracking database for employees with two sheets named:
Sheet 1. Calendar Months
Sheet 2. Leave Time Tracker

I have the following information in Sheet 1 'Calendar Months':
- The Month in Cell B3
- The Year in Cell E3
- The employees names in Range C6:C14 (named: A, B, C, D, E, F, G, H, I)
- The dates (01 to 31) in Range F6:AJ6
- The Leave category Late is "L" that I can be selected in the dates range (calendar) for all employees.

I am looking to do the following in Sheet 2 'Leave Time Tracker':
- When I select the "L" in sheet 1 the employee name should automatically formulate to sheet 2 from Cell A4 all the way down under the heading 'Employee Name' in Cell A3
- The "L" selection in the calendar should also automate the date in sheet 2 from Cell B4 all the way down under the heading 'Date of Late Arrival' in Cell B3.

*The automated inputs needs to be inserted in new rows each time and not alphabetically order itself. Example, Employee named A is late on 01 Jan 2025, Employee B is late on 05 Jan 2025, but when Employee named A is late again on 22 Jan 2025 it should go after Employee B that was selected before that.

Can someone please assist me with formulas I can use? Your further assistance will be highly appreciated

 

[Asbestos_Jen]

I can't really picture your layout from your description. I think you'll likely need a VBA solution, if I understood your request correctly.
Could you attach some example data, including expected results using XL2BB?

 

[NoSparks]

- The dates (01 to 31) in Range F6:AJ6

Are these real dates formatted "dd" or are they just numbers?

 

[Roderique]

Are these real dates formatted "dd" or are they just numbers?

Hi. It is formatted

 

[NoSparks]

Don't know what the title of this thread has to do with what you've asked for
but none the less....
Right click on the Calendar Months tab and select View code
the VBA window will open, paste this into the editor on the right.

Private Sub Worksheet_Change(ByVal Target As Range)
    'limit to one cell changing at a time
    If Target.CountLarge > 1 Then Exit Sub
        
    Dim src As Worksheet: Set src = Sheets("Calendar Months")
    Dim dest As Worksheet: Set dest = Sheets("Leave Time Tracker")
    Dim lastRow As Long, writeRow As Long
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    If Not Intersect(Target, Range("F7:AJ" & lastRow)) Is Nothing Then
        If UCase(Target.Value) = "L" Then
            With dest
                writeRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
                .Cells(writeRow, "A") = src.Cells(Target.Row, 1)
                .Cells(writeRow, "B") = src.Cells(6, Target.Column)
                .Cells(writeRow, "B").NumberFormat = "d/m/yyyy"
            End With
        End If
    End If

End Sub

Goto the Calendar Months sheet and try an L entry.

 

[Roderique]

Don't know what the title of this thread has to do with what you've asked for
but none the less....
Right click on the Calendar Months tab and select View code
the VBA window will open, paste this into the editor on the right.

Private Sub Worksheet_Change(ByVal Target As Range)
    'limit to one cell changing at a time
    If Target.CountLarge > 1 Then Exit Sub
       
    Dim src As Worksheet: Set src = Sheets("Calendar Months")
    Dim dest As Worksheet: Set dest = Sheets("Leave Time Tracker")
    Dim lastRow As Long, writeRow As Long
   
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    If Not Intersect(Target, Range("F7:AJ" & lastRow)) Is Nothing Then
        If UCase(Target.Value) = "L" Then
            With dest
                writeRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
                .Cells(writeRow, "A") = src.Cells(Target.Row, 1)
                .Cells(writeRow, "B") = src.Cells(6, Target.Column)
                .Cells(writeRow, "B").NumberFormat = "d/m/yyyy"
            End With
        End If
    End If

End Sub

Goto the Calendar Months sheet and try an L entry.

Thank you. I will add other columns to add all late time until it gets to 8 hours.

Is VBA the only option? Our office uses a system called MyContent where the excel document will be uploaded for all employees to acces. The document will open in Excel online.

 

[NoSparks]

Is VBA the only option?

as far as I know it is