Restricting Dates entry to Today's date: Date Validation

nitinjoshi123

New Member
Joined
Jun 8, 2008
Messages
18
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 :)
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Jun 8, 2008
Messages
18
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

Well-known Member
Joined
Sep 10, 2006
Messages
756
How about the date entering automatically and something like this:
Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top