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]

Ah, you're right. Actually you need to fill in the venue first and then the date.
I amended the code so it will force the user to to fill in the venue first.

All I've changed in the code is instead of A1:A10 my range is B1:B99 because I have a new column A (for a different purpose) so it's all moved along one. And because we will need more entries than 10 obviously. But that shouldn't affect anything else?

That's fine.

Try this one:

[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:A20"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    
    [COLOR=Royalblue]If[/COLOR] Target.Offset(, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        MsgBox [COLOR=brown]"You need to pick a venue first"[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        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]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range, flag [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR], tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
        [COLOR=Royalblue]If[/COLOR] IsDate(Target) [COLOR=Royalblue]Then[/COLOR]
            flag = [COLOR=Royalblue]True[/COLOR]: tx = [COLOR=brown]""[/COLOR]
            [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"A1:A20"[/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]
                    tx = tx & c & [COLOR=brown]" : "[/COLOR] & c.Offset(, [COLOR=crimson]1[/COLOR]) & vbLf
                    flag = [COLOR=Royalblue]False[/COLOR]
                    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
            
            [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]False[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                    MsgBox [COLOR=brown]"Pick another date."[/COLOR] & vbLf & [COLOR=brown]"This has been allocated:"[/COLOR] & vbLf & tx, 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]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Ceallach]

Ah that's better! Thank you.


Any chance you can help with this too?

"Another question, is there a way (in a separate sheet) if I had the Months of the year laid out (sort of calendar style) to have it capture the dates entered for that particular month, but in date order? So at a glance if a manager or somebody wanted to see which dates were booked for "November" they can just look at that and see which weeks have bookings? Ideally I don't want them to need to run a macro or play with a pivot table, just have something that auto-populates from the data."

So it might look like a row of months with the dates listed underneath.

 

[Akuini]

Any chance you can help with this too?

Since it's a different problem then you might want to start a new thread for that.

 

[Ceallach]

Since it's a different problem then you might want to start a new thread for that.

Will do, thanks!

 

[Ceallach]

I think you may want to put data validation in col B (listing all the options) to make sure that the user use the exact same terms.

I put data validation so now they have to pick from a dropdown menu, it will definitely help. However in the process we’ve realized there’s another criteria that we need to factor in. I can’t say what it is as it’s too identifiable. But let’s say it’s “X” or “Y” or “Z”.

So my question is, can we have a second criteria to compare to? Or does that need to be done separately? The criteria is in the next column along.

So Whole Venue X does not conflict with Whole Venue Y or Whole Venue Z (e.g. they can be booked within 7 days of each other).

Whole Venue Y does not conflict with Green Room X because it’s a whole different criteria (e.g. they can be booked within 7 days of each other).

However, Whole Venue Y conflicts with Green Room Y or Blue Room Y.

Does this makes sense or is this beyond what it can do?


[TABLE="width: 417"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Venue[/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD]1-Nov[/TD]
[TD]Whole Venue[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]10-Dec[/TD]
[TD]Green Room[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4-Nov[/TD]
[TD]Whole Venue[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]16-Nov[/TD]
[TD]Blue Room[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]30-Nov[/TD]
[TD]Green Room[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]20-Dec[/TD]
[TD]Whole Venue[/TD]
[TD]Z[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Is the second criteria required or it can be empty?

 

[Ceallach]

Is the second criteria required or it can be empty?

Required. I thought maybe we just specify it within the venue list but that would triple the length of the list, plus means the whole venue conflict would need changing too as there would be multiple conflicts

 

[Akuini]

What about green room X with green room Y? is it a conflict?

 

[Ceallach]

What about green room X with green room Y? is it a conflict?

No, no conflict, can occur within the 7 days

 

[Akuini]

Sorry, I kind of forget what you really mean by occur within 7 days, does it include the same day?
So if green room X with green room Y occur in the same day it's not a conflict?