No pocas de las entradas del blog reproducen, de una manera u otra, respuestas que doy a consultas de lectores. Esta nota se originó en la consulta de un lector sobre cómo crear en forma automática una hoja para administrar horas de trabajo. Mi lector quería que hubiera una hoja por cada mes del año y que en ella hubiera una columna para día del mes, con la fecha del día y el nombre del día. Esta hoja tendría que actualizarse automáticamente de acuerdo al nombre del mes. Es decir, para enero tendría que haber 31 columnas, para febrero de este año (2008) 29, etc.
La hoja se vería así
En la primer fila tenemos una inicial para cada día de la semana, L – lunes, M – martes, etc. Este valor tiene que actualizarse de acuerdo al día del mes. Así si el 1ro. de enero del 2008 es un martes, tiene que aparecer M.
El desafío era hacerlo sin macros, sólo con fórmulas. En esta nota describo la
solución que propuse.
Empezamos por definir una
constante matricial, "
meses", cuyo objetivo veremos más adelante
meses={"enero";"febrero";"marzo";"abril";"mayo";"junio";"julio";"agosto";"setiembre";"octubre";"noviembre";"diciembre"}
Ahora necesitamos una fórmula que extraiga el nombre de la hoja activa. Para esto usamos una fórmula con las funciones EXTRAE y CELDA
=EXTRAE(CELDA("filename",A1),ENCONTRAR("]",CELDA("filename",A1))+1,256)
Esta fórmula funciona de la siguiente manera:
1 – la fórmula =CELDA("filename",A1) da como resultado el nombre completo del archivo. Por ejemplo, en una hoja llamada Marzo la fórmula daría "D:\Blog\[horas mantenimiento.xls]Marzo".
Hay que tener en cuenta que la función CELDA sólo con archivos que hayan sido guardados previamente. Así que antes de poner esta fórmula por primera vez, debemos guardar el archivo.
2 – La fórmula =ENCONTRAR("]",CELDA("filename",A1))+1 nos da la posición del símbolo "]", que funciona como indicador de donde empieza el nombre de la hoja. En nuestro ejemplo, la fórmula da 34
3 – Usamos ambas fórmulas como argumentos en la función EXTRAE, para extraer el nombre de la hoja.
Nuestro próximo paso es "armar" la fecha en la línea 2, usando el nombre del mes que obtenemos del nombre de la hoja. Para esto ponemos en la celda B2 esta fórmula
=FECHA(2008,COINCIDIR(EXTRAE(CELDA("filename",A1),ENCONTRAR("]",CELDA("filename",A1))+1,256),
meses,0),1)
Aquí ven el uso del nombre "
meses". Como no podemos usar el nombre de la hoja, que es texto, para armar la fecha con la función FECHA, calculamos la posición del nombre del mes en la constante matricial.
El resultado es "01/01/2008", pero como estamos interesados en exhibir sólo el número del día, usamos el formato personalizado "dd"
En las celdas del rango C2:AF2 ponemos sencillamente la fórmula C2+1, los que nos da la sucesión del día del mes.
Para obtener el día de la semana usamos esta fórmula
=ELEGIR(DIASEM(B2,2),"L","M","M","J","V","S","D")
En la función DIASEM ponemos 2 como valor del segundo argumento para indicar que el lunes es el primer día de la semana.
Nuestro modelo está casi completo, excepto por un pequeño problema. Por ejemplo, si creamos la hoja "Febrero", tendremos demasiadas columnas. Después del 29, veremos que aparece 01 y 02. La fecha completa es, por supuesto, 01/03/2008 y 02/03/2008. Recordemos que hemos dado formato "dd" a las celdas.
Para corregir esto podemos hacer sencillamente eliminar las columnas innecesarias. Pero mucho más elegante es usar Formato Condicional, para hacer desaparecer las columnas supernumerarias.
Todo mes tiene necesariamente 28 días. Por lo tanto aplicamos el formato condicional sólo a las columnas AD, AE y AF. Después de seleccionar las tres columnas, abrimos el menú de formato condicional y en la opción "la fórmula es", ponemos esta fórmula
=MES(AE$2)<>MES($B$2)
Es decir comparamos el mes de las últimas tres fechas con el mes de la primer fecha. En caso de ser distinto, aplicamos el formato condicional
En la pestaña Fuente ponemos el color blanco (o el mismo color del fondo); en la pestaña Bordes elegimos la opción "ninguno" y en la pestaña Tramas elegimos la opción "sin color".
Ahora, sólo veremos las columnas de los días del mes en cuestión.
Technorati Tags: MS Excel
