Restricting Dates entry to Today's date: Date Validation

[nitinjoshi123]

Hi all
I have an excel sheet where all team members enter the job reference no (column A), their initials (column B) and date completed (column C). I want to restrict the date completed column so they can only enter today's date. However, i want to give exceptions to three users whose initials are NM, JM and MD. If i use date validation. it restricts entries for all users. Is it possible to restrict entry for all users except three.
Many thanks in advance.
Nitin

 

[barry houdini]

Presumably for the 3 excepted users you would still want to restrict them to a date?

This will allow those 3 users to enter any date within 100 days of today (either way), you can change that as required, assuming they enter their initials first

Select column C and use Data > Validation with the custom formula

=IF(AND(B1<>"NM",B1<>"MD",B1<>"JM"),C1=TODAY(),ABS(C1-TODAY())<100)

also untick the "ignore blank" box.

Note: this isn't particularly difficult to get around, pasted values aren't validated, or a user could enter the "wrong" initials to allow entry of a date other than today...and then change initials back

 

[nitinjoshi123]

Thanks , but this formula is giving me error. Not accepting any dates..
I understand that people can find a way around, but we can't do much can we. I have to leave the sheet accessible to all users and can't protect it either. Unless I have a second sheet that autofills it from the first sheet, but tracks the changes made to the first sheet as well.. Can't get my head around on how to do that..

 

[Simon Lloyd]

How about the date entering automatically and something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Me.Unprotect Password:="password"
    If Not Intersect(Target, Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row)) Is Nothing Then
    If Range("B" & Target.Row).Value = "" Then Exit Sub
        If Target.Value = "NM" Or Target.Value = "JM" Or Target.Value = "MD" Then
        MsgBox "Please Enter Appropriate Date In Column C", vbInformation, "Date Edit Authorisation"
            Target.Offset(0, 1).Select
        Else
        Me.Unprotect Password:="password"
        Target.Offset(0, 1) = Date
        Me.Protect Password:="password"
        End If
    Else
        Exit Sub
        Me.Protect Password:="password"
    End If
    If Target.Offset(0, 1).Value = "" Then
    Me.Unprotect Password:="password"
    Else
    Me.Protect Password:="password"
    End If
End Sub

it goes in the worksheet code module, it's not perfect but you get the idea!

 

[barry houdini]

Thanks , but this formula is giving me error. Not accepting any dates..

It works when I test it, did you use exactly that formula?

 

[nitinjoshi123]

yes!!!!