Calcular intervalos en Excel con SIFECHA

Supongamos que la fecha de hoy es el 1/1/2007 y nos preguntamos cuántos días faltan hasta la apertura de los Juegos Olímpico. Si queremos hacer el cálculo con Excel, ponemos la fecha actual en una celda, la fecha de la apertura en otra y en una tercera efectuamos la resta:




Como vemos, 583 días. Si queremos expresar el resultado en términos de años, meses y días, podemos usar la función SIFECHA (DATEDIF en la versión inglesa). Esta función, que es una función total y absolutamente normal de Excel pero no está documentada, excepto en XL2002, a pesar que existe desde la versión 5 de Excel.

La función SIFECHA usa tres argumentos: fecha de inicio, fecha final y tipo de intervalo. Siguiendo con nuestro ejemplo, podemos usar esta fórmula para calcular el intervalo en días

=SIFECHA(A1,B1,"d") que da 583

si queremos calcular la cantidad de meses usamos

=SIFECHA(A1,B1,"m") que da 19, es decir la cantidad entera de meses en el intervalo

y si queremos la cantidad de años

=SIFECHA(A1,B1,"y") que da 1, la cantidad entera de años.

Si queremos calcular la cantidad de días por encima de la cantidad entera de años en el intervalo, usamos el argumento "yd" de la siguiente manera

=SIFECHA(A1,B1,"yd") que da 217, es decir la cantidad de días desde el 01/01/2008 hasta el 06/08/2008

De la misma manera podemos calcular la cantidad entera de meses, por encima de la cantidad de años en el intervalo con

=SIFECHA(A1,B1,"ym") que da 7 (hay que recordar que siempre obtenemos números enteros)

o días no incluidos en la cuenta de los meses

=SIFECHA(A1,B1,"md") que da 5

Como ven, podemos combinar estas fórmulas y exhibir el resultado de la siguiente manera



La función SIFECHA no tiene un argumento que nos permita calcular las semanas. Para esto podemos usar la fórmula

=ENTERO((B1-A1)/7) que da 83

Si queremos expresar el intervalo hasta las Olimpíadas en términos de años, meses, semanas y días, tenemos que usar esta fórmula para calcular las semanas

=ENTERO(SIFECHA(A2;B2;"md")/7), es decir, primero calculamos la cantidad de días "libres" (no incluidos en la cuenta de los meses) y los dividimos por 7.



Si queremos expresar el resultado en una sola celda, incluyendo el texto, podemos usar la concatenación de esta manera

=SIFECHA(A2;B2;"y")&" año "&SIFECHA(A2;B2;"ym")&" meses "&SIFECHA(A2;B2;"md")&" días"

Nuestra querida lengua castellana nos complica un poco las cosas, ya que debemos respetar los singulares y los plurales, para lo cual usaremos la función SI

=SIFECHA(A2;B2;"y")&SI(SIFECHA(A2;B2;"y")=1;" año ";" años ")&SIFECHA(A2;B2;"ym")&SI(SIFECHA(A2;B2;"ym")=1;" mes ";" meses ")&"y "&SIFECHA(A2;B2;"md")&SI(SIFECHA(A2;B2;"md")=1;" día";" días")

Como esta fórmula es un tanto larga y engorrosa, podemos incluirla en un nombre para facilitar la lectura de las celdas en nuestra hoja. Lo que hacemos es poner esta fórmula en un nombre



Una aplicación más práctica de esta función es crear una tabla de antigüedad de empleados, como me pedía uno de mis lectores:



En la celda B1 ponemos la función =HOY(), para obtener el día corriente; en las celdas donde queremos calcular la antigüedad usamos el nombre "lapso_rel" que contiene la fórmula usada en "lapso" con una pequeña pero importante modificación. Como hemos de copiar este nombre a lo largo de un rango, debemos usar referencias relativas a las celdas de la fecha inicial de los empleados, pero la referencia a la fecha corriente debe ser absoluta:

=SIFECHA(Hoja1!B3;Hoja1!$B$1;"y")&SI(SIFECHA(Hoja1!B3;Hoja1!$B$1;"y")=1;" año ";" años ")&SIFECHA(Hoja1!B3;Hoja1!$B$1;"ym")&SI(SIFECHA(Hoja1!B3;Hoja1!$B$1;"ym")=1;" mes ";" meses ")&"y "&SIFECHA(Hoja1!B3;Hoja1!$B$1;"md")

Otra variante sería utilizar la función HOY() dentro de la fórmula, en lugar de ponerla en una celda auxiliar

