Is there any formula can read the data from the file name and mark it onto an excel file ?

Option Explicit
Sub ProcessPDFs()
    Dim oFSO        As Object
    Dim PDFFolder   As Object
    Dim PDFFile     As Object
    Dim Parts       As Variant
    Dim DateCol     As Variant
    Dim Prefix      As String
    Dim PrefixRow   As Variant
    Dim FolderPath  As String
    
    FolderPath = "C:\2023-01"
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set PDFFolder = oFSO.GetFolder(FolderPath)
    For Each PDFFile In PDFFolder.Files
        If UCase(Right(PDFFile.Name, 3)) = "PDF" Then
            Parts = Split(Left(PDFFile.Name, Len(PDFFile.Name) - 4), " ")
            Prefix = Parts(0) & "-" & Parts(1)
            Set PrefixRow = Columns("D").Find(Prefix)
            Parts = DateValue(Parts(2))
            Set DateCol = Range("2:2").Find(Parts)
            Cells(PrefixRow.Row, DateCol.Column) = "PM"
        End If
    Next PDFFile
End Sub

You're welcome...

Sub ProcessPDFs()
    Dim SrcSht      As Worksheet
    Dim PMDateRec   As Worksheet
    Dim oFSO        As Object
    Dim PDFFolder   As Object
    Dim PDFFile     As Object
    Dim Parts       As Variant
    Dim DateCol     As Variant
    Dim Prefix      As String
    Dim PrefixRow   As Variant
    Dim FolderPath  As String
    Dim DestRow     As Variant
    Dim DestCol     As Long
    
    Set SrcSht = Worksheets("sheet1")
    Set PMDateRec = Sheets("pm date")
    
    FolderPath = "C:\2023-01"
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set PDFFolder = oFSO.GetFolder(FolderPath)
    
    For Each PDFFile In PDFFolder.Files
        If UCase(Right(PDFFile.Name, 3)) = "PDF" Then
            Parts = Split(Left(PDFFile.Name, Len(PDFFile.Name) - 4), " ")
            Prefix = Parts(0) & "-" & Parts(1)
            
            ' update PM
            With SrcSht
                Set PrefixRow = .Columns("D").Find(Prefix)
                Parts = DateValue(Parts(2))
                Set DateCol = .Range("2:2").Find(Parts)
                .Cells(PrefixRow.Row, DateCol.Column) = "PM"
            End With    'source sheet
            
            'put PM dates
            With PMDateRec
                Set DestRow = .Columns("D").Find(Prefix)
                DestCol = .Cells(DestRow.Row, .Columns.Count).End(xlToLeft).Column + 1
                .Cells(DestRow.Row, DestCol).Value = Format(Date, "dd-mmm")
            End With
        End If
    Next PDFFile
End Sub

In the attached zipfile, several of the pdf's have the same CODE & UNIT, but different dates:

                                COUNT
CEN	EAP1	CEN EAP1	2
CEN	EAP5	CEN EAP5	2
CIO	BL1	CIO BL1	2
JOR	BL1	JOR BL1	2
LIV	L2	LIV L2	2
LON	BL1	LON BL1	2
LON	L1	LON L1	2

I think I have a fix, but it'll have to wait 'til tomorrow

Option Explicit
Sub ProcessPDFs()
    Dim SrcSht      As Worksheet
    Dim PMDateRec   As Worksheet
    Dim oFSO        As Object
    Dim PDFFolder   As Object
    Dim PDFFile     As Object
    Dim Parts       As Variant
    Dim DateCol     As Variant
    Dim Prefix      As String
    Dim PrefixRow   As Variant
    Dim FolderPath  As String
    Dim DestRow     As Variant
    Dim DestCol     As Long, r As Long
    
    Set SrcSht = Worksheets("sheet1")
    Set PMDateRec = Sheets("pm date")
    
    FolderPath = "C:\2023-01"
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set PDFFolder = oFSO.GetFolder(FolderPath)
    
    For Each PDFFile In PDFFolder.Files
        r = r + 1
        Sheet5.Cells(r, 1).Value = PDFFile.Name
        If UCase(Right(PDFFile.Name, 3)) = "PDF" Then
            Parts = Split(Left(PDFFile.Name, Len(PDFFile.Name) - 4), " ")
            Prefix = Parts(0) & "-" & Parts(1)
            
            ' update PM
            With SrcSht
                Set PrefixRow = .Columns("D").Find(Prefix)
                Parts = DateValue(Parts(2))
                Set DateCol = .Range("2:2").Find(Parts)
                .Cells(PrefixRow.Row, DateCol.Column) = "PM"
            End With    'source sheet
            
            'put PM dates
            With PMDateRec
                Set DestRow = .Columns("D").Find(Prefix)
                DestCol = .Cells(DestRow.Row, .Columns.Count).End(xlToLeft).Column + 1
                .Cells(DestRow.Row, DestCol).Value = Format(Date, "dd-mmm")
                
                'test for dup date and delete
                If .Cells(DestRow.Row, DestCol - 1).Value = .Cells(DestRow.Row, DestCol).Value Then
                    .Cells(DestRow.Row, DestCol).Value = ""
                End If
            End With
        End If
    Next PDFFile
End Sub