miércoles, octubre 19, 2011

Cálculo de semanas en Excel

Excel no tiene una función que calcule la cantidad de semanas entre dos fechas. Para calcular la cantidad de años, meses y días podemos usar la “indocumentada” función SIFECHA.

En esa nota muestro cómo calcular también la cantidad de semanas, pero dado que sigo recibiendo consultas sobre el tema mostraré aquí la técnica en forma sucinta. A quien no esté familiarizado con SIFECHA le recomiendo leer la nota mencionada.

Supongamos que queremos calcular la cantidad de meses, semanas y días entre dos fechas



En la celda B4 usamos la fórmula

=SIFECHA(comienzo,fin,"ym")

donde “comienzo” es un nombre que se refiere a la celda B1 y “final” es un nombre que se refiere a la celda B2.

(aclaración: dependiendo de las definiciones del sistema hay que usar “a” en lugar de “y” para los años)

En B5 ponemos =SIFECHA(comienzo,fin,"md")

Finalmente en B8 usamos =B4&" meses y "&B5&" días"

Excel no incluye el día de comienzo cuando calcula el intervalo, de manera que quien quiera incluirlo en el resultado deberá agregar “+1” a la fórmula.

Nosotros queremos este cálculo



En la celda B5 (semanas) ponemos

=ENTERO(SIFECHA(comienzo,fin,"md")/7)

para calcular la cantidad de semanas de los días no incluidos en la cuenta de los meses.

En la celda B6 ponemos

=SIFECHA(comienzo,fin,"md")-(B5*7)

Es decir, descontamos del total de días calculado por SIFECHA la cantidad de días de las semanas en la celda B5.

Si queremos una fórmula que no dependa del B5 tendríamos que usar

=SIFECHA(comienzo,fin,"md")-(ENTERO(SIFECHA(comienzo,fin,"md")/7)*7)

La fórmula en B9 es

=B4&" meses, "&B5&" semanas y "&B6&" días"

