Auto-Create a monthly Sign in sheet for preschool students

jonwondon

New Member
Joined
Oct 20, 2020
Messages
39
Office Version
  1. 2019
Platform
  1. Windows
The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class"

School days are obviously Monday-Friday but they start on different days depending on what month it is. For example: Next month, November 2020's first school day will be Monday the 2nd (11/2/20). I'm trying to avoid going into the table and manually editing all the dates and days to correspond with the coming month. This task takes a good hour or two when running the front desk. Is there a way to auto generate this?

Right now the table is in word, but I feel like an excel spreadsheet would be best. Thanks in advance for your help.
Monthly.PNG
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try this Vba script:
VBA Code:
Sub Enter_Weekdays()
'Modified  10/21/2020  2:53:34 AM  EDT
Application.ScreenUpdating = False
Columns(1).ColumnWidth = 20
Dim mm As Long
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim anss As Long
Dim b As Long
Columns(1).ClearContents
mm = InputBox("Enter Month Number")
b = 1

Select Case mm
    Case 9, 4, 6, 11: mmm = 30
    Case 1, 3, 5, 7, 8, 10, 12: mmm = 31
    Case 2: mmm = 29
End Select

    For i = 1 To mmm
        anss = Weekday(DateAdd("d", i - 1, mm & "/1/" & Year(Date)))
            Select Case anss
                Case 2 To 6
                Cells(b, 1).Value = WeekdayName(anss) & vbNewLine & DateAdd("d", i - 1, mm & "/1/" & Year(Date))
                b = b + 1
            End Select
    Next
Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this Vba script:
VBA Code:
Sub Enter_Weekdays()
'Modified  10/21/2020  2:53:34 AM  EDT
Application.ScreenUpdating = False
Columns(1).ColumnWidth = 20
Dim mm As Long
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim anss As Long
Dim b As Long
Columns(1).ClearContents
mm = InputBox("Enter Month Number")
b = 1

Select Case mm
    Case 9, 4, 6, 11: mmm = 30
    Case 1, 3, 5, 7, 8, 10, 12: mmm = 31
    Case 2: mmm = 29
End Select

    For i = 1 To mmm
        anss = Weekday(DateAdd("d", i - 1, mm & "/1/" & Year(Date)))
            Select Case anss
                Case 2 To 6
                Cells(b, 1).Value = WeekdayName(anss) & vbNewLine & DateAdd("d", i - 1, mm & "/1/" & Year(Date))
                b = b + 1
            End Select
    Next
Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub
So how would I enter this? as a macro in Excel?
 
Upvote 0
Ok I figured it out! This is perfect! BUT it works for November 2020 and December 2020. Once I enter 2021 - the dates are off again.

Is there a way instead of entering "Month #" - I could just enter Month/Year and have it automated?
 
Upvote 0
Ok I figured it out! This is perfect! BUT it works for November 2020 and December 2020. Once I enter 2021 - the dates are off again.

Is there a way instead of entering "Month #" - I could just enter Month/Year and have it automated?
The script is using the current year. So if in 2021 you run the script it will use 2021

Would this not work for you?

Will you actually be doing this in 2020 for the year 2021.

If you really need this modified where user must enter Month and year let me know and I will modify the script
 
Upvote 0
So if you need a script where user enters Month and year try this:
Inputbox will popup
User should enter Month like this: 12,2021
This would be for Dec 2021
Be sure and enter comma like I did.
You will see the input box defaults to current month and current year but you can modify this.

VBA Code:
Sub Enter_Weekdays()
'Modified  10/22/2020  1:06:52 AM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Columns(1).ColumnWidth = 20
Dim mm As String
Dim i As Long
Dim yy As Long
Dim anss As Long
Dim b As Long
Columns(1).ClearContents
b = 1
mm = InputBox("Enter Month then , then year", "Like This: 12,2020", Month(Date) & "," & Year(Date))
Dim LString As String
Dim LArray() As String
LString = mm
LArray = Split(LString, ",")
mm = LArray(0)
yy = LArray(1)

Select Case mm
    Case 9, 4, 6, 11: mmm = 30
    Case 1, 3, 5, 7, 8, 10, 12: mmm = 31
    Case 2: mmm = 29
End Select

    For i = 1 To mmm
        anss = Weekday(DateAdd("d", i - 1, mm & "/1/" & yy))
            Select Case anss
                Case 2 To 6
                Cells(b, 1).Value = WeekdayName(anss) & vbNewLine & DateAdd("d", i - 1, mm & "/1/" & yy)
                b = b + 1
            End Select
    Next
