# Date data validation formula

#### Ceallach

##### New Member
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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Akuini

##### Well-known Member
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, [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 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

##### New Member
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

##### Well-known Member
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

##### New Member
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
<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> </tbody>

#### Akuini

##### Well-known Member
Try this:

Code:
``````[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]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

##### New Member
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

##### Well-known Member
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 DateVenueCategory
2
15/12/2019​
Whole venueParty
3
04/12/2019​
Green roomConference
4
01/12/2019​
Whole venueParty
5
28/11/2019​
Whole venueConference
6
21/11/2019​
Green roomMeeting
7
14/11/2019​
Whole venueConference
8
06/11/2019​
Pink roomConference
9
04/11/2019​
Green room
10
Green room
 Sheet: Sheet1

#### Ceallach

##### New Member
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

##### Well-known Member
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``````