Calculando las semanas del año en Excel

Últimamente he recibido varias preguntas sobre temas relacionados con cálculos de fechas. Una de ellas era cómo calcular el número de semana de una determinada fecha.

A primera vista parece una pregunta sencilla. Si tenemos instalado el Análisis Toolpak, podemos utilizar la función NUM.DE.SEMANA(núm_de_serie;tipo), donde "num_de_serie" es el número de serie de la fecha y "tipo" es una variable que indica si la semana comienza el domingo (tipo = 1) o el lunes(tipo = 2).

Si NUM.DE.SEMANA da resultado #¿NOMBRE?, podemos usar su equivalente en inglés WEEKNUM.

Sencillo, no? Pues bien, no!. Sucede que hay que tener en cuenta varias cuestiones antes de lanzarnos a calcular el número de semana.

El resto de esta entrada está basado en los excelentes artículos de Chip Pearson y Ron de Bruin sobre el tema.

La primer semana comienza el 1 de enero; pero, cuando comienza la segunda, al 8 de enero o el primer lunes después del 1 de enero? Que día marca el comienzo de la semana, el lunes o el domingo?

Veamos las distintas formas de calcular el número de semana. Un archivo con las distintas fórmulas se puede bajar aquí.

Número de semana absoluto.

De acuerdo este concepto, la primer semana empieza siempre el 1 de enero y concluye el 7 de enero, sin tener en cuenta el día de la semana. De esta manera tendremos 53 semanas al año. La semana 53 tendrá un día o dos, si se trata de un año bisiesto.
Para calcular el número de semana absoluto usamos la fórmula
=TRUNCAR(((A1-FECHA(AÑO(A1),1,0))+6)/7)
Si en la celda A1 tenemos la fecha de hoy (28/06/2006), esta fórmula da el resultado 26.

Número de semana de Excel.

Excel ofrece la función WEEKNUM (o NUM.DE.SEMANA). Para usar esta fórmula debemos instalar previamente el Analysis Toolpak.
Esta función calcula el número de semana contando desde el domingo o el lunes. Por lo tanto, la primer semana puede tener entre 1 y 7 días.
La sintaxis de esta fórmula es
NUM.DE.SEMANA(núm_de_serie;tipo)
Núm_de_serie es una fecha dentro de la semana. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones.
Tipo es un número que determina en qué día comienza la semana. El valor puede ser 1 (domingo) o 2 (lunes).
La función =WEEKNUM(A1,2), donde A1 contiene la fecha 28/06/2006 da el resulta 27. En cambio la función =WEEKNUM(A1,1) da 26.

Número de semana ISO (International Organization for Standardisation )

De acuerdo a la norma ISO, la semana empieza siempre un lunes y termina un domingo. La primer semana del año es la que contiene el primer jueves. Es decir, la primer semana tendrá siempre 4 días por lo menos.
La fórmula para calcular el número de semana de acuerdo al estándar ISO es

=ENTERO((A1-FECHA(AÑO(A1-DIASEM(A1-1)+4),1,3)+DIASEM(FECHA(AÑO(A1-DIASEM(A1-1)+4),1,3))+5)/7)

Si la celda A1 contiene la fecha 28/06/2006, el resultado será 26.

Finalmente, si queremos utilizar fórmulas en lugar de la función WEEKNUM,

Si la semana empieza en domingo:

=1+ENTERO((A1-(FECHA(AÑO(A1),1,2)-DIASEM(FECHA(AÑO(A1),1,1))))/7)

Si la semana empieza en lunes:

=1+ENTERO((A1-(FECHA(AÑO(A1),1,2)-DIASEM(FECHA(AÑO(A1),1,0))))/7)


Categorías: Funciones&Formulas

Technorati Tags: WEEKNUM

La función INDIRECTO con cuadernos Excel cerrados.

Con la función INDIRECTO de Excel, podemos establecer referencias dinámicas a un rango o una celda en un cuaderno remoto (referencia externa). En esta entrada sobre el uso de la función INDIRECTO, daba un ejemplo de cómo establecer referencias a celdas que se encuentran en distintas hojas de un mismo cuaderno Excel.
De la misma manera podemos establecer referencias a celdas en hojas de otros cuadernos (lo que he llamado cuadernos remotos). Como ya he explicado, la función INDIRECTO devuelve la referencia especificada por una cadena de texto. El problema con INDIRECTO es que si se hace referencia a un libro remoto (una referencia externa), ese libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devolverá el valor de error #¡REF!

En esta nota sobre el tema en Daily Dose of Excel se señalan varias soluciones, de las que mencionaré sólo dos:

