Date Time Question

[Daviv]

I have the following on my spreadsheet:

A1 = Date
A5 = Begin Time
A6 = End Time
A7 = A1+A5 'Giving me the Beginning Date & Time
A8 = A1+A6 'Giving me the End Date & Time

What I want to do is whenever the user enters a time in Cell A9, either a
data validation or vba check that time entered is between the value in Cell
A7 and A8. The assumption is that the value in Cell A9 is A9+A1 but I only
want the user to only enter the time. It is probably easier just to have a
format of Date/Time throughout but I am constraint by the design format. I
tried with the validation with a custom formula: = AND(A9+A1>A7, A9+A1<A8).
This just gave me a error for any number I entered. And I tried a event
procedure (Note the target.column and target row do not match the cells
above):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 15 And Target.Row = 10 Then
If Target.Value + Target.Offset(-9, 2).Value >
Target.Offset(-1, 0).Value And Target.Value + Target.Offset(-9, 2).Value >
Target.Offset(-1, 1) Then
Target.Value = Target.Value
Else
MsgBox ("Time entered does not fall within work shift.")
Application.Enableevents = False
Target.Value = " "
Exit Sub
Application.Enableevents = True
End If
End If
End Sub

But this does not work either. Any help is appreciated.

 

[Jay]

Hi Daviv -

Because the scenario you've described results in two different times on the
same date in A7 and A8, you could use data validation on the times alone.
Validation Settings for Cell A9
Allow: Time
Data: between
Start time: =A5
End time: =A6


Or, you could refer to the time portion of the date/time in Cells A7 and A8:
Validation Settings for Cell A9
Allow: Time
Data: between
Start time: =Time(Hour(A7), Minute(A7), Second(A7))
End time: =Time(Hour(A8), Minute(A8), Second (A8))