Incremental dates based upon specifications

  • Thread starter Thread starter itybitty68
  • Start date Start date
I

itybitty68

I'm trying to auto input the date of a column based off a text entry from
another column. I have used the NOW() function as my date but when you use
that formula on the line below, when one updates, they both update & I don't
want that to happen each time a new text is entered. I want the date that is
automatically entered as date with current time so that I can use that
information to validate the correct date if entered before 12, date entered
would be current date & if after 12pm it would be the next day and if entered
on friday after 12 it would be entered as monday.
 
What you described can't be done with just formulas, as it requires a
calculation to be performed at a single point in time. However, you can do
this with some visual basic coding. Right click on the sheet tab, view code,
and paste the following in, editting as required:


Private Sub Worksheet_Change(ByVal Target As Range)

'Use this line to control what range you want to check
If Intersect(Target, Range("A2:A100")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

'Determine current time
x = Time
If x > TimeValue("12:00 pm") Then

'If in the afternoon:
If Target.Value = Date + 1 Then Exit Sub
'If in the morning
Else: If Target.Value = Date Then Exit Sub
End If

'What to say if invalid date
MsgBox "Invalid date input"
Target.Select

'Do you want entry deleted?
'If yes, remove single quotation from
'the next line

'Target.ClearContents
End Sub
 
Back
Top