1 – El complemento desarrollado por Laurent Longre, MOREFUNC.XLL, que incluye la función INDIRECT.EXT que funciona como INDIRECTO, pero también con cuadernos cerrados. Esta función tiene varias limitaciones:
- permite establecer referencias sólo a una única celda, por lo que no sirve como segundo argumento en la función BUSCARV (VLOOKUP)
- no funciona con si se refiere a nombres en cuadernos cerrados


2 – La función PULL de Harlan Grove. Esta es una UDF (user defined function) cuyo código se puede obtener aquí. Este función permite crear referencias a rangos y por lo tanto, puede usarse con BUSCARV (VLOOKUP), por ejemplo =VLOOKUP("search_text",PULL("'C:temp[book1.xls]sheet!'A1:B20"),2,0)

Ambas funciones tienen en común el ser lentas.


Categorías: Funciones&Formulas
Technorati Tags: INDIRECTO

Hojas de Calculo on-line – Google Spreadsheets, IRows, NumSum y ZohoSheet

Hace unos días atrás Google anunció la salida de su hoja de cálculo on-line,
Google Spreadsheets. A pesar de lo que afirman algunos comentaristas, no creo que en esta etapa, Google Spreadsheets sea una amenaza a la hegemonía de Excel en lo que a hojas de cálculo se refiere, lo cual supondría un masivo pasaje de las aplicaciones desktop a las aplicaciones on-line.
Google Spreadsheets está desprovista de herramientas como gráficos, tablas dinámicas, funciones matriciales, macros y uso de nombres, para mencionar los más evidentes. Además supongo que hay no pocas cuestiones de seguridad y privacidad no resueltas.

En mi opinión, en esta etapa, las distintas hojas de cálculo on-line que existen cumplirán dos funciones:
1 – alternativa gratuita para particulares e instituciones pequeñas, siempre que el problema de privacidad no sea crítico;
2 – herramienta para publicar archivos existentes en la red cómodamente

Google Spreadsheets no es la única hoja de cálculo on-line disponible. Curiosamente, ninguno de los comentaristas que leído menciona a alguna de estas aplicaciones. Antes de Google Spreadsheets ya existían IRows, NumSum y ZohoSheet.

He estado "jugando" un poco con estas aplicaciones y aquí resumo mis impresiones:

Google Spreadsheets:
No me gustó: el interfaz es un tanto incómodo al principio para quien esté acostumbrado al de Excel; no tiene la posibilidad de crear gráficos. Al tratar de importar una hoja donde uso nombres, recibo un aviso del tipo "Ooops, server error", pero no me dan ningún indicio de cuál es el problema.
Me gustó: tiene muchas funciones, incluyendo SUMAPRODUCTO; permite pasar de una hoja a otra con un interfaz similar al de la pestañas en Excel; al importar un archivo, cuando no puede resolver una fórmula, exhibe los valores numéricos que aparecen en las celdas de la hoja.


No me gustó: ciertos aspectos del interfaz no son cómodos, como pasar de una hoja a otra; como Google, tampoco IRows sabe interpretar rangos con nombres; tampoco tiene una fórmula equivalente a SUMAPRODUCTO. Al surgir problemas en la subida de archivos, el anuncio que aparece es más explícito que el de Google, pero no lo suficiente. Al subir un archivo, si no puede resolver las fórmulas o interpretar los rangos, muestra el valor #import error# en las celdas, en lugar del valor numérico, como hace Google.
Me gustó: en general el interfaz resulta bastante familiar; permite crear gráficos.


No me gustó: por definición los archivos importados son públicos!; sólo sube la primer hoja, de manera que si tenemos un cuaderno con varias, tendremos que subirlo "en etapas".


No me gustó: al subir un archivo, sino consigue resolver una fórmula, muestra un valor de "#invalid formula!#" en lugar del valor numérico original. Según dicen permite usar casi todas las funciones principales de Excel, pero no cuenta con una lista de funciones de donde poder elegir.
Me gustó: el interfaz, fuera del menú, es muy intuitivo; permite crear gráficos; utiliza pestañas para pasar de una hoja a otra.

Mis conclusiones: tomando como cierto lo del uso de las funciones de Excel, ZohoSheet parece ser la más completa para desarrollar hojas on-line junto con IRows, que me parece más intuitivo y fácil de usar. Google Spreadsheets parece más adecuada para subir archivos existentes aunque le falten los gráficos. Pero es de suponer que seguirá desarrollándose e incorporará gráficos y nuevas funcionalidades en el futuro.



Categorías: Manejo de Datos_, Varios_

Technorati Tags: Hojas de cálculo online