Formula to consolidate data from multiple sheets

Please try at
A4
=DATE(2021,1,MATCH(SEQUENCE(COUNTA('1:31'!$A$4:$A$100))-1,MMULT(N(SEQUENCE(31)>SEQUENCE(,31)),SUBTOTAL(3,INDIRECT("'"&SEQUENCE(31)&"'!A4:A100")))))

B4:L4
=IFERROR(FILTER(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",0,'1:31'!A$4:A$100)&"</m></x>","//m"),1-ISERR(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",0,'1:31'!$A$4:$A$100)&"</m></x>","//m"))),"")

For more data better use Power Query or VBA

Power Query
Change file path in red

let
    Source = Excel.Workbook(File.Contents("D:\Get Data From Multiple Sheets.xlsx"), null, true),
    Filtered = Table.SelectRows(Source, each [Kind] = "Sheet" and not Text.Contains([Item], "Report")),
    AddedT = Table.AddColumn(Filtered, "T", each Table.PromoteHeaders(Table.RemoveFirstN([Data],2))),
    RemovedColumns = Table.RemoveColumns(AddedT,{"Data", "Item", "Kind", "Hidden"}),
    AddedSuffix = Table.TransformColumns(RemovedColumns, {{"Name", each _ & "Jan2021", type text}}),
    ExpandedT = Table.ExpandTableColumn(AddedSuffix, "T",Table.ColumnNames(AddedSuffix[T]{0})),
    ChangedType = Table.TransformColumnTypes(ExpandedT,{{"Name", type date}, {"Machine", type text}, {"From", type time}, {"To", type time}, {"Total Time", type time}, {"Failure#(lf)Code", Int64.Type}, {"Extention#(lf)Code", type any}, {"Delay#(lf)Code", type any}, {"Equip. Name", type any}, {"Stoppage Code", Int64.Type}, {"Stoppage Description", type text}, {"Stoppage Category", type text}})
in
    ChangedType

VBA

Sub combine()
Dim s As Worksheet
Application.ScreenUpdating = False
    For Each s In ThisWorkbook.Sheets
        If Not s.Name Like "Report*" Then
            With s.[A3].CurrentRegion.Offset(1)
                .Resize(.Rows.Count - 1).Copy Sheets("Report VBA").Cells(Rows.Count, 2).End(xlUp)(2)
                Sheets("Report VBA").Cells(Rows.Count, 1).End(xlUp)(2).Resize(.Rows.Count - 1) = s.Name & "Jan"
            End With
        End If
    Next
Application.ScreenUpdating = True
End Sub

How many rows maximum per sheet.

Hahaha. Formulae just won't work with that data size. Forget it.

Go with PQ or VBA.

We're NOT going down that way again. You wouldn't believe me about INDIRECT alternatives, but I was right.

FORGET IT. It won't work.

Please try at B4

=INDIRECT("'"&DAY($A4)&"'!R"&COUNTIF($A$4:$A4,$A4)+3&"C[-1]",)

try at B4

=LET(i,SEQUENCE(COUNTA('1:31'!$A$4:$A$1000))-1,a,MMULT(N(SEQUENCE(31)>SEQUENCE(,31)),SUBTOTAL(3,INDIRECT("'"&SEQUENCE(31)&"'!A4:A1000"))),d,MATCH(i,a),r,i-LOOKUP(i,a)+4,CELL("contents",INDIRECT("'"&d&"'!R"&r&"C"&SEQUENCE(,11),)))

Not sure you are using the term 'dynamic' correctly - what do you mean by it?