excel formula to return m/d/yyyy based on filename

[ewong]

hi,

i have a question. i currently have this excel formula to grab the excel file name:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

the file name will always be in this format m.dd.yyyy human resources report.xls

e.g.: 8.27.2012 human resources report, 7.28.2012 human resources report, etc

i want to create a formula that will grab the month from the file name.

so if the file name is 8.27.2012 human resources report.xls, the formula will return 8/1/2012.

the day will always be the first, year will be current year. month will depend on the file name. if the filename is august (8th month), then the month would be 8.

whats the formula for this?

thanks!

[martindwilson]

Please Login or Register  to view this content.

[ewong]

thankss, it's working