Hi, I need help and I hope someone is clever enough to un-jumble what I've written below and then help me sort out what I need ![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I've created a time sheet for my work. Within the time sheet I have created an IF formula to work out how many hours the person is supposed to work each day so that they can claim the difference as toil. Can someone please help me create a message box which will appear on each line as they enter their end of day time, to alert them that they have worked more or less hours than expected and need to claim the time?
My sheet is set up as follows: Title worksheet is Timesheet, range A7:U24
Column:
A: Day
B: Date (auto populated from lookup on separate tab)
C: Start Time
D: Start of Break 1 Time
E: Formula to calculate time between start of break 1, and start time.
F: Finish Time of Break 1
G: Formula to calculate total time of break 1
H: Start Break 2 Time
I: Formula to calculate the time between start break 2 and finish of break 1
J. Finish Break 2 Time
K. Finish Time for the day
L: Formula to calculate single break day to finish time
M: Formula to calculate time of break 2
N: Formula to calculate end of second break to end of day time
O: Formula ISText calculate total hours of day if 2 breaks were taken
P: Formula ISTEXT calculate total hours of single break day
Q: Formula ISTEXT calculate total hours worked if no break was taken
R: Daily Total : Formula lookup how many breaks and return appropriate value
S: Expected Hours: Formula - lookup employees name return expected hours from tab 2
T: Expected Hours: as Text (Wasn't sure if this is why my formula wasn't working so added in)
U: Claimable hours: Formula to calculate difference between expected and daily totals.
The columns with blue writing are all hidden.
The code I was previously advised to use is:
Private Sub Worksheet_Chane(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub
If Not Intersect(Target, Range("D:D) Is Nothing Then
If Target.Value > Target.Offset(0, -2).Value Then
MsgBox "You have exceeded the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
ElseIf Target.Value < Target.Offset(0, -2).Value Then
MsgBox "You are short of the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
End If
End If
End Sub
It's not working for me. If someone could please please please help me get it running that would be great!
I've created a time sheet for my work. Within the time sheet I have created an IF formula to work out how many hours the person is supposed to work each day so that they can claim the difference as toil. Can someone please help me create a message box which will appear on each line as they enter their end of day time, to alert them that they have worked more or less hours than expected and need to claim the time?
My sheet is set up as follows: Title worksheet is Timesheet, range A7:U24
Column:
A: Day
B: Date (auto populated from lookup on separate tab)
C: Start Time
D: Start of Break 1 Time
E: Formula to calculate time between start of break 1, and start time.
F: Finish Time of Break 1
G: Formula to calculate total time of break 1
H: Start Break 2 Time
I: Formula to calculate the time between start break 2 and finish of break 1
J. Finish Break 2 Time
K. Finish Time for the day
L: Formula to calculate single break day to finish time
M: Formula to calculate time of break 2
N: Formula to calculate end of second break to end of day time
O: Formula ISText calculate total hours of day if 2 breaks were taken
P: Formula ISTEXT calculate total hours of single break day
Q: Formula ISTEXT calculate total hours worked if no break was taken
R: Daily Total : Formula lookup how many breaks and return appropriate value
S: Expected Hours: Formula - lookup employees name return expected hours from tab 2
T: Expected Hours: as Text (Wasn't sure if this is why my formula wasn't working so added in)
U: Claimable hours: Formula to calculate difference between expected and daily totals.
The columns with blue writing are all hidden.
The code I was previously advised to use is:
Private Sub Worksheet_Chane(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub
If Not Intersect(Target, Range("D:D) Is Nothing Then
If Target.Value > Target.Offset(0, -2).Value Then
MsgBox "You have exceeded the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
ElseIf Target.Value < Target.Offset(0, -2).Value Then
MsgBox "You are short of the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
End If
End If
End Sub
It's not working for me. If someone could please please please help me get it running that would be great!