Autofill dates excluding certain weekdays

[Barruel]

Hi.

First post here, so I salute everyone in this forum.

I'm not too savvy when it comes to Excel, but I've always been able to solve stuff (even complex stuff) just by googling a bit about it. This time is different and I've just had to register in this forum seeking help. I've even searched this forum for my issue but found nothing useful.

I'm a teacher in an high school. I have a number of groups which have classes at certain workdays but not in every workday. I have for example class x on mondays, tuesdays and thursdays, class y on tuesdays, wednesdays and freedays, and so on.

I need to log certain stuff for every class, and I want Excel to autofill me a list of dates when I have class x (or y, or z).

How can I tell Excel to show me a list of dates which include only certain workdays (say mondays, tuesdays and thursdays)? Is this possible?

TIA

PS: I'm working with Excel 2000, but I own also Office 2007, so any working solution will be OK.

 

[diddi]

welcome aboard (from an x-chalkie) (oops dont tell the rest of em)

if you are setting up say 1 term at a time, you markbook will only be relatively short so a simple solution would be to put in consequitive rows in your date column:

First row: initial date (and day?) (eg A4)
in A5: =A4 + 1
in A6 =A4 + 3
in A7 =A4 + 4

then copy and paste the set of 3 cells down the page

 

[Barruel]

welcome aboard (from an x-chalkie) (oops dont tell the rest of em)

if you are setting up say 1 term at a time, you markbook will only be relatively short so a simple solution would be to put in consequitive rows in your date column:

First row: initial date (and day?) (eg A4)
in A5: =A4 + 1
in A6 =A4 + 3
in A7 =A4 + 4

then copy and paste the set of 3 cells down the page

Hi diddi.

Thanks for the answer. Unfortunately, I need to fill the dates for the whole academic course, which spans from september to june.

The series would be like you describe. For example, for classes on mondays, tuesdays and thursdays would be Cell, Cell +1, Cell +3, Cell +4, Cell +5, Cell +8 and so on, but Excel can't follow the series by autofilling. It does weird things.

I intend to get just the list of dates and then paste it in a series of Word tables which will be printed, so I can log the stuff on paper.

 

[diddi]

what about a short macro:

Sub MakeDates()
    Dim Start As Date
    Start = DateSerial(2010, 9, 1)
    Cells(1, 1) = Start
    Cells(2, 1) = Start + 1
    Cells(3, 1) = Start + 2
    For Row = 4 To 250 Step 3
        Cells(Row, 1) = Cells(Row - 3, 1) + 7
        Cells(Row + 1, 1) = Cells(Row - 2, 1) + 7
        Cells(Row + 2, 1) = Cells(Row - 1, 1) + 7
    Next Row
End Sub

 

[diddi]

and format column A as whatever date you want, eg with the day name included or whatever. you can add a gap line for each week and other variations...

 

[Barruel]

what about a short macro:

Sub MakeDates()
    Dim Start As Date
    Start = DateSerial(2010, 9, 1)
    Cells(1, 1) = Start
    Cells(2, 1) = Start + 1
    Cells(3, 1) = Start + 2
    For Row = 4 To 250 Step 3
        Cells(Row, 1) = Cells(Row - 3, 1) + 7
        Cells(Row + 1, 1) = Cells(Row - 2, 1) + 7
        Cells(Row + 2, 1) = Cells(Row - 1, 1) + 7
    Next Row
End Sub

This works fantastic. It even doesn't miss the correct days when changing months and years. Just a thing: can you set it to fill the data horizontally instead of vertically (that is, every date in a new column instead of in a new row)?.

Also, I'd like to exclude holidays (present in a range of cells), but that will be overly complicated I guess.

Thanks, diddi. Much appreciated.

 

[Barruel]

Just a thing: can you set it to fill the data horizontally instead of vertically (that is, every date in a new column instead of in a new row)?.

Ok. I accomplished this with Paste special + Transpose. Would be nice to do it directly from the macro, but this is working. I'm googling about how to do the holiday trick. Overly complicated, indeed.

Also, there are no templates in Excel just like Word's *.dot files? The macros seem to be stored in the book. Can they be made available for every new spreadsheet?

 

[diddi]

the columns would be like this

Sub MakeDates()
    Dim Start As Date, Col as long
    Start = DateSerial(2010, 9, 1)
    Cells(1, 1) = Start
    Cells(1, 2) = Start + 1
    Cells(1, 3) = Start + 2
    For col = 4 To 250 Step 3
        Cells(1,col) = Cells(1,col - 3) + 7
        Cells(1,col + 1) = Cells(1,col - 2) + 7
        Cells(1,col + 2) = Cells(1,col - 1) + 7
    Next col
End Sub

do you have a list of holiday dates

 

[Barruel]

the columns would be like this

Sub MakeDates()
    Dim Start As Date, Col as long
    Start = DateSerial(2010, 9, 1)
    Cells(1, 1) = Start
    Cells(1, 2) = Start + 1
    Cells(1, 3) = Start + 2
    For col = 4 To 250 Step 3
        Cells(1,col) = Cells(1,col - 3) + 7
        Cells(1,col + 1) = Cells(1,col - 2) + 7
        Cells(1,col + 2) = Cells(1,col - 1) + 7
    Next col
End Sub

do you have a list of holiday dates

Dates in columns tested and working wonders. How will it be to start filling from the currently selected cell instead of from A1? Otherwise it's perfect.

As for the holidays list, I don't have one right now. I should make one. Do you need just a place in the macro to input the range of cells which contain holiday dates or do the dates themselves need to be explicitly present in the macro?

 

[diddi]

not perfect? cant have that

Sub MakeDates()
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long
    Start = DateSerial(2010, 9, 1)
    SRow = Selection.Row
    SCol = Selection.Column
    Cells(SRow, SCol) = Start
    Cells(SRow, SCol + 1) = Start + 1
    Cells(SRow, SCol + 2) = Start + 2
    For Col = SCol + 3 To SCol + 200 Step 3
        Cells(SRow, Col) = Cells(SRow, Col - 3) + 7
        Cells(SRow, Col + 1) = Cells(SRow, Col - 2) + 7
        Cells(SRow, Col + 2) = Cells(SRow, Col - 1) + 7
    Next Col
End Sub