lunes, 15 de abril de 2013

Obtener Número de semana dentro de un mes para una fecha dada. La función NUM.SEMANA.

Todos conocemos la función NUM.SEMANA que nos devuelve el número de semana para un día cualquiera del año, un valor entre 1 y 52 (número de semanas total del año). Es una función bastante sencilla, ya que sólo requiere dos argumentos:
=NUM.DE.SEMANA(núm_de_serie;[tipo_de_devolución])
siendo el segundo argumento con el que indicamos en qué día empieza la semana (lunes, martes, etc).


La misión de hoy va algo más lejos, ya que no queremos concoer el número de semana respecto al total del año, si no el número de semana correspondiente dentro de un mes... es decir, cada mes, tendrá un valor de 1,2,3,4,5 o incluso 6 semanas.
Para disponer de un ejemplo, y poder comprobar nuestro cálculo, recuperaremos una macro explicada en este blog tiempo atrás (ver como construir un calendario con macros).
Si bien he realizado una mínima corrección para comenzar las semanas de los meses en Lunes (contante vbMonday). Y esto es lo que conseguimos:

Obtener Número de semana dentro de un mes para una fecha dada. La función NUM.SEMANA.



Por verificar lo que buscamos. Si nos fijamos en el mes de diciembre de 2013, vemos que el mes comienza el 01/12/2013, Domingo; esta sería la semana 1 del mes, y el 31/12/2013, Martes correspondería a la semana 6 del mes.

Con este aspecto claro, vamos a ver nuestra fórmula, calculada sobre cualquier día entre el 01/01/2013 y 31/12/2013, logrado con una función =ALEATORIO.ENTRE("01/01/2013";"31/12/2013"), que hemos colocado en al celda AB3. Nuestra fórmula en AC3 es:
=NUM.DE.SEMANA(AB3;2)-NUM.DE.SEMANA(FIN.MES(AB3;-1)+1;2)+1

Obtener Número de semana dentro de un mes para una fecha dada. La función NUM.SEMANA.
haz clic en la imagen


Podemos actualizar la hoja cuantas veces queramos y comprobar como efectivamente la fórmula es correcta...

Pasamos a explicarla en detalle. Comenzaremos por la parte más profunda:
FIN.MES(AB3;-1)+1
con esta función obtenemos, primero, el último día del mes anterior al de la fecha de estudio, al que sumando +1 posteriormente, obviamente logramos el primer día del mes de la fecha a analizar.

Con la fecha primero de mes anidada en la función NUM.SEMANA logramos entonces el número de semana absoluto (de 1 a 52) en que comienza el mes al que corresponde la fecha a estudiar.
NUM.DE.SEMANA(FIN.MES(AB3;-1)+1;2)

Si este valor lo restamos de la semana absoluta de la fecha de estudio:
NUM.DE.SEMANA(AB3;2)
por diferencias tendríamos el valor dentro del mes.. sólo hace falta un ajuste a la fórmula, sumándo +1 a esa diferencia tendremos el valor exacto.
=NUM.DE.SEMANA(AB3;2)-NUM.DE.SEMANA(FIN.MES(AB3;-1)+1;2)+1

