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!

 

[Ceallach]

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?

Yes that's right. It's basically use of some equipment related to that room so it's not a conflict at all, can be used at anytime separate.

 

[Akuini]

Ok, so the conflict only happens if for the same second criteria (x,y or z) user pick whole venue & any colored room within 7 days?
If user doesn't pick whole venue then there is no situation that will cause conflict, is it correct?

Another thing, is it possible that the user inserting duplicate entries? do you need to prevent it?

 

[Ceallach]

Ok, so the conflict only happens if for the same second criteria (x,y or z) user pick whole venue & any colored room within 7 days?
If user doesn't pick whole venue then there is no situation that will cause conflict, is it correct?

Another thing, is it possible that the user inserting duplicate entries? do you need to prevent it?

Yep conflict if they have the same criteria within 7 days. Conflict for whole venue with the same criteria as any other room within 7 days.

If there's no whole venue used within the 7 days then yes, no situation will cause conflict unless somebody tries to select the exact same venue and criteria e.g. Green Room X for 2nd Oct and Green Room X for 3rd Oct would conflict. Any other combination of room and criteria is fine.

Duplicate entries isn't a likely issue, there are further columns where they specify the person who has booked it etc so it would be obvious if there was an issue there. And it still isn't very many events per month.

 

[Akuini]

Ok, 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=Brown]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=Darkcyan]"A1:A20"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    
        [COLOR=Royalblue]If[/COLOR] Target.Offset(, [COLOR=Brown]1[/COLOR]) = [COLOR=Darkcyan]""[/COLOR] [COLOR=Royalblue]Or[/COLOR] Target.Offset(, [COLOR=Brown]2[/COLOR]) = [COLOR=Darkcyan]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            MsgBox [COLOR=Darkcyan]"You need to pick a Venue and a criteria"[/COLOR]
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            [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], crt [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
        [COLOR=Royalblue]If[/COLOR] IsDate(Target) [COLOR=Royalblue]Then[/COLOR]
            flag = True: tx = [COLOR=Darkcyan]""[/COLOR]: ven = Target.Offset(, [COLOR=Brown]1[/COLOR]).Value: crt = Target.Offset(, [COLOR=Brown]2[/COLOR]).Value
            [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c In Range([COLOR=Darkcyan]"A1:A20"[/COLOR])
                [COLOR=Royalblue]If[/COLOR] Len(c) > [COLOR=Brown]0[/COLOR] [COLOR=Royalblue]And[/COLOR] IsDate(c) [COLOR=Royalblue]And[/COLOR] c.Address <> Target.Address [COLOR=Royalblue]Then[/COLOR]
                    [COLOR=Royalblue]If[/COLOR] c.Offset(, [COLOR=Brown]2[/COLOR]) = crt [COLOR=Royalblue]And[/COLOR] (ven = [COLOR=Darkcyan]"Whole Venue"[/COLOR] [COLOR=Royalblue]Or[/COLOR] c.Offset(, [COLOR=Brown]1[/COLOR]) = [COLOR=Darkcyan]"Whole Venue"[/COLOR] [COLOR=Royalblue]Or[/COLOR] c.Offset(, [COLOR=Brown]1[/COLOR]) = ven) [COLOR=Royalblue]Then[/COLOR]
                        [COLOR=Royalblue]If[/COLOR] Abs(DateDiff([COLOR=Darkcyan]"d"[/COLOR], [COLOR=Royalblue]CDate[/COLOR](Target), [COLOR=Royalblue]CDate[/COLOR](c))) < [COLOR=Brown]7[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                            tx = tx & c & [COLOR=Darkcyan]" : "[/COLOR] & c.Offset(, [COLOR=Brown]1[/COLOR]) & [COLOR=Darkcyan]" : "[/COLOR] & crt & vbLf
                            flag = False
                        [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]Next[/COLOR]
            
            [COLOR=Royalblue]If[/COLOR] flag = False [COLOR=Royalblue]Then[/COLOR]
                    MsgBox [COLOR=Darkcyan]"Pick another date."[/COLOR] & vbLf & [COLOR=Darkcyan]"This has been allocated:"[/COLOR] & vbLf & tx, vbCritical
                    Application.EnableEvents = False
                    Target.ClearContents
                    Application.EnableEvents = True
                    [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]Else[/COLOR]
            MsgBox [COLOR=Darkcyan]"You need to insert a date"[/COLOR]
                    Application.EnableEvents = False
                    Target.ClearContents
                    Application.EnableEvents = True

        [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]