Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Time Question
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+A1A7, 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. -- Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Time Question
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)) -- Jay "Daviv" wrote: 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+A1A7, 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. -- Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time Question | Excel Discussion (Misc queries) | |||
Date Time Question | Excel Programming | |||
Another question about time and date | Excel Discussion (Misc queries) | |||
Date Time Question | Excel Programming | |||
Date/Time Question | Excel Worksheet Functions |