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!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,262
Office Version
2013
Platform
Windows
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
 

Ceallach

New Member
Joined
Sep 14, 2019
Messages
20
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
Joined
Feb 1, 2016
Messages
2,262
Office Version
2013
Platform
Windows
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

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,262
Office Version
2013
Platform
Windows
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]
 

Ceallach

New Member
Joined
Sep 14, 2019
Messages
20
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
Joined
Feb 1, 2016
Messages
2,262
Office Version
2013
Platform
Windows
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
Joined
Sep 14, 2019
Messages
20
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
Joined
Feb 1, 2016
Messages
2,262
Office Version
2013
Platform
Windows
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
 

Forum statistics

Threads
1,078,447
Messages
5,340,345
Members
399,370
Latest member
salamon

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top