Date data validation formula

[Ceallach]

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!

 

[Akuini]

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")'.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge <> 1 Then Exit Sub

    If Not Intersect(Target, [COLOR=#ff0000]Range("A1:A5")[/COLOR]) Is Nothing Then
        Dim c As Range
        If IsDate(Target) Then
            For Each c In [COLOR=#ff0000]Range("A1:A5")[/COLOR]
                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

 

[Ceallach]

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.

 

[Akuini]

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

 

[Ceallach]

Like this:

[TABLE="width: 206"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <tbody>[TR]
[TD="width: 75, bgcolor: #D0CECE"]Event Date[/TD]
[TD="width: 90, bgcolor: #BFBFBF"]Venue[/TD]
[TD="width: 109, bgcolor: #BFBFBF"]Category[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15/12/2019[/TD]
[TD="bgcolor: transparent"]Whole venue[/TD]
[TD="bgcolor: transparent"]Party[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/12/2019[/TD]
[TD="bgcolor: transparent"]Green room[/TD]
[TD="bgcolor: transparent"]Conference[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/12/2019[/TD]
[TD="bgcolor: transparent"]Whole venue[/TD]
[TD="bgcolor: transparent"]Party[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]28/11/2019[/TD]
[TD="bgcolor: transparent"]Whole venue[/TD]
[TD="bgcolor: transparent"]Conference[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]21/11/2019[/TD]
[TD="bgcolor: transparent"]Green room[/TD]
[TD="bgcolor: transparent"]Meeting[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14/11/2019[/TD]
[TD="bgcolor: transparent"]Whole venue[/TD]
[TD="bgcolor: transparent"]Conference[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/11/2019[/TD]
[TD="bgcolor: transparent"]Pink room[/TD]
[TD="bgcolor: transparent"]Conference[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Try this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A1:A10"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
        [COLOR=Royalblue]If[/COLOR] IsDate(Target) [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"A1:A10"[/COLOR])
                [COLOR=Royalblue]If[/COLOR] Len(c) > [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]And[/COLOR] IsDate(c) [COLOR=Royalblue]And[/COLOR] c.Address <> Target.Address _
                [COLOR=Royalblue]And[/COLOR] (c.Offset(, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"Whole venue"[/COLOR] [COLOR=Royalblue]Or[/COLOR] Target.Offset(, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"Whole venue"[/COLOR] [COLOR=Royalblue]Or[/COLOR] _
                c.Offset(, [COLOR=crimson]1[/COLOR]) = Target.Offset(, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
                     [COLOR=Royalblue]If[/COLOR] Abs(DateDiff([COLOR=brown]"d"[/COLOR], [COLOR=Royalblue]CDate[/COLOR](Target), [COLOR=Royalblue]CDate[/COLOR](c))) < [COLOR=crimson]7[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                        MsgBox [COLOR=brown]"Wrong Date"[/COLOR], vbCritical
                        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
                        Target.Activate
                        Target.ClearContents
                        Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
                        [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
                    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Ceallach]

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?

 

[Akuini]

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
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1​

[/td][td=bgcolor:#B4C6E7]Event Date[/td][td=bgcolor:#B4C6E7]Venue[/td][td=bgcolor:#B4C6E7]Category[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2​

[/td][td]

15/12/2019​

[/td][td]Whole venue[/td][td]Party[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

3​

[/td][td]

04/12/2019​

[/td][td]Green room[/td][td]Conference[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

4​

[/td][td]

01/12/2019​

[/td][td]Whole venue[/td][td]Party[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

5​

[/td][td]

28/11/2019​

[/td][td]Whole venue[/td][td]Conference[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

6​

[/td][td]

21/11/2019​

[/td][td]Green room[/td][td]Meeting[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

7​

[/td][td]

14/11/2019​

[/td][td]Whole venue[/td][td]Conference[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

8​

[/td][td]

06/11/2019​

[/td][td]Pink room[/td][td]Conference[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

9​

[/td][td]

04/11/2019​

[/td][td]Green room[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

10​

[/td][td]

​

[/td][td]Green room[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Ceallach]

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!

 

[Akuini]

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").)

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