Validation of Employee Timesheet

[RoRo123]

Errors with Hrs. entry by employees. Occasional keying errors such as colons
(8:00), commas (8,00), etc. How do I set up a validation that eliminates all
possible error entries other than regular hr. entries with 2 decimal places,
and gives a pop-up error message? We are using Excel 2003.

 

[JLatham]

Well, I don't know about eliminating all possible error entries since humans
seem to have an almost infinite capacity for screwing up a typed entry, but...
This code will test entries and replace commas and colons with a period
which should help most of the time.

To put the code to work, open your workbook, go to the time entering sheet
and right-click on its name tab and choose [View Code] from the list that
appears. Copy the code below and paste it into the code module presented to
you. Make any change needed to get it to work in the correct column(s).
Close the VB Editor and give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in a column and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3 ' column C
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
'
'if you wish to check multiple columns, then
'just change the initial Case Is = statement to
'include all column numbers, as this example
'for columns C, G and R
' Case Is = 3, 7, 18

End Sub

 

[RoRo123]

Sorry. I guess stating "all" possible errors was a bit of a stretch. How
will the code change knowing that the applicable cells are C3:C369, D3369
and E3:E369. Also, will it affect any Locked cells?
--
RGS

Well, I don't know about eliminating all possible error entries since humans
seem to have an almost infinite capacity for screwing up a typed entry, but...
This code will test entries and replace commas and colons with a period
which should help most of the time.

To put the code to work, open your workbook, go to the time entering sheet
and right-click on its name tab and choose [View Code] from the list that
appears. Copy the code below and paste it into the code module presented to
you. Make any change needed to get it to work in the correct column(s).
Close the VB Editor and give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in a column and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3 ' column C
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
'
'if you wish to check multiple columns, then
'just change the initial Case Is = statement to
'include all column numbers, as this example
'for columns C, G and R
' Case Is = 3, 7, 18

End Sub

Errors with Hrs. entry by employees. Occasional keying errors such as colons
(8:00), commas (8,00), etc. How do I set up a validation that eliminates all
possible error entries other than regular hr. entries with 2 decimal places,
and gives a pop-up error message? We are using Excel 2003.

 

[T. Valko]

You could try Data Validation.

In Excel times are stored as the fractional part of a day. A day has a value
of 1 so time is the fractional portion of 1.

For example, 12:00 PM is the midpoint of the day. So, 12:00 PM is half of a
day and Excel stores 12:00 PM as the decimal value 0.5 (1/2 of 1).

12:00 AM = decimal value 0
11:59:59 PM = decimal value 0.999988425925926

So, to test that a valid time has been entered you just have to make sure
the entry is >=0 and <1.

the applicable cells are C3:C369, D3369 and E3:E369.

Select the *entire* range C3:E369 starting from cell C3.
Goto the menu Data>Validation
Allow: Custom
Formula: =AND(C3>=0,C3<1)

You can set custom input/alert messages. Click the appropriate tabs and fill
in the desired info.

OK out

--
Biff
Microsoft Excel MVP

Sorry. I guess stating "all" possible errors was a bit of a stretch.
How
will the code change knowing that the applicable cells are C3:C369,
D3369
and E3:E369. Also, will it affect any Locked cells?
--
RGS

Well, I don't know about eliminating all possible error entries since
humans
seem to have an almost infinite capacity for screwing up a typed entry,
but...
This code will test entries and replace commas and colons with a period
which should help most of the time.

To put the code to work, open your workbook, go to the time entering
sheet
and right-click on its name tab and choose [View Code] from the list that
appears. Copy the code below and paste it into the code module presented
to
you. Make any change needed to get it to work in the correct column(s).
Close the VB Editor and give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in a column and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3 ' column C
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
'
'if you wish to check multiple columns, then
'just change the initial Case Is = statement to
'include all column numbers, as this example
'for columns C, G and R
' Case Is = 3, 7, 18

End Sub

Errors with Hrs. entry by employees. Occasional keying errors such as
colons
(8:00), commas (8,00), etc. How do I set up a validation that
eliminates all
possible error entries other than regular hr. entries with 2 decimal
places,
and gives a pop-up error message? We are using Excel 2003.

 

[JLatham]

