Extract date from file name and return in a cell

[rwm]

I have a file named "Project View 2024 02 05" where the integers are the date in the format "YYYY MM DD". I need a VBA script for a user defined function that when called, extracts the date from the filename and places it into the cell in date format. In this case, when calling the function, the cell shows "2/5/2024". I need the date to update as the filename updates to a different date as well.

 

[CephasOz]

Here's a VBA function as requested:

Function FileDate() As Date
    Dim lngPos As Long
    Dim strDate As String
    lngPos = InStrRev(ThisWorkbook.Name, ".xls")
    strDate = Left(ThisWorkbook.Name, lngPos - 1)
    strDate = Right(strDate, 10)
    FileDate = CDate(strDate)
End Function

and here's a formula to do the same

=DATEVALUE(SUBSTITUTE(RIGHT(TEXTBEFORE(TEXTAFTER(CELL("filename"),"["),".xls"),10)," ","/"))

 

[rwm]

Here's a VBA function as requested:

Function FileDate() As Date
    Dim lngPos As Long
    Dim strDate As String
    lngPos = InStrRev(ThisWorkbook.Name, ".xls")
    strDate = Left(ThisWorkbook.Name, lngPos - 1)
    strDate = Right(strDate, 10)
    FileDate = CDate(strDate)
End Function

and here's a formula to do the same

=DATEVALUE(SUBSTITUTE(RIGHT(TEXTBEFORE(TEXTAFTER(CELL("filename"),"["),".xls"),10)," ","/"))

The formula is perfect thank you so much!