=SIFECHA(Hoja1!B3;Hoja1!HOY();"y")&SI(SIFECHA(Hoja1!B3;Hoja1!HOY();"y")=1;" año ";" años ")&SIFECHA(Hoja1!B3;Hoja1!HOY();"ym")&SI(SIFECHA(Hoja1!B3;Hoja1!HOY();"ym")=1;" mes ";" meses ")&"y "&SIFECHA(Hoja1!B3;Hoja1!HOY();"md")




Technorati Tags: MS Excel

Exportar datos de Excel a archivo texto.

Una tarea corriente es importar archivos de texto a Excel, para lo cual existen varias posibilidades.
Otra tarea similar es generar un archivo texto para exportar datos de una hoja Excel a una aplicación externa. Algunas de estas aplicaciones aceptan archivos texto con formato .csv (comma separated values); otras aplicaciones exigen archivos texto delimitado con tabulaciones.
En ambos casos usamos el menú Guardar como y elegimos alguna de las opciones de texto




Si elegimos la opción .csv Excel genera un archivo de texto cuyos campos están delimitados por comas. Supongamos esta hoja Excel



Si la exportamos usando la opción .csv obtenemos este resultado



Como pueden ver, un archivo texto cuyos campos están separados por ";".

En los casos de exportar a una aplicación que no acepta este formato, usamos la opción texto delimitado con tabulaciones. El resultado es



El problema surge cuando la aplicación a la cual queremos exportar los datos exige que los campos tengan una longitud determinada. En esta nota mostraremos como crear un archivo texto con campos de longitud determinada por el usuario.

A la hoja Excel que contiene los datos a exportar la llamaremos "BD" (base de datos). Agregamos una fila por encima de la fila 1, de manera que los encabezamientos queden en la fila 2. Ahora ponemos en las celdas correspondientes de la fila 1 el "ancho" deseado para cada campo



El próximo paso consiste en agregar una hoja, que llamaremos "ascii". En celda A1 de esta hoja ponemos esta fórmula

=BD!A3&REPETIR(" ";BD!A$1-LARGO(BD!A3))

Esta fórmula crea un valor compuesto por el contenido de la celda A3 (siendo 3 la primer fila de la hoja BD con datos a exportar) y una cantidad de espacios en blanco hasta completar el largo deseado para el campo. En nuestro caso "Jorge" tiene un largo de cinco, pero el campo "Nombre" tiene que tener 15. Los 10 espacios necesarios son calculados por la expresión BD!A$1-LARGO(BD!A3)
Copiamos esta fórmula a lo largo del rango necesario



Ahora tenemos que guardar esta hoja como archivo texto delimitado con tabulaciones, tal como mostramos más arriba. Es recomendable crear una copia de la hoja usando la opción Mover o copiar (clic con el botón derecho en la pestaña de la hoja) a un libro nuevo y asegurándonos de haber señalado la opción "crear una copia"



Al guardar este nuevo libro, que contiene sólo la hoja con los datos, recibimos este mensaje



Apretamos "Si" y guardamos el libro. El resultado es



Como ven, el ancho de los campos está de acuerdo a nuestras definiciones.

Un problema potencial se presenta cuando el largo del valor de la celda en BD es mayor que el ancho requerido para el campo.
Por ejemplo, si el ancho para el campo "Ciudad" es sólo de 15 caracteres, cuando ponemos Santiago de Chile (17 caracteres) en la hoja BD, obtensmo un resultado de error en la hoja "ascii"



Esto se debe a que la función REPETIR no puede aceptar un número negativo como segundo argumento.
En esto casos tenemos dos opciones:
1 – cambiar el parámetro del ancho del campo (lel valor en la celda C1 en nuestro ejemplo);
2 – cambiar la fórmula para que "recorte" el texto al ancho requerido, desechando los caracteres supernumerarios. Para eso usamos esta fórmula

=SI(LARGO(BD!C5)>BD!C$1;IZQUIERDA(BD!C5;BD!C$1);BD!C5&REPETIR(" ";BD!C$1-LARGO(BD!C5)))

que nos dará como resultado



es decir, recortando Santiago de Chile (17 caracteres) a Santiago de Chi (15 caracteres).

El archivo con el ejemplo esta a vuestra disposición aquí.

Technorati Tags: MS Excel

Validación de datos entre varias hojas Excel – otra variante

El método que propongo en mi nota anterior para validación de datos entre varias hojas de Excel no funciona en algunos casos, como le ha sucedido a uno de mis lectores. No he logrado encontrar una explicación por qué en mi ejemplo el método funciona pero en el archivo de mi lector no.


18/02/2012
Esta entrada ha sido actualizada con esta nota.