Excellent! I'd thought about data validation initially, but my brain went
numb regarding how time is stored (smack's forehead sharply) and so I went
with the VBA solution.

RoRo123 - I'd go with T.Valko's solution, much cheaper than mine to
implement, plus using Data Validation gives you the opportunity to define an
input prompt and an error message to be displayed which you'd asked for
initially anyhow.

You could try Data Validation.

In Excel times are stored as the fractional part of a day. A day has a value
of 1 so time is the fractional portion of 1.

For example, 12:00 PM is the midpoint of the day. So, 12:00 PM is half of a
day and Excel stores 12:00 PM as the decimal value 0.5 (1/2 of 1).

12:00 AM = decimal value 0
11:59:59 PM = decimal value 0.999988425925926

So, to test that a valid time has been entered you just have to make sure
the entry is >=0 and <1.

the applicable cells are C3:C369, D3369 and E3:E369.

Select the *entire* range C3:E369 starting from cell C3.
Goto the menu Data>Validation
Allow: Custom
Formula: =AND(C3>=0,C3<1)

You can set custom input/alert messages. Click the appropriate tabs and fill
in the desired info.

OK out

--
Biff
Microsoft Excel MVP

Sorry. I guess stating "all" possible errors was a bit of a stretch.
How
will the code change knowing that the applicable cells are C3:C369,
D3369
and E3:E369. Also, will it affect any Locked cells?
--
RGS

Well, I don't know about eliminating all possible error entries since
humans
seem to have an almost infinite capacity for screwing up a typed entry,
but...
This code will test entries and replace commas and colons with a period
which should help most of the time.

To put the code to work, open your workbook, go to the time entering
sheet
and right-click on its name tab and choose [View Code] from the list that
appears. Copy the code below and paste it into the code module presented
to
you. Make any change needed to get it to work in the correct column(s).
Close the VB Editor and give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in a column and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3 ' column C
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
'
'if you wish to check multiple columns, then
'just change the initial Case Is = statement to
'include all column numbers, as this example
'for columns C, G and R
' Case Is = 3, 7, 18

End Sub

:

Errors with Hrs. entry by employees. Occasional keying errors such as
colons
(8:00), commas (8,00), etc. How do I set up a validation that
eliminates all
possible error entries other than regular hr. entries with 2 decimal
places,
and gives a pop-up error message? We are using Excel 2003.

.

 

[JLatham]

OOPS! I went back and read the OP again, it appears that he DOES NOT want
entries to appear as time, h:mm, that type of entry looks like one that needs
to be corrected. It appears that he's expecting times to be entered as hours
and portions of hours worked vs hours:minutes. So we're kind of back to my
VBA code if that's true. The reason I used the .Text property in the code
was because of h:mm entries being automatically converted to times even
though internally the .Value is a fraction. That's also the reason I forced
the cell's format to "General" in the code.

In either case, it would appear that he should now have a solution, either
mine or yours, depending on which really works the way he needs it to.

You could try Data Validation.

In Excel times are stored as the fractional part of a day. A day has a value
of 1 so time is the fractional portion of 1.

For example, 12:00 PM is the midpoint of the day. So, 12:00 PM is half of a
day and Excel stores 12:00 PM as the decimal value 0.5 (1/2 of 1).

12:00 AM = decimal value 0
11:59:59 PM = decimal value 0.999988425925926

So, to test that a valid time has been entered you just have to make sure
the entry is >=0 and <1.

the applicable cells are C3:C369, D3369 and E3:E369.

Select the *entire* range C3:E369 starting from cell C3.
Goto the menu Data>Validation
Allow: Custom
Formula: =AND(C3>=0,C3<1)

You can set custom input/alert messages. Click the appropriate tabs and fill
in the desired info.

OK out

--
Biff
Microsoft Excel MVP

Sorry. I guess stating "all" possible errors was a bit of a stretch.
How
will the code change knowing that the applicable cells are C3:C369,
D3369
and E3:E369. Also, will it affect any Locked cells?
--
RGS

Well, I don't know about eliminating all possible error entries since
humans
seem to have an almost infinite capacity for screwing up a typed entry,
but...
This code will test entries and replace commas and colons with a period
which should help most of the time.

To put the code to work, open your workbook, go to the time entering
sheet
and right-click on its name tab and choose [View Code] from the list that
appears. Copy the code below and paste it into the code module presented
to
you. Make any change needed to get it to work in the correct column(s).
Close the VB Editor and give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in a column and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3 ' column C
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
'
'if you wish to check multiple columns, then
'just change the initial Case Is = statement to
'include all column numbers, as this example
'for columns C, G and R
' Case Is = 3, 7, 18

End Sub

:

Errors with Hrs. entry by employees. Occasional keying errors such as
colons
(8:00), commas (8,00), etc. How do I set up a validation that
eliminates all
possible error entries other than regular hr. entries with 2 decimal
places,
and gives a pop-up error message? We are using Excel 2003.

.

 

[JLatham]

On the offchance that you do end up going with the VBA solution, I've
modified the code based on your 2nd posting. This will first check to make
sure that only one cell changed (so you can use [Del] without fear), and that
the changed cell was in a row from 3 to 369, then it goes on as before to
check if the change was in columns C, D or E.

I could have used INTERSECT() easily with your setup since C3:E369 is an
easily defined range, but I elected to stick with the Select Case to give you
some flexibility in the future in case you insert columns between C and D or
D and E (you'd have to change the code just a little), or need to test other
columns in the same manner.

I'm presuming that having the sheet locked will not affect things, since the
cells we are examining are presumably being typed into by a user, so those
individual cells are not locked. If that's not the case, let me know and we
can add a couple of lines of code to deal with unprotecting and reprotecting
the sheet.
Here's the new code:

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in columns C, D or E
'between rows 3 and 369 (inclusive) and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
'validate the change as:
' #1 - a single cell, so if you [Del] a bunch, nothing happens
' #2 - change took place in rows 3:369
If Target.Cells.Count > 1 Then
Exit Sub ' multiple cells selected
End If
If Target.Row < 3 Or Target.Row > 369 Then
Exit Sub ' not in rows 3 through 369
End If
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3, 4, 5 ' columns C, D or E
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
End Sub

Sorry. I guess stating "all" possible errors was a bit of a stretch. How
will the code change knowing that the applicable cells are C3:C369, D3369
and E3:E369. Also, will it affect any Locked cells?
--
RGS

Well, I don't know about eliminating all possible error entries since humans
seem to have an almost infinite capacity for screwing up a typed entry, but...
This code will test entries and replace commas and colons with a period
which should help most of the time.

To put the code to work, open your workbook, go to the time entering sheet
and right-click on its name tab and choose [View Code] from the list that
appears. Copy the code below and paste it into the code module presented to
you. Make any change needed to get it to work in the correct column(s).
Close the VB Editor and give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in a column and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3 ' column C
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
'
'if you wish to check multiple columns, then
'just change the initial Case Is = statement to
'include all column numbers, as this example
'for columns C, G and R
' Case Is = 3, 7, 18

End Sub

Errors with Hrs. entry by employees. Occasional keying errors such as colons
(8:00), commas (8,00), etc. How do I set up a validation that eliminates all
possible error entries other than regular hr. entries with 2 decimal places,
and gives a pop-up error message? We are using Excel 2003.

 

[RoRo123]

First, employees are entering actual hours worked (the honor system.) The
cells they are entering the hours into are NOT locked. I have used this last
VBA routine of yours and it does automatically change a colon or comma to a
decimal. However, it is deleting my previous number formatting completely,
and setting the entry to only one decimal place (7.5). I would like to see
the number entry at two decimal places. Thanks so much for you help so far
--
RGS

On the offchance that you do end up going with the VBA solution, I've
modified the code based on your 2nd posting. This will first check to make
sure that only one cell changed (so you can use [Del] without fear), and that
the changed cell was in a row from 3 to 369, then it goes on as before to
check if the change was in columns C, D or E.

I could have used INTERSECT() easily with your setup since C3:E369 is an
easily defined range, but I elected to stick with the Select Case to give you
some flexibility in the future in case you insert columns between C and D or
D and E (you'd have to change the code just a little), or need to test other
columns in the same manner.

I'm presuming that having the sheet locked will not affect things, since the
cells we are examining are presumably being typed into by a user, so those
individual cells are not locked. If that's not the case, let me know and we
can add a couple of lines of code to deal with unprotecting and reprotecting
the sheet.
Here's the new code:

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in columns C, D or E
'between rows 3 and 369 (inclusive) and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
'validate the change as:
' #1 - a single cell, so if you [Del] a bunch, nothing happens
' #2 - change took place in rows 3:369
If Target.Cells.Count > 1 Then
Exit Sub ' multiple cells selected
End If
If Target.Row < 3 Or Target.Row > 369 Then
Exit Sub ' not in rows 3 through 369
End If
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3, 4, 5 ' columns C, D or E
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
End Sub

Sorry. I guess stating "all" possible errors was a bit of a stretch. How
will the code change knowing that the applicable cells are C3:C369, D3369
and E3:E369. Also, will it affect any Locked cells?
--
RGS

Well, I don't know about eliminating all possible error entries since humans
seem to have an almost infinite capacity for screwing up a typed entry, but...
This code will test entries and replace commas and colons with a period
which should help most of the time.

To put the code to work, open your workbook, go to the time entering sheet
and right-click on its name tab and choose [View Code] from the list that
appears. Copy the code below and paste it into the code module presented to
you. Make any change needed to get it to work in the correct column(s).
Close the VB Editor and give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'check for changes in a column and if the entry
'in the cell changed, replace commas or colons with periods.
'
'can be modified to check any number of columns
'
Dim newEntry As String
Application.EnableEvents = False ' prevent re-entry
newEntry = Target.Text
Select Case Target.Column
Case Is = 3 ' column C
newEntry = Replace(newEntry, ",", ".")
newEntry = Replace(newEntry, ":", ".")
Case Else
'do nothing
End Select
Target.NumberFormat = "General"
Target.Value = newEntry
Application.EnableEvents = True ' for next time
'
'if you wish to check multiple columns, then
'just change the initial Case Is = statement to
'include all column numbers, as this example
'for columns C, G and R
' Case Is = 3, 7, 18

End Sub

:

Errors with Hrs. entry by employees. Occasional keying errors such as colons
(8:00), commas (8,00), etc. How do I set up a validation that eliminates all
possible error entries other than regular hr. entries with 2 decimal places,
and gives a pop-up error message? We are using Excel 2003.