22 comentarios:

  1. Excelente!!! Estaba tratando de realizar para unos reportes de la gestión semanal de la empresa en la que trabajo, muy buen foro...

    ResponderEliminar
  2. genial gracias justo lo que necesitaba!

    ResponderEliminar
  3. hola en el caso del calendario de diciembre tiene 6 semanas, como hago para que en el calculo me redondee a 4 semanas. gracias

    ResponderEliminar
    Respuestas
    1. Hola Osval,
      y cómo sería ese redondeo que comentas??...
      en el caso del año del ejemplo (2013).
      qué días serían la semana 1, y la 2, y la 3 y la 4??

      El resto de los meses del año también se debe redondear a meses de 4 semanas??

      Ten en cuenta que los años y los meses son como son, con 4,5 o 6 semanas... cualquier alteración a esto supone una nueva definición de qué entendemos por semanas/meses

      Saludos

      Eliminar
    2. Facil introduce un si condicional si la semana es mayor a 4 coloque 4 asi de arbitrario

      Eliminar
    3. Este comentario ha sido eliminado por el autor.

      Eliminar
  4. Hola estoy buscando hacer una validación de datos condicional respecto a las semanas que contiene un mes de otra casilla y un año de otra casilla para que en la lista me queden por ejemplo si es marzo de 2016 :1. del 1 al 6 2. del 7 al 14 y asi... Como es posible lograr esto si no me deja utilizar esta formula pues supongo que es muy larga =SI($C$6='Citas asignadas'!$M$2,'Citas asignadas'!$N$2:$R$2,SI($C$6='Citas asignadas'!$M$3,'Citas asignadas'!$N$3:$R$3,SI($C$6='Citas asignadas'!$M$4,'Citas asignadas'!$N$4:$R$4,SI(Calendario!$C$6='Citas asignadas'!$M$5,'Citas asignadas'!$N$5:$R$5,SI($C$6='Citas asignadas'!$M$6,'Citas asignadas'!$N$6:$S$6,SI($C$6='Citas asignadas'!$M$7,'Citas asignadas'!$N$7:$R$7,SI($C$6='Citas asignadas'!$M$8,'Citas asignadas'!$N$8:$R$8,SI($C$6='Citas asignadas'!$M$9,'Citas asignadas'!$N$9:$R$9,SI($C$6='Citas asignadas'!$M$10,'Citas asignadas'!$N$10:$R$10,SI($C$6='Citas asignadas'!$M$11,'Citas asignadas'!$N$11:$S$11,SI($C$6='Citas asignadas'!$M$12,'Citas asignadas'!$N$12:$R$12,SI($C$6='Citas asignadas'!$M$13,'Citas asignadas'!$N$13:$R$13))))))))))))

    ResponderEliminar
    Respuestas
    1. Hola María,
      se trataría de trabajar con nombres definidos asociadas a esos 12 rangos, para luego recuperar y vincular las validaciones con la función INDIRECTO
      Echa un vistazo a este ejemplo:
      http://excelforo.blogspot.com.es/2010/04/validacion-de-celdas-anidadas-y.html
      Un saludo

      Eliminar
  5. Ismael me das permiso para poner tu explicación de la formula dentro del material didactico de mi curso por supuesto identificante debidamente como el autor e identificando el blog? saludos

    ResponderEliminar
    Respuestas
    1. Por supuesto Edwin,
      un placer poder ayudarte

      Un cordial saludo

      Eliminar
  6. Ismael gracias por el aporte pero tengo una duda sobre la formula, en que te basas para considerar cual semana es donde empieza el mes? por ejemplo la normativa ISO considera que la primer semana del año es aquella donde el 1 de enero cae de lunes a jueves, caso contrario la siguiente semana es la 1ª, ahora en tu ejemplo de agosto (imagen) tu formula calcula como semana 2 del mes, pero si nos fijamos en el calendario completo la semana del 1 de agosto es la semana 31 del año la cual es la misma del 31 de julio, en resumen mi pregunta es, ¿la ultima semana de julio es la misma que la primera de agosto? es correcto eso?

    ResponderEliminar
    Respuestas
    1. Hola,
      las semanas completas van de lunes a domingo (así lo especificamos en la función NUM.De.SEMANA), por tanto, es la misma semana del año aunque esté partida en dos meses...
      que no es incompatible con que, la parte de agosto, a la vez sea la semana 1 del mes en curso
      ;-)

      Saludos

      Eliminar
  7. Genial Ismael muchas gracias como siempre.

    habría manera de seleccionar en el calendario con un recuadro rojo el dia =hoy() ?

    ResponderEliminar
    Respuestas
    1. ;-)

      Quizá con formato condicional... pero sería bastante elaborado, ya que los días dispuestos en el calendario son solo números, no fechas... por lo que habría que replicar para cada número, su equivalente en 'fecha'...

      Saludos

      Eliminar
    2. Conseguí hacer una chapucilla, pero no se como pegar el código ya que me limita el numero de caracteres en el cuadro de responder.

      dime como hago para pasártelo y a ver si te gusta :)
      gracias

      Eliminar
  8. al código que colgaste para crear el calendario le sume esto

    Dim FHOY As Date
    Dim DHOY As Integer
    'Pone Año en A1, y consulta de fecha actual y calculo de cualquier fecha.
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=YEAR(NOW())"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "Dia del año"
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "Ahora"
    Range("AB3").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Range("AC3").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("AB4").Select
    ActiveCell.FormulaR1C1 = "=WEEKNUM(R[-1]C,2)-WEEKNUM(EOMONTH(R[-1]C,-1)+1,2)+1"
    Range("AC4").Select
    ActiveCell.FormulaR1C1 = "Semana Dentro del Mes actual"
    Range("AB5").Select
    ActiveCell.FormulaR1C1 = "=WEEKNUM(R[-2]C,2)"
    Range("AC5").Select
    ActiveCell.FormulaR1C1 = "Semana del año"
    Range("AB6").Select
    ActiveCell.FormulaR1C1 = "=EOMONTH(R[-3]C,-1)+1"
    Selection.NumberFormat = "m/d/yyyy"
    FHOY = Range("AB6").Value
    Range("AC6").Select
    ActiveCell.FormulaR1C1 = "Primer dia del mes actual"
    Range("AB7").Select
    ActiveCell.FormulaR1C1 = "=WEEKNUM(EOMONTH(R[-4]C,-1)+1,2)"
    Range("AC7").Select
    ActiveCell.FormulaR1C1 = "Semana en que empieza el mes actual"
    Range("AB8").Select
    ActiveCell.FormulaR1C1 = "=DAY(R[-5]C[1])"
    DHOY = Range("AB8").Value
    Range("AC8").Select
    ActiveCell.FormulaR1C1 = "Dia de hoy"

    Cells.Find(What:=FHOY, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(7, 6)).Select
    Selection.Find(What:=DHOY, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With

    'Ajusta columnas y quita lineas
    Columns("B:AD").Select
    Columns("B:AD").EntireColumn.AutoFit
    Range("A1").Select
    ActiveWindow.DisplayGridlines = False

    ResponderEliminar
  9. Genial, la forma de como obtuviste el numera de las semanas del mes. tu formula está de lujo.

    ResponderEliminar
  10. Hola a todos, soy Martin. Me gustaria saber si es posible modificar la formula para considerar semana solo de lunes a Viernes. Por ejemplo obtener un 3 para el dia 21-12-2018 que aun siendo la cuarta semana, es el tercer viernes de mes. Podria hacerse con con DIAS.LAB? un saludo mi mas cordial agradecimiento.

    ResponderEliminar
    Respuestas
    1. por si a alguien le sirviese, me respondo a mi mismo:

      = SI(DIASEM(A6;2)>5;””;SI(DIASEM(FECHA(AÑO(A6);MES(A6);1))=7;NUM.DE.SEMANA(A6)-NUM.DE.SEMANA(FECHA(AÑO(A6);MES(A6); 1));NUM.DE.SEMANA(A6)-NUM.DE.SEMANA(FECHA(AÑO(A6);MES(A6);1)) + 1))


      saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.