Auto-Create a monthly Sign in sheet for preschool students

jonwondon

New Member
Joined
Oct 20, 2020
Messages
13
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,986
Office Version
  1. 2013
Platform
  1. Windows
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
 

jonwondon

New Member
Joined
Oct 20, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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?
 

jonwondon

New Member
Joined
Oct 20, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,986
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,986
Office Version
  1. 2013
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,986
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
399
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"),"")
 

jonwondon

New Member
Joined
Oct 20, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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?
 

jonwondon

New Member
Joined
Oct 20, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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"),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,113,833
Messages
5,544,565
Members
410,621
Latest member
S Oberlander
Top