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!
 
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?
 
Upvote 0
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.
 
Upvote 0
Ok, 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=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]
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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