Columns(1).AutoFit
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You caused a error. Maybe " & vbNewLine & mm & "  is not a proper entry" & vbNewLine & "Entry should look like this" & vbNewLine & "12,2020"


End Sub
 
Upvote 0
Ok this script works the same as previous one but instead of entering 12,2020
Enter 12/2020

Trying always makes thing better.

VBA Code:
Sub Enter_Weekdays()
'Modified  10/22/2020  1:43:38 AM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Columns(1).ColumnWidth = 20
Dim mm As String
Dim i As Long
Dim yy As Long
Dim anss As Long
Dim b As Long
Columns(1).ClearContents
b = 1
mm = InputBox("Enter Month then / then year", "Like This: 12/2020", Month(Date) & "/" & Year(Date))
Dim LString As String
Dim LArray() As String
LString = mm
LArray = Split(LString, "/")
mm = LArray(0)
yy = LArray(1)

Select Case mm
    Case 9, 4, 6, 11: mmm = 30
    Case 1, 3, 5, 7, 8, 10, 12: mmm = 31
    Case 2: mmm = 29
End Select

    For i = 1 To mmm
        anss = Weekday(DateAdd("d", i - 1, mm & "/1/" & yy))
            Select Case anss
                Case 2 To 6
                Cells(b, 1).Value = WeekdayName(anss) & vbNewLine & DateAdd("d", i - 1, mm & "/1/" & yy)
                b = b + 1
            End Select
    Next
Columns(1).AutoFit
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You caused a error. Maybe " & vbNewLine & mm & "  is not a proper entry" & vbNewLine & "Entry should look like this" & vbNewLine & "12/2020"


End Sub
 
Upvote 0
Greetings
What about Feed abck please
Just enter Year and month

Cell Formulas
RangeFormula
A4:A28A4=IF(MONTH(DATE($A$1,$B$1,ROWS($A$4:A4))+(((CEILING(ROWS($A$4:A4)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,WEEKDAY(DATE($A$1,$B$1,1))+1-2,WEEKDAY(DATE($A$1,$B$1,1))-2),5)/5)-1)*2)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,1,0))=$B$1,TEXT(DATE($A$1,$B$1,ROWS($A$4:A4))+(((CEILING(ROWS($A$4:A4)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,WEEKDAY(DATE($A$1,$B$1,1))+1-2,WEEKDAY(DATE($A$1,$B$1,1))-2),5)/5)-1)*2)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,1,0),"dddd mm/dd/yyyy"),"")
 
Upvote 0
Greetings
What about Feed abck please
Just enter Year and month

Cell Formulas
RangeFormula
A4:A28A4=IF(MONTH(DATE($A$1,$B$1,ROWS($A$4:A4))+(((CEILING(ROWS($A$4:A4)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,WEEKDAY(DATE($A$1,$B$1,1))+1-2,WEEKDAY(DATE($A$1,$B$1,1))-2),5)/5)-1)*2)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,1,0))=$B$1,TEXT(DATE($A$1,$B$1,ROWS($A$4:A4))+(((CEILING(ROWS($A$4:A4)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,WEEKDAY(DATE($A$1,$B$1,1))+1-2,WEEKDAY(DATE($A$1,$B$1,1))-2),5)/5)-1)*2)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,1,0),"dddd mm/dd/yyyy"),"")
where do i enter the first piece of data to get this working?
 
Upvote 0
Greetings
What about Feed abck please
Just enter Year and month

#VALUE!
#VALUE!
Cell Formulas
RangeFormula
A4:A28A4=IF(MONTH(DATE($A$1,$B$1,ROWS($A$4:A4))+(((CEILING(ROWS($A$4:A4)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,WEEKDAY(DATE($A$1,$B$1,1))+1-2,WEEKDAY(DATE($A$1,$B$1,1))-2),5)/5)-1)*2)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,1,0))=$B$1,TEXT(DATE($A$1,$B$1,ROWS($A$4:A4))+(((CEILING(ROWS($A$4:A4)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,WEEKDAY(DATE($A$1,$B$1,1))+1-2,WEEKDAY(DATE($A$1,$B$1,1))-2),5)/5)-1)*2)+IF(WEEKDAY(DATE($A$1,$B$1,1))=1,1,0),"dddd mm/dd/yyyy"),"")
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

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