Copy a date from the filename into a cell

    I am trying to remember how to do this.


    I have a file that is a month behind the current month. What I want to do is automatically have the cell with date change as I change the name of the file to the next month.


    For example:
    Currently in file:
    A1 = Apr-05
    FileName : InvoicesApr05


    Want to Change it to:
    A1 = May-05
    FileName : InvoicesMay05


    I remember that I would use the Cell("filename") function along with Find (to find the "[" in the pathname.



    How do I do this?

    Re: Copy a date from the filename into a cell


    this should work

    Code
    Function filedate()
application.volatile
filename = Mid(ActiveWorkbook.Name, 9, 5)
filedate = Format(DateValue(Mid(filename, 1, 3) & "-1-" & Mid(filename, 4, 2)), "mmm-yy")
End Function


    place this in a module in the workbook then place the formula "=filedate()" in A1
    This will update when the file name changes.

    :drum: The worst moment for the atheist is when he is really thankful and has nobody to thank.

    Re: Copy a date from the filename into a cell


    Thanks


    How would I apply this function if I had * May05.xls as the last 5 characters of the filename?

    Re: Copy a date from the filename into a cell


    this will grab the last 5 characters of the filename and extract the date

    Code
    Function filedate()
Application.Volatile
filename = Mid(Right(ActiveWorkbook.Name, 9), 1, 5)




filedate = Format(DateValue(Mid(filename, 1, 3) & "-1-" & Mid(filename, 4, 2)), "mmm-yy")
End Function

    :drum: The worst moment for the atheist is when he is really thankful and has nobody to thank.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login