Vba Macro Help -Possibly a fomula

[DaveR]

What I am trying to achieve is this; I have a 'Date of refferal' in Column H, what I want to happen it that when this is filled in, Column I then offers two days which are either the following Wednesday, or the Wednesday after that?

All help greatfully recieved.

 

[Andrew Poulsom]

For the following Wednesday:

=IF(ISNUMBER(H1),H1-WEEKDAY(H1)+4+(7*(WEEKDAY(H1)>3)),"")

 

[DaveR]

I'm currently looking at http://www.ozgrid.com/VBA/excel-calendar-dates.htm

So I currently have this set up, but would like the Userform to trigger on a date entry in Column H and ideally highlight in Red the following two Wednesdays as options.

Thanks for the help so far, but looking at it closer I don't think a formula will give me what I would like.

 

[GlennUK]

I would use Andrew's formula, and a similar one for the other Wednesday, in another part of the sheet, and refer to those 2 cells in Data Validation/List for an in-cell drop-down.

 

[DaveR]

Thats is actually a cunning plan!

I'll have a go now........

Thanks for the inspiration (and the original formula)!

 

[DaveR]

Almost there!

The Formula;

=IF(ISNUMBER(H1),H1-WEEKDAY(H1)+4+(7*(WEEKDAY(H1)>3)),"")

works fine and my 'cever' bodge to make it work for the following 7 day i.e. cell value+7 now fills the cell with #value if the refreance cell is blank.

How do I change the above formula to calulate 2 weeks in advance? (I've tried the obvious changing 7 to 14 but that didn't work).

 

[Andrew Poulsom]

For 2 weeks in advance:

=IF(ISNUMBER(H1),H1-WEEKDAY(H1)+11+(7*(WEEKDAY(H1)>3)),"")

 

[DaveR]

Thank for this!

Now the next problem begins.....

What I would like to do is after the date is selected in Column I (either of the following wednesday), I would like to Generate a dynamic list for Column J were there is a series of times available i.e. 9.00am, 9.20am 9.40 am etc.

This is straightforward, however I want to ensure the list of available times only contains those times that havent already been booked on that date?

All suggestions on how to accomplish greatly recieved.

 

[GlennUK]

So there must be a list of bookings for that day, with times, somewhere. Can you describe that?

 

[DaveR]

It is simply a list of booking slots from 9.00am to 11.20 in 20minute slots. As they are used, they become unavailable to select for that date.

I'm currently looking at http://www.contextures.com/xlDataVal03.html

as I think this is what I want, just needs a bit of tweeking to get it to work for me.