¿Cómo utilizar las funciones de fechas en Excel? ¿Cual es el primer mes del año que comienza en jueves? ¡Excel lo sabe¡

¿Cual es el primer mes del año que comienza en jueves? ¡Excel lo sabe¡

Utilización de las funciones de fechas en Excel

De bote pronto.  Rápido, contesta: ¿cual es el primer mes de un año que comienza en un día determinado de la semana?, es decir dime ¿cual es el primer mes del próximo año 2014 que comienza en jueves?

Si nunca te has hecho esta pregunta, posiblemente podrás seguir el resto de tu vida. Pero si bien te pica la curiosidad, o estas preparando un proyecto o un evento, preguntas de esta guisa puede que te hagas.

He preparado un pequeño modelo en Excel, y que en base a dicha pregunta trabajaremos, las fórmulas matriciales, los formatos condicionales, las listas personalizadas, funciones como elegir, coincidir, índice, y el uso de formas en una Excel. ¿Quién da mas? Animo, descárgate aquí el e modelo en Flashforward, y repasa algunos temas de Excel que puedas tener oxidados. 

Explorando la pregunta.

Para saber en que día empieza un mes podemos utilizar la función DIASEM(fecha, tipo), que devuelve el número de la semana que representa una fecha concreta, y este dependerá de en que día consideras que empieza la semana, en Domingo, lunes, etc.

Así que podemos construir una tabla de donde pongamos en el eje vertical los años y en el horizontal los meses. Calculamos para cada combinación de mes y año, el número de día de la semana de del primer día, y para aclararnos, convertimos ese numero en texto, mediante la función ELEGIR (;), y para los argumentos utilizaremos las listas personalizadas de los días de la semana. Sitúate en B7 y;

• Crear la fecha con FECHA(año;mes;dia):   “=Fecha($A7;columnas()-1;1)”. El truko de esta función es utilizar columnas(), que devuelve el número de la columna de la celda en la que esta la formula, y como hemos empezado en la columna B, que es la columna #2, pues tendríamos el número del mes que deseamos, sin tener que poner en una fila con los números de los meses.
• Calcular el día de la semana, para ello utilizamos DIASEM(núm_de_serie,tipo) con la opción #2 que corresponde a la semana que empieza por lunes como día #1 y domingo como #7. “DIASEM(FECHA(…);2)” nos devolverá un número del 1 al 7.
• Poner en texto el numero de día de la semana mediante ELEGIR(núm_índice,valor1,valor2,...) : “ELEGIR(DIASEM(FECHA($A8;COLUMNA()-1;1);2);"Lun";"Mar";"Mié";"Jue";"Vie";"Sáb";"Dom")”
•  Ya lo tienes, ya esta. 

De esta forma hemos creado una tabla donde se recogen el día de la semana en que comienzan los meses de diferentes años.

¿Y esto no lo puedes poner en una formula y en una sola celda?

Pues a eso vamos, en eso esta el reto.

Pongamos en la celda G5 Año, y en la celda G6 el número de año que deseas. En la celda c4, pon el número de la semana que te interesa, empezando el lunes como 1.

En H4 escribiremos esta función:

“{=ELEGIR(+COINCIDIR(C4;DIASEM(FECHA(G4;FILA(1:12);1);2);0);"Enero";"Febrero";"Marzo";"Abril";"Mayo";"Junio";"Julio";"Agosto";"Septiembre";"Octubre";"Noviembre";"Diciembre")}” ojo es matricial y tienes que introducir la fórmula pulsando Shiht+Ctrl+Enter” .

Las “tripas” de la fórmula

·      El “core” de la fórmula FILA(1:12), generamos una variable matricial, de 12 filas por 1 columna que contiene los 12 números de mes que tiene el año, y sobre esta variable se construye toda la formula.

·      Hemos generado una matriz de 12x1, y que servirá de contenedor para las doce fechas de inicio de mes de ese año, cosa que logramos mediante FECHA(año;mes;dia), y estos resultados son transformados en el número de la semana mediante DIASEM(fecha;tipo). Así logramos tener una matriz de 12X1 elementos que contiene los números de semana correspondientes al primer día de mes de cada uno de los meses de ese año.

·      Pues si tienes una lista, una matriz, aplicamos la función COINCIDIR(valor_buscado;matriz_buscada;tipo_de_coincidencia) y nos devolverá el número del orden que ocupa el primer elemento de la matriz en que dicho valor (día de la semana) coincida con el día que tu has elegido.

·      Y todo esto lo integramos en el operador del valor de la función ELEGIR, que al coincidir el número del mes con el numero de orden, nos devolverá el nombre del mes en formato texto.

Así de fácil, y si quieres automatizarlo, puedes explorar el modelo y encontrarás mas cosas como insertar controles de formularios y vincular ese valor a una celda, de forma que si cambia el valor de la celda, cambia el valor del texto de la forma insertada o los formatos condicionales, etc..

Reconversión Excel. MAC.

·      Me he vuelto un poco mas loco buscando las listas personalizadas de Excel. En Windows están aquí.

En MAC podremos encontrarlas en Excel > Preferencias, o también con “⌘ + ,”.

·      Para fijar referencias absolutas pulsar la tecla de “⌘ +T”, es el equivalente al F3 de Excel para Windows.