Selecting the date of every Monday and Wednesday from a date range

Question

I need a formula or a macro to help automate a grade sheet's dates.

We have class every Monday or Wednesday only. I would like to vlookup from an input table of each quarter's date range, for example, Sept. 10 - Oct 24. The code should auto-insert the date of every Monday and Wednesday in a row at the top of my grade sheet.

Every year I use the same Excel workbook I built to average and rate the grading with no problem, however I can't seem to get this one right. Currently I have to enter each date by hand.

Solution 1

Here's a formula that will do that.

• In your first column, put your starting date.
• In the next column, put this formula:

=IF(TEXT(A1,"ddd")="Mon",A1+2,A1+5)

Which says, if the previous column is a Monday, this column is that day + 2. If it's not, this column is that day + 5.

Then copy the formula into the subsequent cells until you've reached the last date of your quarter.

Here's a video I found that shows how to copy a formula: Quickly Copy/Paste Data, Formulas, Text in Excel Using the Quick Fill Handle

You can format that row so that the dates are displayed how you like by right-clicking the row number (which selects the whole row) and choosing Format Cells....

Under the Number tab, choose Date from the category list and then pick a type. Or you can pick Custom from the category list and customize it. I used this under custom:

ddd, mmm d

Which is display like this:

Mon, Sep 10

This page lists what each "code" means (i.e. "ddd" is the day abbreviated, "Mon"):

http://www.ozgrid.com/Excel/CustomFormats.htm

Solution 2

A simpler solution is:

=IF(WEEKDAY(A1,11)=5,A1+3,A1+2)

This uses the Weekday function with the parameter (return type) 11, which means the week starts on a Monday and ends on a Sunday.

So if the weekday is 5 (i.e. Friday) it adds 3 days (which = Monday). Otherwise it adds 2 days (so Monday + 2 = Wednesday and Wednesday + 2 = Friday).

The previous solution only gave you one set of Mon, Wed, Fri. This formula will allow you to repeat the sequence for more than one week.