Calcular intervalos en Excel con SIFECHA
sábado, agosto 25, 2007
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 581
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,"m") 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
34 comments:
Hola:
Nuevamente yo. (anteriormente solicite ayuda para conteios ciclicos)
ahora tengo un dilema.
debo entregar a un usuario un reporte que le facilite su toma de deciciones
esto para el area de compras principalmente. el caso es que logre hacer el reporte en cierta forma usando tablas dinámicas, pero no quiero que realice ninguna operacion (suma, multiplicacion, diferencia etc) sobre los datos que agrego a dicha area. es decir simplemente quiero que me los muestre.
ejemplo:
tabla: proveedor, marca, estilo, color, talla, costo, descuento, iva, util, precio
- coloco proveedor como encabezado (ya que las consultas las va a realizar por proveedor)
- seguido de marca (un proveedor puede tener mas de una)
- despues en columna Intento colocar El Estilo y tal vez alguno otro de los datos mencionados, pero al Igual que al colocarlos en el area de datos me trata de realizar la Operacion que viene predeterminada.
el problema que encuentro aqui es q no me da opcion a dejar los datos asi sin Operacion alguna. (Habrá alguna otra forma de hacer esto????)
lo que el usuario quiere hacer es seleccionar el proveedor y una marca del mismo y
con ello verá un listado de los estilos (productos) q nos da ese proveedor asi como los demas datos de cada estilo (producto), con lo cual el comprador podrá tener un mayor-mejor criterio para realizar sus pedidos.
como le mencione con anterioridad ya se hace con la tabla dinámica pero tambien el Excel hace una operacion sobre los datos que le pongo y eso es lo q no quiero
que sume o promedie los %'s o precios como lo hace actualmente :(
algo asi es lo que quiero que de de resultado
proveedor
marca
estilos color material costo desc precio util
1 bco piel 1 3% 5 25%
2 bco-mno piel 10 3% 50 25%
1 bco piel 1 3% 5 25%
1 bco piel 1 3% 5 25%
espero explicarme. y que me pueda ayudar a encontrar una buena solucion
gracias y saludos desde mexico.
Hola!... encuentro increíblemente útil tu blog pero tengo una duda que no logré clarificar.
Tengo un rango de celdas al cual le asigné el nombre "Validos", y estoy necesitando una fórmula que, al momento de teclear un valor, me diga si existe en ese rango de celdas o no.
Por ejemplo, si en el rango de celdas tengo 10,20,30,40,50 y en la celda C2 (la que estoy evaluando) meto 10 me dice "Encontrado", pero si meto 20 me dice "No encontrado", es decir, solo me está evaluando el primer elemento del rango, cómo logro que me busque en todas las celdas del rango???
Ésta es la fórmula que estoy usando.
=SI(C2=Validos,"Encontrado","No encontrado")
Gracias por tu atención.
Hola José Juan
tu consulta no está relacionada con el tema de la nota. Mandame una copia del archivo o un ejemplo y seguiremos la consulta por mail (jorgedun@gmail.com).
Hola Pogo
fíjate en esta nota sobre cómo determinar si un valor existe en un rango de Excel
Gracias!. Es útil tu solución, encontré ésta otra:
=SI(BUSCAR(C2,cctvalidas)=C2,"Encontrado","No encontrado")
Donde C2 es la celda evaluada y cctvalidas es el nombre del rango de celdas + 1, es decir, si los datos van de a1 hasta a20 se debe crear el rango desde a1 hasta a21, por qué?, no lo sé jejeje. Saludos!
En primer lugar felicitarte por tu blog.
Seguidamente una pequeña corrección
en la expresión siguiente
=SIFECHA(A1,B1,"m") que da 1, la cantidad entera de años.
Las celdas deben referenciarse a A2 y B2 y el tercer parámetro para años es "y"
Efectivamente Josep. Gracias por la observación.
Hola Jorge, en realidad se ve que eres todo un Master en Excel, y en realidad agradecía todo el apoyo que has aportado a esta comunidad.
Pues te comento que tengo un problema y es el siguiente:
Tengo que llevar el control de una Planillas la cuales posen un serial correlativo.
Al principio pense es sencillo, resto el ultimo serial por el el serial inicial y me da el numero de planillas en total.
Cuando me sorpresa me he dado cuenta que me suma un numero de mas, ejmp:
=C6-B6
el valor de la Columna c6 es: 3 y el de la b6 es:1 y la formula en excel me da:2 "es logico" 3-1=2, pero sabemos que el numero de existencia de planillas son 3 y no 2.
Otro ejemplo que realice es este:
el valor de la Columna c6 es: 121 y el de la b6 es:20 y la formula en excel me da como resultado:101, lógico 121-20=101 pero sabemos que la existencia real de planilla es de 100.
Que solucion me puede brindar?
Hola Carlos
no se si enterpreto correctamente tu consulta, pero cuando calculas una diferencia y quieres que ambos extremos estén incluidos en el cálculo, debes agregar 1.
Para contar el número de elementos en un rango es más conveniente usar las funciones CONTAR o CONTARA.
Primero de todo felicitar al autor del blog, que gran descubrimiento acabo de hacer y lo que pienso aprender de él. Gracias por hacerlo posible.
Un apunte sobre el calculo de las fechas, en el caso que queden 7 dias o mas surge el problema de que nos pone la semanas correctamente pero nos sigue manteniendo los dias. Por ejemplo si pongo fecha 30/08/2008 me salen 4 semanas pero me sigue poniendo 29 dias. Lo he solucionado poniendo en la casilla D5 la funcion RESIDUO(SIFECHA(A2;B22;"md");7) y ya me pone correctamente 1 dia.
Espero aprender mucho con tu blog, un saludo.
Saludos, y felicitaciones por el BLOG.
Mi nombre es Nestor y Cuando uso la función SIFECHA me devuelve error de numero(#¡NUM!), como soluciono esto. Aparentemente no tengo la función "SIFECHA" en el excel 2002 y Excel 2007.
Saludos
Hola,
sí tienes la función en tu versión de Excel. Si no la tuvieras el error sería #NOMBRE?
El error #NUM! indica que hay algo errado con los argumentos de la función.
Intenta cambiar el orden de las fechas en la función. En esta función el primer argumento es la fechas "menor". Por ejemplo, si las fechas son 10/07/2007 y 24/10/2007, el primer argumento debe ser la celda que contiene 10/07/2007.
Hola, para empezar felicitarte por tu blog, esta muy bien. Yo lo que quiero hacer es pasar horas a años, meses, dias y horas. osea imaginate:
A1= 250horas y que en A2 me pasara esa cifra a lo que dije anteriormente.
Es para calcular el tiempo trabajado.
Muchas gracias
Hola Martín aquí va una explicación breve. Más adelante publicaré en el blog una explicación más detallada.
Suponiendo que en la celda A1 tienes el número 250 (y no con formato de hora y/o fecha, sino como número), tienes que dividirlo primero por 24. Así que ponemos 24 en la celda A2, y en la celda A3 hacemos la división lo que da 10,416667. En una celda libre pones esta fórmula
=SIFECHA(0;A3;"y")&" años - "&SIFECHA(0;A3;"m")&" meses - "&SIFECHA(0;A3;"md")&" días y "&HORA(A3-ENTERO(A3))&" horas"
el resultado será
0 años - 0 meses - 10 días y 10 horas
Si en A1 tienes la hora con formato de horas, es decir 250:00:00 (lo que quiere decir que has usado el formato [hh]:mm:ss), no hay necesidad de dividir por 24.
exelente blog los felicito por esta gran labor de ayudar a los demas y quisera saber si me pudieran ayudar con lo siguiente.... Quisiera hacer una hoja donde me calcule el costo menor que me dan mi proveedor... osea incluir los precios de mis proveedores y que me calculara cual es el costo menor y de que proveedor es ese costo
Hola
la consulta no está relacionado con el tema de la nota (esto es un blog, no un foro). De todas maneras, tienes que usar la función MIN para obtener el menor valor de un rango. Luego puedes usar INDICE o alguna otra función de búsqueda para obtener el nombre del proveedor cuyo precio es el que aparece como resultado de MIN.
Hola Jorge,
Yo estoy calculando la antigüedad de los trabajadores y no tengo la formula sifecha;:que fórmula puedo usar en su defecto???
Muchas gracias
Alicia A
Hola Alicia
¿qué versión de XL estás usando? Creo que SIFECHA ya existía en XL en versiones anteriores a XL97. Por algún motivo, esta función no aparece en la lista de funciones, pero que existe, existe.
Te recomiendo probar escribir la función con esta sintaxis:
=SIFECHA(fecha anterior, fecha posterior, "m")
Si no funciona usar DATEDIF en lugar de SIFECHA.
Mi estimado, ante todo dejame felicitarte por excelente e ilustrativo Blog. En esta ocasión, recurro a ti para una consulta:
En la siguiente fórmula, =SIFECHA(A3,B3,"y")&" años, "&SIFECHA(A3,B3,"ym")&" meses y "&SIFECHA(A3,B3,"md")&" días" ´
Cuál es el significado de los términos "y", "&", "ym", "md". Muchas gracias por tu ayuda, atentamente Héctor.
Nemi,
SIFECHA calcula intervalos en términos de años, meses o días. De aquí:
"y" = años (year en inglés)
"m" = meses (month)
"d" = días (days)
"ym" = la cantidad de meses por encima de la cantidad de años calculada
Está todo explicado en la nota.
Muchas gracias por la información, me fue muy últil.
Sólo hice una pequeña modificación en la información para que me saliera la diferencia exacta de AA/MM/DD
=SIFECHA(A1,B1,"y") da la cantidad de años 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
HOLA DISCULPA LA MOLESTIA TENGO UNA DUDA
TENGO DOS FECHAS UNA INCIAL Y DE CIERRE
LA INICAL ES 31/08/2007 Y LA DE CIERRE ES 30/06/2010 SACANDO CON LA FORMULA ME SALE 33 MESES PERO SACANDO A MANO SACO 35 DIAS ESA ES LA DUDA FAVOR SI ME PUDIESES AYUDAR GRACIAS
Estimado,
aún calculando a mano desde agosto del 2007 hasta junio del 2010 debe haber 34 meses. ¿Qué fórmula estás usando y con qué argumentos?
podrian ayudarme como calculo los dias horas y minutos si en diferentes celdas tengo fecha inicial, hora inicial, fecha final y hora final saludos
Combinando en una celda la fecha inicial con la hora inicial y en una segunda celda la fecha final con la hora final.
Hola,
muchas gracias por el post, es muy interesante. Pero no consigo hacerlo funcionar con Excel 2007. Para empezar con algo sencillo, estoy usando esta fórmula:
=SIFECHA(E2;HOY( );”Y”)
Me sale un error del tipo #¿NOMBRE?
Le doy a 'Mostrar pasos de cálculo...' y aparece una ventana que muestra la evaluación de la fórmula:
SIFECHA(26907; 40810; "Y")
El número '40810' está en cursiva.
La expresión "Y" aparece subrayado.
He probado a cambiar "Y" por "y", "years", "A", "años", ... pero el error persiste.
¿Puedes echarme un cable?
Gracias de antemano.
Luis,
el error #¿NOMBRE? indica que Excel no reconoce alguna de las dos funciones SIFECHA u HOY. Revisa las funciones. Otra posibilidad es usarlas en inglés: DATEDIF y TODAY
estuve como loco buscando la bendita función SIFECHA y no la encontraba, busque en el mismo excel y encontré su equivalente que es FRAC.AÑO.
No exactamente. FRAC.AÑO calcula en días la fracción de año entre dos fechas. SIFECHA calcula días, años, meses. etc.
SIFECHA no esta documentada en la ayuda de Excel.
No encuentro la función SIFECHA por ningún lado, cuando la uso me da un error #NAME?. ¿Que se puede hacer?. Tampoco la reconoce en inglés.
En inglés la función se llama DATEDIF. Asegurate de poner el tercer argumento (la unidad de tiempo) entre comillas. Si no pones las comillas, por ejemplo
SIFECHA(A1,B1,ym)
Excel trata de interpretar ym como si fuera un nombre y al no existir de el erro #NAME!
Gracias.
Muy útil si usas todavía Excel 2003.
Un saludo
hola quisiera saber si me podrias ayudar con dicha funcion ..estoy haciendo una planilla para ingresar la fecha q ingresa un interno ej.12/02/2000(celda A), en la otra celda coloco el computo de su pena ej.:2años y 6 meses...mi consulta es como obtengo el resultado de la finalizacion de la pena? o sea q m indique la fecha de finalizacion...muchas gracias
Tienes que hacerlo con la función FECHA.MES. Esta función hace el cálculo por meses, aasí que en tu caso sería
=FECHA.MES(FECHANUMERO("12/02/2000"),30)
o si en la celda A1, por ejemplo, está la fecha de ingreso y en la celda A2 los meses
=FECHA.MES(A1,A2)
Publicar un comentario