Find specifc days 75 days before selected date. 1

[netrusher]

I would like to select a date on a calendar and go back 75 days from the date selected and show every Tuesday, Thursday and Saturday in the 75 day range. I would like to see the actual date of each Tue. Thur. & Sat. Please advise and thanks. I am using MS Excel 2003.

 

[SkipVought]

Hi,

I would like to select a date on a calendar
what calendar? Please be very specific.

and go back 75 days from the date selected and show
'show' in what way do you intend to show?

every Tuesday, Thursday and Saturday in the 75 day range. I would like to see the actual date of each Tue. Thur. & Sat. Please advise and thanks. I am using MS Excel 2003.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[netrusher]

The Calendar is Calendar Control 11 inserted as an object into a worksheet.
I would like the dates to be displayed in cells on the same worksheet. I hope that
helps.

 

[SkipVought]

So it could be a simple list of about 25 applicable dates?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[netrusher]

Yes it could. Thanks

 

[SkipVought]

Do you have some code that you're starting with?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Actually you could use an approch like this.

Return the selected date to a cell, let's say A2, where Row 1 are two headings, Date and Day.

A3: =A2-1

...and copy down through 74 cells.

B2: =A2

...and use Custom Number Format of ddd.

Then we could use just a few lines of code to Clear/assign filter to the three days in column B, and you'll have your dates/days.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[netrusher]

This is the code I have with my Object Calendar in the workbook. When I click on any date I would like
to go back 75 days and show me a list of dates of every Tues. Weds. & Thur. Just several rolls of cells
each with a date of a Tue, Wed. and Thur. of that 75 day date range. Sorry, I am so new to this the
code below is what I have and I am not even sure on how to post it.

Private Sub Calendar1_Click()
Cells(2, 5) = Calendar1.Value

End Sub

 

[SkipVought]

Okay, I'm sending an attachment.

The formula in column E decrements by one to 75 days.

The formula in column F returns the Week Day Abbreviation for the corresponding date.

When a date is selected by the control, it takes your date in E2 as you specified, then refreshes the AutoFilter, displaying the days you specified.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mintjulep]

See this faq68-6900

It's pretty much all you need.

 

[SkipVought]

Don't know how that FAQ will help the OP.

=TEXT(E2,"ddd")

...will return the Day of the week abbreviation.

=MOD(E2,7)

...will return a number from 0 to 6, and Tueaday, Wednesday, Thursday are 3,4,5.

Either way you can arrive at the OP's requirement.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[netrusher]

Skip,Blessings to you! With the attachment, how do I use the Table in the code. Where
do I put it?

 

[SkipVought]

Make your 75 day table a Structured Table

Select in your table and use Insert > Tables > Table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}