25 comentarios:

  1. :( No me sale.

    Me estoy liando...Mira que tienes paciencia y dedicación para explicar pero no lo termino de ver. Los 9 meses y 4 días (5 si incluimos el mismo día) los tengo claros, pero 1 semana, no lo veo.

    Entiendo que son los días que no entran en la cuenta de los meses, pero si yo multiplico 9 meses por 4 semanas/mes me da 36+1 que nos ha salido son 37.

    Si vamos al calendario, la semana 1 comienza en el 3 de enero (lunes) y el 12 de octubre cae en la semana 41, luego son 40 semanas y 5 días.

    ResponderBorrar
  2. Volviendo al tema del cálculo de semanas y viendo que la función SIFECHA no termina de convencerme he imporvisado otras fórmulas.

    Comento para todos por si alguien más le interesa.
    Ejemplo: cálculo actual de semanas de embarazo
    A1=>Fecha de la última regla
    A2=> 05/06/2011
    B1=>Semanas
    C1=>Días
    D1=>semanas+días

    En celda B2:
    =ENTERO((HOY()-A2)/7)
    En celda C2:
    =RESIDUO((HOY()-A2);7)
    En celda D2:
    =B2&"+"&C2

    De esta forma al introducir la fecha en A2 05/06/2011, hoy 20/11/2011 tendríamos el dato en D2= 19+4

    Antes de dar por buena esta fórmula, me voy al calendario y cuento para ver si es correcta. Cual es mi sorpresa que me da 18 semanas y 4 días.

    ¿Dónde está el error?. Demasiados cálculos por hoy. Mañana será otro día.

    MIL GRACIAS

    ResponderBorrar
  3. excelente tutorial, me sirvio mucho... muchas gracias por compartirlo.

    ResponderBorrar
  4. Claro, sencillo y útil. GRACIAS!!!!

    ResponderBorrar
  5. Arantxa,
    enviame el archivo por mail privado.

    ResponderBorrar
  6. Os puede servir de algo esto, solo para semenas entre fechas:

    =TRUNCAR(((E7-E8)+6)/7)+(DIASEM(E7)=DIASEM(E8))

    01/05/2011 - E7
    01/01/2011 - E8

    Re: 18

    ResponderBorrar
  7. Buenas tardes, tu calendario me sirvio de mucho pero lo que estoy buscando es que ese calendario; donde yo le hara click a cualquier celda de la misma columna(obviamente que se encuentre en blanco) me salga el calendario y elegir la fecha. Podrias ayudarme

    ResponderBorrar
  8. Muy buena fórmula que no conocía. He probado todas las que han proporcionado otros usuarios y todas funcionan perfectamente. gracias a todos.

    ResponderBorrar
  9. Hola Jorge,

    Se podrian saber cuantos dias lectivos hay entre 2 fechas? Es decir, sin tener en cuenta sabados y domingos?

    Saludos,

    ResponderBorrar
  10. Si, con la función DIAS.LAB. En el blog hay varias notas sobre el tema.

    ResponderBorrar
  11. Estimado, felicitaciones por el blog, es excelente, me ha ayudado bastante.

    Respecto de las fechas, tengo un problema para el cálculo de un simple promedio diario, pero para una semana. La idea es automatizarlo para no estar contando la cantidad de valores y días para el calculo del promedio, es posible hacerlo con alguna de estas funciones?

    Saludos

    AFAA

    ResponderBorrar
  12. Estimado, ¿podrías explicar el problema un poco más? ¿Cómo están organizados los datos?

    ResponderBorrar
  13. En una columna tengo fechas y en la siguiente los valores de esa fecha a una hora determinada, es por esto que tengo varios valores para nu mismo dia, la idea es obtener en las columnas siguientes valores de promedio diario, máximo, desv. est., etc. Si existe la forma de dejarlo automatizado, sería genial.

    Saludos
    AFAA

    ResponderBorrar
  14. Lo más eficiente es usar una tabla dinámica. No sólo que podés hacer todos los cálculos agrupando las fechas como te resulte conveniente, sino que también cumplís con la norma de separa entre datos, cálculos y presentación.

    ResponderBorrar
  15. Lo intentaré de esa forma... Muchas gracias por turespuesta y nuevamente, felicitaciones por el blog.

    Saludos

    AFAA

    ResponderBorrar
  16. Amigo Jorge, sigo tu blog desde hace algún tiempo y se ha vuelto para mi en una fuente de referencia obligada! Mil gracias por tu gran aporte sin ningún tipo de interés!

    En esta oportunidad comedidamente solicito de tu amable colaboración, ayudándome a resolver con una fórmula o macro debido a que tengo una situación que describo a continuación:
    Tengo un rango de datos de un proyecto con fechas consecutivas y necesito calcular el número de semanas a partir del día de la semana, es decir:

    Día Semana
    Sábado 11 de enero de 2014 1
    Domingo 12 de enero de 2014 2
    Lunes 12 de enero de 2014 2
    ...
    ...
    ...
    Sábado 18 de enero de 2014 2
    Domingo 19 de enero de 2014 3
    ...
    ...
    ...
    Sábado 25 de enero de 2014 3
    Domingo 26 de enero de 2014 4
    ... y así sucesivamente...

    (en este ejemplo coloco que la semana inicia el día domingo, pero puede empezar cualquier otro día de la semana).

    Sé que con excel se puede realizar esto, lo que no estoy seguro es si se puede con alguna fórmula o con una macro.

    Agradezco tu amable atención y espero puedas echarme una mano.

    ResponderBorrar
  17. Puedes usar la función NUM.DE.SEMANA. Fijate en esta nota.

    ResponderBorrar
  18. Lo he intentado con otro método
    =(HOY()-K5)/7
    Donde K5 es la fecha de ultima menstruación... es bastante simple, pero me lo expresa en fracción decimal y quisiera que fuera en fracción 7, para que en una celda me expresara 38.6 semanas y al cumplir un día más se convierta en 39 semanas, pero sigo sin poder cambiar de decimal a septecimal, hasta donde tengo entendido excel solo reconoce binario, decimal y hexadecimal, incluso creo en base 8, pero eso no me es útil, alguien me podría ayudar con esto?

    ResponderBorrar
  19. Hola Pascual, se puede hacer la transformación para expresar el resultado en "base 7". Si lo hacemos con fórmulas auxiliares (para simplificar la explicación, luego muestro como hacerlo con una única fórmula), suponiendo que en K5 está la fecha de 05/03/2014 y hoy es 02/12/2014:

    el la celda A1 ponemos =(HOY()-K5)/7 ==>38.85714286

    en la celda A2 ponemos =TRUNCAR(A1)+(A1-TRUNCAR(A1))*7/10 ==> 38.6

    Como puede verse, tomamos la parte decimal de la diferencia de días dividida por 7, la multiplicamos por 7 y dividimos por 10.

    Si quieres usar una única fórmula:

    =TRUNCAR((HOY()-K5)/7)+((HOY()-K5)/7-TRUNCAR((HOY()-K5)/7))*7/10

    ResponderBorrar
  20. Hola de nuevo Pascual,
    me quedé molesto con la solución que puse en le comentario anterior ya que me parece demasiado larga.
    Esta fórmula es más concisa

    =TRUNCAR((HOY()-K5)/7)+RESIDUO((HOY()-K5),7)/10

    ResponderBorrar
  21. me gustaria que me ayudes si tengo una fecha inicial ej 01-01-2015 y luego varias fechas posteriores ej. 01-04-2015, 07-07-2015 y 01-10-2015 necesito una función que me diga si anoto la primera (01-04-2015)de acuerdo a la fecha inicial diga que son 3 meses y si anoto luego la segda. (07-07-2015)me anote 6 meses con respecto a la fecha inicial y si anoto 01-10-2015 me anote 9 meses con respecto a la fecha inicial o sea que cada vez que anote una fecha me indique la cantidad de meses transcurridos, se puede como sería, favor enviar respuesta a mi correo vgg50@yahoo.es, gracias

    ResponderBorrar
  22. Usando la función SIFECHA (sigue el enlace que aparece al principio del post).

    ResponderBorrar
  23. HOLA OYE QUISIERA SABER SI TIENES ALGUNA FORMULA ESPECIFICA PARA AGRUPAR POR TRIMESTRES EN EXCEL

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.