Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Date data validation formula

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

    Default Date data validation formula

    I have a basic spreadsheet where staff enter details of events they're planning, it's in a set format so the date of event goes in a specific row. Even with only 4 events per month there's an issue with people not checking and trying to schedule them too close together.

    I've been asked to make it so they can't enter a date into the date cells that falls within 7 days (before or after) any other date entered, as the events need to be a minimum of 7 days apart.

    Every way I can think of doing it results in some kind of weird circular formula. e.g. having a seperate sheet that picks up the dates and then calculates what dates are still allowed. Is there something I'm missing here?

    Also separate to that they want a separate tab with a sort of calendar of the dates so we can see at a glance which weeks we have events scheduled, but again I'm not sure how to get that done in date order. It sounded like a basic task but not I'm thinking my Excel is rustier than I thought it was! Help appreciated!

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

    Default Re: Date data validation formula

    Hi, Ceallach. Welcome to the Forum
    You'll need vba to do that.
    Let's say the dates will be entered in Range("A1:A5") sheets "sheet1"

    This is an Event Procedure, you need to put it in the code module of the sheet (say sheet1).
    So copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.
    Change 'Range("A1:A5")' to suit.
    The Sub Worksheet_Change is triggered whenever you exit a cell after you changed the cell content, in this example if the cell is in 'Range("A1:A5")'.


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge <> 1 Then Exit Sub
    
        If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
            Dim c As Range
            If IsDate(Target) Then
                For Each c In Range("A1:A5")
                    If Len(c) > 0 And IsDate(c) And c.Address <> Target.Address Then
                         If Abs(DateDiff("d", CDate(Target), CDate(c))) < 7 Then
                            MsgBox "Wrong Date", vbCritical
                            Application.EnableEvents = False
                            Target.Activate
                            Target.ClearContents
                            Application.EnableEvents = True
                            Exit Sub
                        End If
                    End If
                Next
            End If
            
        End If
    End Sub

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

    Default Re: Date data validation formula

    This is so cool! Thank you I'm wondering is it possible to take this to another level, for example if the rules only applied depending on what happened in another column e.g. "venue" or is that too complicated? It just occurred to me that this could be useful for that. For example, if an event was being held just in the "green room" on 1st Dec and another in the 4th Dec in the "blue room" that would be ok because they aren't in the same space, but an event in the "whole venue" couldn't be held within 7 days of either of those dates as it would need both those rooms. It's rare a partial venue is used but it is on occasion so we would need to manually override the date restriction otherwise.

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

    Default Re: Date data validation formula

    It's hard to understand your explanation without seeing your data.
    It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.
    or
    You can post a sample data as a table.

    To copy a simple table from worksheet:
    1. In your worksheet select the table/range
    2. In the menu, select border icon > select All Borders > then copy.
    3. Back to the thread > in the reply box paste the table

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

    Default Re: Date data validation formula

    Like this:

    Event Date Venue Category
    15/12/2019 Whole venue Party
    4/12/2019 Green room Conference
    1/12/2019 Whole venue Party
    28/11/2019 Whole venue Conference
    21/11/2019 Green room Meeting
    14/11/2019 Whole venue Conference
    6/11/2019 Pink room Conference

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

    Default Re: Date data validation formula

    Try this:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge <> 1 Then Exit Sub
    
        If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
            Dim c As Range
            If IsDate(Target) Then
                For Each c In Range("A1:A10")
                    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
                            MsgBox "Wrong Date", vbCritical
                            Application.EnableEvents = False
                            Target.Activate
                            Target.ClearContents
                            Application.EnableEvents = True
                            Exit Sub
                        End If
                    End If
                Next
            End If
            
        End If
    End Sub

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

    Default Re: Date data validation formula

    Yes that's what I mean, except it gets more complicated. If someone is using the Green room on 4/12 then nobody can use the whole venue on 5/12 because the green room Is part of the whole venue. So if someone is just using the blue room on 5/12 they’re fine, but if they’reusing the whole venue or the green room they can’t use a date with 7 days ofthe one that’s already there for the green room. Socan it look at multiple if/then criteria like that?

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

    Default Re: Date data validation formula

    So if someone is just using the blue room on 5/12 they’re fine, but if they’reusing the whole venue or the green room they can’t use a date with 7 days ofthe one that’s already there for the green room.
    I don't understand, that's what the code does. If on 5/12 you use blue room then you can use green room within +-7 days but not whole venue.
    For example I can add 04/11/2019 for Green room, but can't add 12/11/2019 for green room because 14/11/2019 is for Whole venue

    Your data is in column A:B, right?
    Can you give another example where the code doesn't give the expected result using your criteria?

    Excel 2013 32 bit
    A
    B
    C
    1
    Event Date Venue Category
    2
    15/12/2019
    Whole venue Party
    3
    04/12/2019
    Green room Conference
    4
    01/12/2019
    Whole venue Party
    5
    28/11/2019
    Whole venue Conference
    6
    21/11/2019
    Green room Meeting
    7
    14/11/2019
    Whole venue Conference
    8
    06/11/2019
    Pink room Conference
    9
    04/11/2019
    Green room
    10
    Green room
    Sheet: Sheet1

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

    Default Re: Date data validation formula

    Sorry, I think I was confused because the code didn't specify the other other locations! I reallyappreciate your help, this has been quite enlightening. I’ll implement the code fully on myspreadsheet and let you know how I go!

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

    Default Re: Date data validation formula

    If your data is on different column, then change 'Offset(, 1)' to suit, 1 means if the venue is 1 column to the right of Date, so if the data say, Date in col D & venue in col G then it should be 'Offset(, 3)'.
    (And off course you need to change Range("A1:A10").)

    Code:
    If Len(c) > 0 And IsDate(c) And c.Address <> Target.Address _
    And (c.Offset(, 3) = "Whole venue" Or Target.Offset(, 3) = "Whole venue" Or _
    c.Offset(, 3) = Target.Offset(, 3)) Then

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
  •