Autofill dates excluding certain weekdays

B

Barruel

New Member
Joined
Nov 21, 2010
Messages
13
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
 
Upvote 0
diddi said:
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
Click to expand...

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.
 
Upvote 0
what about a short macro:

Code: 
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
 
Upvote 0
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...
 
Upvote 0
diddi said:
what about a short macro:

Code: 
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
Click to expand...

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.
 
Last edited:
Upvote 0
Barruel said:
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)?.
Click to expand...

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?
 
Last edited:
Upvote 0
the columns would be like this

Code: 
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
 
Upvote 0
diddi said:
the columns would be like this

Code: 
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
Click to expand...

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?
 
Upvote 0
not perfect? cant have that

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
372
KRice
K
J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
496
Jaxi
J
P
  • Question
Find nearest WORKDAY before a date
Replies
6
Views
239
Fluff
Fluff
C
  • Question
Formula Result Change to next Workday
Replies
3
Views
323
Fluff
Fluff
F
  • Solved
Issue with VBA code I created for work
Replies
7
Views
652
KAPOK
K

Forum statistics

Threads
1,215,339
Messages
6,124,362
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top