Encontrar una fecha a partir de la semana del año

Excel pone a nuestra disposición todo un arsenal de funciones para extraer información a partir de fechas. Por ejemplo, si tomamos la fecha de hoy, 14 de abril del 2008 podemos extraer el número de día (que es más bien obvio), el día de la semana (lunes, martes, etc), el mes y al año



Quien quiera más información sobre funciones y cálculos con fechas y horas, puede apretar el enlace Fechas y Hora en el área de etiquetas del blog (en la columna a la izquierda).

Un lector me consulta cómo obtener los días de una semana si sólo sabemos el año y el número de semana.

Supongamos que tenemos el año en la celda B1 de la hoja y el número de semana en la celda B2



Empezamos por calcular el primer día del año con la fórmula =FECHA(B1;1;1)



Cada semana tiene necesariamente 7 días, así que podemos calcular el número de días transcurridos hasta el fin de la semana que aparece en la celda B2, con la fórmula =B2*7



Como pueden apreciar, el resultado es incorrecto. Hemos agregado el control Calendario, para poder controlar el resultado de nuestra fórmula.

Lo que queremos obtener es el primer lunes de la semana, para lo cual tendremos que hacer algunas correcciones a nuestra fórmula.

La corrección la calculamos con esta fórmula

=(1>=DIASEM(FECHA(B1;1;1))*7)+1-DIASEM(FECHA(B1;1;1))

Sumamos este resultado al de nuestra fórmula original y obtenemos el último día de la semana buscada



Ahora tenemos que llevar el resultado al primer día, para lo cual todo lo que tenemos que hacer es restar 6 del resultado obtenido

Nuestra fórmula final será

=FECHA(B1;1;1)+(B2*7)+(1>=DIASEM(FECHA(B1;1;1))*7)+1-DIASEM(FECHA(B1;1;1))-6



Esta fórmula siempre nos dará el primer lunes de la semana. Por ejemplo, si buscamos la primer semana del 2008, el primer día es, obviamente, el 01/01/2008. Pero el primer lunes de esa semana es el 31/12/2007



Si queremos extraer las fechas por semanas a partir del primer día del año, es decir, por intervalos de 7 días, tenemos que usar esta fórmula

=FECHA(B1;1;1)+(B2*7)-7




El archivo con el ejemplo se puede descargar aquí

Technorati Tags: MS Excel

Otro modelo para administración de horas de trabajo con Excel

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

Operaciones con rangos que contienen errores

Si una fórmula en nuestra hoja de Excel se refiere a un rango que contiene errores, como #N/A, el resultado será también error.
Si el rango contiene sólo algunas celdas, una solución es, sencillamente, borrar el contenido de las celdas con error.
Pero hay situaciones en las cuales no queremos borrar el contenido de las celdas ya sea porque se trata de una gran cantidad de datos o por cualquier otro motivo.
En esos casos necesitamos crear fórmulas que ignoren las celdas con un valor de error en el rango.
En esta nota veremos como sumar, contar e imitar la función SUMAPRODUCTO en estas situaciones.

Empezamos por considerar un rango en el cual hay vatios tipos de errores (de hecho, todos los errores que se pueden dar en Excel)




Si usamos la fórmula =SUMA(A2:A11), el resultado será #¡VALOR!.

Para superar este inconveniente usamos esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11),0,A2:A11))}

Como siempre recordamos que las fórmulas matriciales son introducidas en las celdas pulsando simultáneamente Ctrl+Mayúsculas+Enter.

Esta fórmula crea un vector donde si un valor del rango da resultado error al ser evaluado por la función ESERROR, este valor es convertido a 0. En caso contrario se mantiene el valor original. La función SUMA realiza la operación con los valores del vector creado por la función SI.

Si queremos contar cuantos valores que no son error hay en el rango usamos esta fórmula matricial

={SUMA((NO(ESERROR(A2:A11)))*1)}

Esta misma fórmula puede escribirse de esta forma más "académica"

={SUMA(--NO(ESERROR(A2:A11)))}

También podemos usar funciones base de datos para resolver este problema.
Si el tipo de error es el mismo en todas las celdas erróneas del rango, por ejemplo #N/A , como en esta situación



definimos el área de criterios en el rango C1:C2 como mostramos en la imagen arriba y creamos la fórmula

=BDSUMA(A1:A11,A1,C1:C2)

Para contar cuantos valores no erróneos hay en la lista usamos esta otra fórmula

=BDCONTAR(A1:A11,A1,C1:C2)

Si tenemos distintos tipos de valores en la lista, en lugar de crear varios criterios, podemos usar un único criterio, como se muestra aquí



Hay que prestar atención que el área de criterios sigue siendo el rango C1:C2, pero hemos dejado la celda C1 vacía y en la celda C2 tenemos una fórmula y no un valor. Las fórmulas para sumar y contar son idénticas a las del ejemplo anterior.



Finalmente tenemos que enfrentarnos con el caso que el rango con errores aparezca en una función SUMAPRODUCTO. En este caso tenemos que desistir de usar esta función e imitarla con una fórmula matricial.
Si queremos obtener la suma de los productos de los miembros de las listas 1 y 2 como en esta imagen



tendremos que usar esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11*B2:B11),0,(A2:A11*B2:B11)))}

Si tenemos más de dos vectores, es conveniente usar nombres para señalar los rangos. Por ejemplo, en este caso creamos tres nombres Lista1, Lista2 y Lista3



El cuaderno con los ejemplos se puede descargar aquí

Technorati Tags: MS Excel