Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 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

share|improve this answer
    
simple and well-explained! –  andy holaday Jul 11 '12 at 2:42
    
@andyholaday Thanks! –  flackend Jul 11 '12 at 12:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.