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:
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
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
=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:
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
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
Excelente!!! Estaba tratando de realizar para unos reportes de la gestión semanal de la empresa en la que trabajo, muy buen foro...
ResponderEliminarGracias!!!
Eliminargenial gracias justo lo que necesitaba!
ResponderEliminarhola en el caso del calendario de diciembre tiene 6 semanas, como hago para que en el calculo me redondee a 4 semanas. gracias
ResponderEliminarHola Osval,
Eliminary 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
Facil introduce un si condicional si la semana es mayor a 4 coloque 4 asi de arbitrario
EliminarEste comentario ha sido eliminado por el autor.
EliminarHola 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))))))))))))
ResponderEliminarHola María,
Eliminarse 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
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
ResponderEliminarPor supuesto Edwin,
Eliminarun placer poder ayudarte
Un cordial saludo
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?
ResponderEliminarHola,
Eliminarlas 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
Genial Ismael muchas gracias como siempre.
ResponderEliminarhabría manera de seleccionar en el calendario con un recuadro rojo el dia =hoy() ?
;-)
EliminarQuizá 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
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.
Eliminardime como hago para pasártelo y a ver si te gusta :)
gracias
al código que colgaste para crear el calendario le sume esto
ResponderEliminarDim 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
Genial, la forma de como obtuviste el numera de las semanas del mes. tu formula está de lujo.
ResponderEliminar;-)
EliminarSlds
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.
ResponderEliminarpor si a alguien le sirviese, me respondo a mi mismo:
Eliminar= 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
Gracias!!!!
ResponderEliminar