Date data validation formula

Ceallach

New Member
Joined
Sep 14, 2019
Messages
20
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Like this:

Event DateVenueCategory
15/12/2019Whole venueParty
4/12/2019Green roomConference
1/12/2019Whole venueParty
28/11/2019Whole venueConference
21/11/2019Green roomMeeting
14/11/2019Whole venueConference
6/11/2019Pink roomConference
<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>
 
Upvote 0
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]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]
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top