Obtener una fecha a partir de la semana del año en Excel

Es una práctica muy común enumerar las semanas del año y asociar ciertas actividades o eventos al número de semana en el que ocurren, pero la realidad es que no estamos tan acostumbrados a dicha métrica y se hace necesario obtener una fecha a partir del número de semana del año.

El problema que resolveremos es el siguiente: dado un número de año y un número de semana obtendremos la fecha en la que inicia y termina dicha semana para ubicarla fácilmente en nuestro calendario. Pero antes de comenzar con el desarrollo de la solución debo explicar un poco sobre las semanas del año.

Número de semanas del año

Un año tiene 365 días por lo que siempre tendremos 52 semanas que es el resultado de dividir ese número de días entre siete. Por un instante podríamos pensar que todo es muy sencillo ya que solo bastaría con identificar los primeros siete días del año como la primera semana y comenzar a hacer la cuenta a partir de ahí, pero es precisamente con la identificación de la primera semana del año donde comienzan las discrepancias.

Para empezar, algunas personas consideran el primer día de la semana como el lunes, mientras otros consideramos el día inicial como el día domingo, pero eso no es todo, si el día 1 de enero es viernes, ¿cuándo empieza y termina la primera semana del año? Tomemos como ejemplo el año 2010:

El primer método comúnmente utilizado para numerar las semanas del año indica que la primera semana será aquella que incluya el día 1 de enero. Para nuestro ejemplo, si consideramos que la semana inicia en domingo y termina en sábado, entonces la primera semana del año 2010 inicia el 1 de enero y termina el sábado 2 de enero. Por el contrario, si decimos que la semana inicia en lunes y termina en domingo, entonces la primera semana del año 2010 comienza el 1 de enero y termina el domingo 3 de enero.

A parte de las dos alternativas ya mencionadas, existe una tercera opción que está documentada bajo la norma ISO 8601 que indica que todas las semanas inician en lunes y que la primera semana es la que contenga el primer jueves del año. Con tales condiciones, la primera semana ISO del año 2010 inicia el día lunes 4 de enero y termina el domingo 10 de enero.

Funciones de Excel para el número de semana

De manera predeterminada, la función NUM.DE.SEMANA asigna la primera semana del año a la semana que contenga el día 1 de enero considerando el domingo como el primer día de la semana. Así que, para obtener el número de semana de una fecha podemos utilizar la siguiente fórmula:

=NUM.DE.SEMANA(A2)

Si necesitamos considerar el día lunes como el primer día de la semana, entonces debemos modificar la fórmula de la siguiente manera:

=NUM.DE.SEMANA(A2,2)

La única fórmula que representa un desafío es el cálculo de la semana ISO y para ello utilizamos la siguiente fórmula:

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

Pero si tienes Excel 2013 ya no es necesario utilizar esta última fórmula sino que será suficiente utilizar la función ISO.NUM.DE.SEMANA. Observa cómo se comportan todas las fórmulas mencionadas con los días del año 2010:

Tal como lo mencionamos en la sección anterior, existen diferentes métodos para asignar el número de semana a una fecha y estas tres fórmulas lo asignan de acuerdo al método seleccionado. Si quieres saber un poco más sobre estas fórmulas consulta el artículo Calcular el número de semana en Excel.

Tomaremos los datos de la imagen anterior para tratar el asunto medular de este artículo y que es cómo obtener una fecha a partir de la semana del año en Excel. Es decir, hacer el proceso inverso de las fórmulas recién mostradas.

Obtener una fecha a partir del número de semana

La lógica de la primera fórmula que voy a presentar está basada en el hecho de encontrar el día domingo a partir del cual comenzaré la cuenta para la primera semana del año, recordando que dicha cuenta puede iniciar en el año anterior. Para eso utilizaré la función RESIDUO y la función DIASEM, y suponiendo que el año está en la celda D2 la fórmula es la siguiente:

=FECHA(D2,1,1) - RESIDUO(DIASEM(FECHA(D2,1,7)), 7)

La función DIASEM devolverá un número entre 1 y 7 (Domingo =1, Sábado = 7) indicando qué día de la semana es el séptimo día del año. La función RESIDUO obtendrá el residuo de dicho valor dividido por siete y que significarán los días de diferencia entre el 1 de enero y el domingo anterior. Al hacer la resta obtengo la fecha del domingo a partir del cual puedo comenzar a contar las semanas del año:

Lo único que me hace falta en esta fórmula es sumar la cantidad de días correspondientes a la semana en cuestión. Eso se logra fácilmente de la siguiente manera:

=FECHA(D2,1,1) - RESIDUO(DIASEM(FECHA(D2,1,7)), 7) + (E2-1)*7

La único que he agregado a esta segunda fórmula es la suma final, donde hago la resta de uno al valor de la semana y lo multiplico por siete. La resta es importante porque así aseguramos que para la semana 1 no se realizará ninguna suma adicional de días. Solo a partir de la semana 2 se comenzará la suma de días tomando como punto de partida el domingo inicial. Observa cómo esta fórmula obtiene correctamente el inicio de la segunda semana del año:

Para obtener la fecha donde termina la semana simplemente sume seis al resultado de la fecha de inicio. Puedes notar del ejemplo anterior que todas les fechas de inicio son un día domingo y las fechas de final son en día sábado.

Fecha con inicio de semana en lunes

Si necesitas considerar el inicio de la semana en lunes, entonces debemos hacer una pequeña modificación a la fórmula anterior, específicamente a la función DIASEM:

=FECHA(D2,1,1) - RESIDUO(DIASEM(FECHA(D2,1,7),2), 7) + (E2-1)*7

La única diferencia es el valor 2 como el segundo argumento de la función DIASEM para indicar que deseamos considerar el lunes como el inicio de semana. De esta manera la función RESIDUO obtendrá la diferencia respecto al día lunes. Observa el resultado de utilizar esta fórmula:

Fecha a partir de la semana ISO

Ahora necesitamos olvidarnos de los dos métodos anteriores porque la lógica de las semanas ISO es totalmente diferente. La norma indica que las semanas inician en día lunes y que la primera semana es la que contiene el primer jueves del año y para obtener ese valor utilizo la siguiente fórmula:

=FECHA(D2,1,8) - DIASEM(FECHA(D2,1,4),2)

La fórmula toma como base el primer día de la segunda semana del año y resta el valor del día de la semana correspondiente al cuarto día, de manera que como resultado obtengamos la fecha del primer jueves del año. Ya que la norma indica que el conteo inicia en lunes, entonces hago una resta de 3 días para llegar a dicha fecha:

=FECHA(D2,1,8) - DIASEM(FECHA(D2,1,4),2) - 3

Esta fórmula ya me devuelve la fecha a partir de la cual puedo iniciar el conteo de semanas ISO y por lo tanto solo restará sumar la cantidad de días que corresponden a la semana en cuestión:

=FECHA(D2,1,8) - DIASEM(FECHA(D2,1,4),2) - 3 + (E2-1)*7

Observa como esta última fórmula obtiene correctamente la fecha de inicio para cualquier número de semana ISO:

Ahora ya tienes tres fórmulas que te ayudarán a obtener una fecha a partir de la semana del año en Excel sin importar el método de conteo de semanas implementado. Descarga el libro de trabajo donde encontrarás los ejemplos desarrollados en este artículo.