Cálculo del número de semana en Excel

Una de las funciones que no incorpora Excel de forma nativa, al menos 2003, es el calculo del número de la semana en la que estamos.

Buscando información al respecto he encontrado la siguiente fórmula que me parece muy interesante:

=INT((B3-DATE(YEAR(B3-WEEKDAY(B3-1)+4);1;3)+WEEKDAY(DATE(YEAR(B3-WEEKDAY(B3-1)+4);1;3))+5)/7)

Hay que tener la fecha en la celda B3 para llevar a cabo el cálculo.

Es interesante conocer que este cálculo no es todo lo trivial que parece.

Hay cuatro maneras diferentes de llevar a cabo el mencionado cálculo:

“1) The International Organization for Standardization (ISO) ISO8601:2000 Standard. All weeks begin on a Monday. Week one starts on Monday of the first week of the calendar year with a Thursday.

2) Excel WEEKNUM function with an optional second argument of 1 (default). Week one begins on January 1st; week two begins on the following Sunday.

3) Excel WEEKNUM function with an optional second argument of 2. Week one begins on January 1st; week two begins on the following Monday.

4) Simple week numbering. Week one begins on January 1st, week two begins on January 8th, and week 53 has only
one or two days(for leap years).”

En la página Week numbers in Excel se puede encontrar un interesante documento al respecto.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s