Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 34

Thread: Date data validation formula

  1. #21
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    Ah, you're right. Actually you need to fill in the venue first and then the date.
    I amended the code so it will force the user to to fill in the venue first.

    All I've changed in the code is instead of A1:A10 my range is B1:B99 because I have a new column A (for a different purpose) so it's all moved along one. And because we will need more entries than 10 obviously. But that shouldn't affect anything else?
    That's fine.

    Try this one:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge <> 1 Then Exit Sub
    
        
        If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
        
        If Target.Offset(, 1) = "" Then
            MsgBox "You need to pick a venue first"
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
    
            Dim c As Range, flag As Boolean, tx As String
            If IsDate(Target) Then
                flag = True: tx = ""
                For Each c In Range("A1:A20")
                     
                    If Len(c) > 0 And IsDate(c) And c.Address <> Target.Address _
                    And (c.Offset(, 1) = "Whole venue" Or Target.Offset(, 1) = "Whole venue" Or _
                    c.Offset(, 1) = Target.Offset(, 1)) Then
                        If Abs(DateDiff("d", CDate(Target), CDate(c))) < 7 Then
                        tx = tx & c & " : " & c.Offset(, 1) & vbLf
                        flag = False
                        End If
                    End If
                Next
                
                If flag = False Then
                        MsgBox "Pick another date." & vbLf & "This has been allocated:" & vbLf & tx, vbCritical
                        Application.EnableEvents = False
                        Target.Activate
                        Target.ClearContents
                        Application.EnableEvents = True
                        Exit Sub
                End If
            End If
            
        End If
    End Sub

  2. #22
    New Member
    Join Date
    Sep 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Ah that's better! Thank you.


    Any chance you can help with this too?

    "Another question, is there a way (in a separate sheet) if I had the Months of the year laid out (sort of calendar style) to have it capture the dates entered for that particular month, but in date order? So at a glance if a manager or somebody wanted to see which dates were booked for "November" they can just look at that and see which weeks have bookings? Ideally I don't want them to need to run a macro or play with a pivot table, just have something that auto-populates from the data."

    So it might look like a row of months with the dates listed underneath.

  3. #23
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    Any chance you can help with this too?
    Since it's a different problem then you might want to start a new thread for that.

  4. #24
    New Member
    Join Date
    Sep 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    Since it's a different problem then you might want to start a new thread for that.
    Will do, thanks!

  5. #25
    New Member
    Join Date
    Sep 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    I think you may want to put data validation in col B (listing all the options) to make sure that the user use the exact same terms.


    I put data validation so now they have to pick from a dropdown menu, it will definitely help. However in the process weve realized theres another criteria that we need to factor in. I cant say what it is as its too identifiable. But lets say its X or Y or Z.

    So my question is, can we have a second criteria to compare to? Or does that need to be done separately? The criteria is in the next column along.

    So Whole Venue X does not conflict with Whole Venue Y or Whole Venue Z (e.g. they can be booked within 7 days of each other).

    Whole Venue Y does not conflict with Green Room X because its a whole different criteria (e.g. they can be booked within 7 days of each other).

    However, Whole Venue Y conflicts with Green Room Y or Blue Room Y.

    Does this makes sense or is this beyond what it can do?


    Date Venue Criteria
    1-Nov Whole Venue X
    10-Dec Green Room Y
    4-Nov Whole Venue Y
    16-Nov Blue Room Z
    30-Nov Green Room X
    20-Dec Whole Venue Z

  6. #26
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    Is the second criteria required or it can be empty?

  7. #27
    New Member
    Join Date
    Sep 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    Is the second criteria required or it can be empty?
    Required. I thought maybe we just specify it within the venue list but that would triple the length of the list, plus means the whole venue conflict would need changing too as there would be multiple conflicts

  8. #28
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    What about green room X with green room Y? is it a conflict?

  9. #29
    New Member
    Join Date
    Sep 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    What about green room X with green room Y? is it a conflict?
    No, no conflict, can occur within the 7 days

  10. #30
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    Sorry, I kind of forget what you really mean by occur within 7 days, does it include the same day?
    So if green room X with green room Y occur in the same day it's not a conflict?
    Last edited by Akuini; Oct 21st, 2019 at 11:27 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •