Cálculo de Número de Días

"Necesito calcular, cada día, cuántos días llevan transcurridos desde el 1 de enero del año en curso y los días que faltan para concluir el año ¿hay alguna función que realice estos cálculos?"

No existe ninguna función directa que realice estos cálculos, pero la solución es bien sencilla utilizando las funciones FECHA y AÑO. Supongamos que deseamos contar con la siguiente información actualizada diariamente:

1. la fórmula de B2 es muy sencilla:

=HOY()

2. La fórmula de B3 es igualmente sencilla utilizando la función NUM.DE.SEMANA Esta función tiene tan sólo dos argumentos NUM.DE.SEMANA(Num_de_serie;Tipo). El primer argumento se refiere a la fecha de la que queremos saber el número de semana del año que le corresponde. El segundo argumento sirve para determinar el tipo de semana, esto es, si la semana comienza el domingo, en cuyo caso escribiremos el valor 1, o si queremos considerar semanas cuyo primer día sea el lunes, en cuyo caso el valor de este argumento será 2. Así las cosas, la fórmula resultante será:

=NUM.DE.SEMANA(B2;2)

3. Para calcular los días transcurridos desde el 1 de enero del año en curso hasta la fecha actual, utilizamos la siguiente fórmula:

=B2-FECHA(AÑO(B2);1;1) Si queremos añadir un texto descriptivo al resultado podemos utilizar el operador & (CONCATENAR):

=B2-FECHA(AÑO(B2);1;1)&" días desde principio del año"

4. Finalmente para saber cuántos días restan para concluir el año en curso:

=FECHA(AÑO(B2);12;31)-B2 Añadiendo un texto descriptivo sería:

=FECHA(AÑO(B2);12;31)-B2&" días para terminar el año"

Si queremos mantener esta información siempre visible en la hoja, podemos utilizar la opción de Inmovilizar Paneles. Para ello nos situamos en la celda A7 y vamos al menú Ventana/Inmovilizar Paneles. Aparecerá un borde superior en toda la fila 7 quedando inmovilizadas las celdas que se encuentren por encima de dicha linea.

Repetición de Caracteres Concretos en una Celda

Pues parece que fue ayer pero ya ha pasado más de un mes desde mi última entrada. Prometí a mis hijos no acercarme a una hoja de cálculo durante las vacaciones y... ¡casi lo cumplo!

Me habéis mandado numerosas preguntas (supongo que debo daros las gracias...) que intentaré contestar lo antes posible. Una de las más repetidas ha sido la siguiente:

"Necesito saber el número de espacios en blanco que contiene un grupo de celdas".

Vamos a utilizar una fórmula bastante sencilla, que sé que leí hace tiempo en algún lado pero no recuerdo donde, con las funciones LARGO y SUSTITUIR. Partimos del siguiente ejemplo:

Queremos saber cuántos espacios en blanco contiene cada celda (cuestión que aunque no lo parezca puede resultar muy útil para, por ejemplo, realizar fórmulas que separen en distintas celdas los nombres y los apellidos). Lo que vamos a hacer en esencia es:

1. Contar el número total de caracteres que contiene la celda B3

2. Borrar todos los espacios en blanco del texto de B3 y escribir el resultado en C3.

3. Contar el número total de caracteres de C3.

4. Hallar la diferencia entre ambos totales. Dicha diferencia será, obviamente, el número de espacios en blancos que tiene la celda B3.

Vamos a empezar, con el objetivo de que se entienda mejor, por el paso 2. Para ello utilizaremos la función SUSTITUIR. Esta función sustituye dentro de una cadena de texto un texto original por otro nuevo específico. La sintaxis de esta función es:

SUSTITUIR(texto;texto_original;texto_nuevo; núm_de_ocurrencia)

Texto:es el texto o la referencia a una celda que contiene texto en el que desea sustituir caracteres.

Texto_original: es el texto que desea reemplazar.

Texto_nuevo: es el texto por el que desea reemplazar texto_original.

Núm_de_ocurrencia: especifica la instancia de texto_original que desea reemplazar por texto_nuevo. Si especifica el argumento núm_de_ocurrencia, sólo se remplazará esa instancia de texto_original. De lo contrario, todas las instancias de texto_original en texto se sustituirán con texto_nuevo.

Así las cosas, nos situamos en la celda C3 y escribimos la siguiente fórmula:

=SUSTITUIR(B3;" ";"") Fíjese que el segundo argumento es "espacio" y que el tercero es "" sin ningún espacio. Le estamos pidiendo a excel que sustituya los espacios en blanco que se encuentre por nada. El resultado de esta fórmula será este:

Como puede comprobar, el contenido de la celda C3 es el mismo que el de la celda B3 pero sin espacios en blanco.

Una vez hecho esto el resto es muy sencillo. Sólo nos queda "medir" el largo de ambas celdas y hallar la diferencia. Para ello hacemos lo siguiente:

en D3 escribimos: =LARGO(B3)

en E3 escribimos: =LARGO(C3)

en F3 escribimos: =B3-C3

¡Problema resuelto! Evidentemente si sólo necesitamos el número de espacios en blanco podemos resumir todas estas fórmulas en la siguiente (que escribo en la celda C3):

=LARGO(B3)-LARGO(SUSTITUIR(B3;" ";""))

En este caso hemos contado el número de espacios en blanco pero puede utilizar esta fórmula con otros caracteres simplemente sustituyendo la expresión " " por "a" ,por ejemplo.

Buscar Dentro de Fórmulas

Descargar el Archivo

"Tengo una hoja con muchos datos y con cálculos de sumatorios parciales dispersos por dicha hoja. Además de dichos sumatorios tengo otras fórmulas. Necesito localizar todas las celdas en donde existe una fórmula que esté utilizando la función SUMA, ya que debo revisar que los rangos aplicados en esta función sean correctos".

La solución a este problema es muy sencilla utilizando la herramienta Buscar. Para ello vamos a utilizar el siguiente ejemplo:

Se trata de una tabla en la que tenemos diversos datos y diversas fórmulas y lo que pretendemos es localizar aquellas que contengan la función SUMA. Para ello seguimos los siguientes pasos:

1. Vamos a l menú Edición/Buscar. Se abrirá la siguiente ventana:

2. Dentro del campo Buscar: escribimos la palabra SUMA y comprobamos que dentro de del campo Buscar dentro de: tenemos seleccionado Fórmulas. Pulsamos el botón Buscar todo. El resultado será el siguiente:

Una vez resuelto el problema planteado en esta consulta, tenemos muchas opciones para trabajar. Lo primero que cabe destacar es que esta herramienta nos presenta un listado de todas las celdas que contienen la función buscada con su fórmula correspondiente. De esta manera ya podemos comprobar desde la propia herramienta si el rango de SUMA es el que nos interesa. Además podemos hacer clic en cada uno de los resultados de la búsqueda y excel selecciona automáticamente la celda a la que hace referencia:

En esta imagen puede comprobar que al seleccionar el primer resultado de la búsqueda excel selecciona la celda en la hoja a la que hace referencia (celda B11).

También podemos realizar una selección múltiple marcando, por ejemplo, el primer resultado de la búsqueda y haciendo clic con la tecla Shift pulsada en el último resultado de la búsqueda:

Como puede comprobar en la imagen excel selecciona las tres celdas implicadas, a saber: B11, C11 y E11.

Finalmente también puede realizar una selección discontinua pulsando la tecla Ctrl y haciendo clic en los resultados de la búsqueda que le interese (por ejemplo el primero y el último):

Evidentemente, las opciones de esta herramienta son mucho más amplias y, por lo tanto, serán objeto de futuros posts.

Días Laborables Entre Dos Fechas

Descargar el Archivo

"Necesito calcular los días laborables transcurridos entre dos fechas ¿Hay alguna función que lo calcule?"

Sin problema. Resolveremos esta cuestión con la función DIAS.LAB

Partimos del siguiente ejemplo:

Queremos que en la celda C6 aparezca la diferencia de los días laborables transcurridos entre la fecha inicial indicada en la celda C2 y la fecha final indicada en la celda C4. Aplicando la función DIAS.LAB la solución es sencilla.

Nota: La función DIAS.LAB no aparece por defecto en la categoría Fecha y Hora de Excel (en versiones anteriores a Excel 2007). Para añadirla debe hacer lo siguiente: vaya al menú Herramientas/Complementos y active la casilla de verificación Herramientas para análisis. Pulse Aceptar y nuevas funciones, incluida la que nos ocupa, le aparecerán en las distintas categorías.

La sintaxis de esta función es:

=DIAS.LAB(Fecha inicial;Fecha final;Festivos)

Es importante destacar que la función DIAS.LAB considera los sábados y domingos como no laborables. Por otro lado, vamos a necesitar un listado de los días festivos del periodo a analizar. En nuestro ejemplo hemos introducido una lista de las fechas festivas en 2009 y 2010 (calendario que debe actualizarse y completarse con festivos locales):

Para que excel nos advierta si introducimos incorrectamente una fecha vamos a utilizar la herramienta de Validación de datos y la función lógica SI.

1. Seleccionamos el rango B16:B38 y le damos el nombre Fiestas (haciendo clic en el cuadro de nombres -a la izquierda de la barra de fórmulas- y escribiendo directamente dicho nombre y pulsando después Enter)

2. Nos situamos en la celda C6 y escribimos la fórmula:

3. Nos situamos en la celda C4 y vamos al menú Datos/Validación y realizamos la configuración que se muestra en las siguientes imágenes:

De esta manera además de obtener el cálculo que estábamos buscando:

si introducimos incorrectamente la fecha final excel nos advertirá:

Generar Agenda de Tareas (Gráficos Gantt)

Durante esta última semana he tenido varias consultas relativas a la generación de gráficos para el control de agendas de proyectos, o lo que es lo mismo, gráficos Gantt. Excel no presenta este tipo de gráficos por defecto pero resulta bastante sencillo conseguirlo siguiendo unos pasos...

Partimos del siguiente ejemplo donde podemos ver en la columna B la tarea; en la columna C la fecha de inicio de dicha tarea; y en la columna D la duración en días desde la fecha de inicio de cada tarea:

1. Seleccionamos el rango B2:D8 y procedemos a insertar gráfico tipo barras horizontales; subtipo barras apiladas:

2. Pulsamos directamente Finalizar. El gráfico obtenido será el siguiente:

3. Ya tenemos la base para empezar a trabajar... Lo que debemos hacer a continuación es un clic con el botón derecho del ratón sobre el eje de las Y (eje vertical) y seleccionar Formato de ejes...

4. En la pestaña Escala marcamos la opción Categorías en orden inverso. De esta manera excel colocará las tareas en el orden correcto (no como las presentaba en el gráfico anterior) y las fechas en la parte superior del gráfico. Hacemos clic encima de la leyenda y suprimimos:

5. Hacemos doble clic encima de la serie de datos de color azul, que se refiere a las fechas de inicio, y le quitamos el relleno y el borde:

6. Hacemos doble clic encima de las fechas que se encuentran en la parte superior del gráfico, para darles formato, y seleccionamos en la ventana que se abre la pestaña Escala. Excel no reconoce aquí los formatos de fecha, por lo que debe utilizar el formato general. Para hacer esto debe volver a la hoja y situarse, por ejemplo en la celda F3. En esta celda copie la fecha inicial de la primera tarea y vaya a Formato/Celda/Número y seleccione la opción General. Póngase en F4 y copie la fecha de inicio del último proyecto más el número de días de duración (en nuestro ejemplo la última fecha es el 7/8/2010 y la duración de esta última tarea es de 7 días, por lo que la fecha final será el 14/8/2010) y haga lo mismo que en el anterior caso. De esta manera obtendremos los números de serie que corresponden a estas dos fechas que nos interesan (en concreto el 40369 y el 40404 ¿Para qué? Pues volviendo a nuestra ventana de Formato de ejes en la pestaña Escala debemos escribir como Mínimo el 40369 (que es la fecha de inicio de la primera tarea) y como Máximo el 40404 (que es la fecha de finalización de la última tarea):

Y con algunas mejoras de formato (color de fuentes, color de lineas de división, etc) conseguimos un sencillo gráfico Gantt como el mostrado a continuación: