¿Como pasarle a una celda el nombre del archivo?

La fórmula para poner el nombre del archivo es esta:

=CELDA("nombrearchivo")

Pero tiene un detalle, también te pone la ruta del archivo, el nombre del archivo y el nombre de la hoja.

Ejemplo:

C:\Users\damor\Documents\Docs\Soporte expertos\[tmax010111.xlsx]Hoja1

Para obtener el nombre del archivo:

=EXTRAE(CELDA("nombrearchivo"),ENCONTRAR("[",CELDA("nombrearchivo"))+1,ENCONTRAR("]",CELDA("nombrearchivo"))-ENCONTRAR("[",CELDA("nombrearchivo"))-1)

Ejemplo:

Tmax010111.xlsx

Para obtener la fecha, si el archivo siempre tiene 4 caracteres al inicio y después 6 caracteres para la fecha, entonces la fórmula quedaría:

=EXTRAE(EXTRAE(CELDA("nombrearchivo"),ENCONTRAR("[",CELDA("nombrearchivo"))+1,ENCONTRAR("]",CELDA("nombrearchivo"))-ENCONTRAR("[",CELDA("nombrearchivo"))-1),5,6)

Ejemplo:

010111

Lo que obtuvimos es un texto, si lo quieres en formato de fecha, lo recomendable es que en otra celda pongas otra fórmula para obtener 01/01/2011:

Por ejemplo, si en la celda G5 pusiste la fórmula anterior, entonces en la celda F5 pon la siguiente fórmula:

=EXTRAE(G5,1,2)&"/"&EXTRAE(G5,3,2)&"/20"&EXTRAE(G5,5,2)

Ejemplo:

01/01/2011

Nota: El resultado no es una fecha de excel, sigue siendo un texto.}

Si lo quieres como fecha entonces en la celda F5 pon la siguiente fórmula:

=FECHA(VALOR("20"&EXTRAE(G5,5,2)),VALOR(EXTRAE(G5,3,2)),VALOR(EXTRAE(G5,1,2)))

Ejemplo:

01/01/2011

'.[Sal u dos. Dante Amor. No olvides valorar la respuesta. 
'.[Avísame cualquier duda

No es necesario una macro para esto, pero sí tendrás que contar con algunas columnas auxiliares para ir armando la fórmula... luego la podrás unificar si te da la paciencia ;)

Suponiendo que O1 obtenemos el nombre del archivo con esta fórmula:

=CELDA("nombrearchivo")

Como esto nos dará el nombre completo (con su ruta) en O2 obtenemos el nombre del libro:

=EXTRAE(O1;ENCONTRAR("[";O1;1)+1;ENCONTRAR("]";O1;1)-ENCONTRAR("[";O1;1)-1)

En O3 el nombre sin la extensión:

=IZQUIERDA(O2;LARGO(O2)-5)

Y la FEcha quedaría como te la indicó Ignacio o sino también así:

=FECHA("20" & DERECHA(O3;2);EXTRAE(DERECHA(O3;6);3;2);EXTRAE(DERECHA(O3;6);1;2))

Como dije antes, se puede intentar concatenar todas las fórmulas en 1 sola o dos.

Si se trata de tomar el nombre del archivo de la carpeta, me parece que se requiere una macro, de echo hay unas aportaciones para eso por parte de Dante y de Elsa Matilde. La verdad yo no me meto mucho en macros.