Date and time

[Guest]

Hi all,
I have two cells one for date today,the other for time now,i need to
disable users from using a date or time prior to date today and time now,and
whenevr users try to use a date before today's date or time before the
present time ,they get an error message "You cann't enter a date prior to
today's date nor time prior to the present time" i need also to put the title
"Operated by Customer Support Tool"
Is it possible ?
Please provide simple way as i'm still new to Excell
Regards

 

[JE McGimpsey]

One way:

Select your date entry cell(s). Assume cell A1 is selected. Choose
Data/Validation. Select Allow:Custom, Formula: =A1>=TODAY()

Do the same thing for Time.

If the entry is made in one cell, change the formula to

=A1>NOW()

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D:E" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column = 4 Then
If .Value < Date Then
MsgBox "Invalid date", , "Operated by Customer Support
Tool"
.Value = ""
End If
ElseIf .Value < Time Then
MsgBox "Invalid time", , "Operated by Customer Support Tool"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D:E" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column = 4 Then
If .Value < Date Then
MsgBox "Invalid date", , "Operated by Customer Support
Tool"
.Value = ""
End If
ElseIf .Value < Time Then
MsgBox "Invalid time", , "Operated by Customer Support Tool"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Have a look at data validation. Complete the settings tab to allow "Date",
data Greater than or equal to then set start date

 

[Guest]

Ok thanks,
I want to display an error message "You canot insert a date before today's
date",whenever users insert a date that's prior to today's date..in the data
validation i want to input the expression that means datetoday..what should i
do ?
Thanx

 

[JE McGimpsey]

One way:

Formula is =A1>=TODAY()

Ok thanks,
I want to display an error message "You canot insert a date before today's
date",whenever users insert a date that's prior to today's date..in the data
validation i want to input the expression that means datetoday..what should i
do ?
Thanx