Auto-Create a monthly Sign in sheet for preschool students

[jonwondon]

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.

 

[My Aswer Is This]

Try this Vba script:

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

 

[jonwondon]

Try this Vba script:

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?

 

[jonwondon]

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?

 

[My Aswer Is This]

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

 

[My Aswer Is This]

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.

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

 

[My Aswer Is This]

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

Trying always makes thing better.

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

 

[Dossfm0q]

Greetings
What about Feed abck please
Just enter Year and month

Auto Create a monthly Sign in sheet for preschool students.xlsm
	A	B	C	D	E
1	2020	11
2
3	Day/Date	Time In	Parent Signture	Time In	Parent Signture
4	Monday 11/02/2020
5	Tuesday 11/03/2020
6	Wednesday 11/04/2020
7	Thursday 11/05/2020
8	Friday 11/06/2020
9	Monday 11/09/2020
10	Tuesday 11/10/2020
11	Wednesday 11/11/2020
12	Thursday 11/12/2020
13	Friday 11/13/2020
14	Monday 11/16/2020
15	Tuesday 11/17/2020
16	Wednesday 11/18/2020
17	Thursday 11/19/2020
18	Friday 11/20/2020
19	Monday 11/23/2020
20	Tuesday 11/24/2020
21	Wednesday 11/25/2020
22	Thursday 11/26/2020
23	Friday 11/27/2020
24	Monday 11/30/2020
25
26
27
28
Sheet

Cell Formulas
Range	Formula
A4:A28	A4	=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"),"")

 

[jonwondon]

Greetings
What about Feed abck please
Just enter Year and month

Auto Create a monthly Sign in sheet for preschool students.xlsm
	A	B	C	D	E
1	2020	11
2
3	Day/Date	Time In	Parent Signture	Time In	Parent Signture
4	Monday 11/02/2020
5	Tuesday 11/03/2020
6	Wednesday 11/04/2020
7	Thursday 11/05/2020
8	Friday 11/06/2020
9	Monday 11/09/2020
10	Tuesday 11/10/2020
11	Wednesday 11/11/2020
12	Thursday 11/12/2020
13	Friday 11/13/2020
14	Monday 11/16/2020
15	Tuesday 11/17/2020
16	Wednesday 11/18/2020
17	Thursday 11/19/2020
18	Friday 11/20/2020
19	Monday 11/23/2020
20	Tuesday 11/24/2020
21	Wednesday 11/25/2020
22	Thursday 11/26/2020
23	Friday 11/27/2020
24	Monday 11/30/2020
25
26
27
28
Sheet

Cell Formulas
Range	Formula
A4:A28	A4	=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?

 

[jonwondon]

Greetings
What about Feed abck please
Just enter Year and month

#VALUE!

#VALUE!

Cell Formulas
Range	Formula
A4:A28	A4	=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"),"")