Existe alguna formula que devuelva el numero de dias correspondientes a una misma semana de un conjunto de fechas?
Tengo una hoja de datos donde en una columna se registra la fecha en que se visita a clientes, de qué manera puedo saber cuantos dias de visitas corresponden a una misma semana. En un dia pueden existir diferentes visitas a diferentes clientes, lo que necesito saber es cuantos dias de la seman se emplearon en esas visitas.
H0la Roberto:
Según lo que leo, supongo que si se trabaja de lunes a domingo el valor que te debe arrojar es entre 0 y 7.
Si es así, supongamos que en la columna A tienes las fechas y en la celda E1 tienes el primer día de la semana por la que quieres consultar y deseas que en E2 quede la cantidad de días ocupados esa semana. Entonces deberías insertar en E2 la siguiente fórmula
=SI(O(A:A=E1);1;0)+SI(O(A:A=(E1+1));1;0)+SI(O(A:A=(E1+2));1;0)+SI(O(A:A=(E1+3));1;0)+SI(O(A:A=(E1+4));1;0)+SI(O(A:A=(E1+5));1;0)+SI(O(A:A=(E1+6));1;0)
La fórmula debiera verse así
Fíjate que al inicio y fin de la fórmula aparecen llaves { y }. Para que esas llaves aparezcan, en lugar de presionar Enter para aceptar la fórmula, presiona Ctrl+Mayús+Enter
Cada SI te arrojará 1 si el día consultado está en la tabla, fíjate que el primer SI comienza consultando por E1(el primer día se la semana) y a los siguientes SI se va incrementando el valor de E1 para ir avanzando en los días.
No lo entendí bien, según el ejemplo que me das, que debería tener en E1?, Haber si te lo puedo explicar mejor. Imagina que en la columna A tengo las fechas de cuándo se visitaron ciertos clientes, en la columna B tengo en Linero de semana a qué corresponde esa fecha, yo quiero que en la columna C me cuente el número de días que trabajaron en las visitas que realizaron para cada semana. Espero esté más claro y si no te puedo enviar el archivo para mayor claridad, saludos y muchas gracias .
Lo lamento Roberto, en realidad no me queda más claro el asunto.
En E1 debiera ir el primer día de la semana por el que quieres consultar, en E2, que es donde agregas la fórmula, aparece la cantidad de días que se trabajaron. Pero creo que no es exactamente lo que deseas.
Quizá si subes una imagen, se podría entender mejor tu requerimiento.
S@lu2
Hola Isaac, muchas gracias intente mandarte una imagen pero no se como funciona espero que si la puedas ver.,
H0la Roberto, disculpa la demora, pero le había estado dando vueltas a este asunto y finalmente esto es lo que resultó.
Puedes pegar esta función en un módulo
Function fcnContarDST(r As Range, d As Range) As Integer
anio = Year(d.Value)
dia_sem = Weekday(d.Value, vbMonday)
ini_sem = DateAdd("d", 1 - dia_sem, d.Value)
With WorksheetFunction
fcnContarDST = IIf(.CountIf(r, ini_sem) > 0, 1, 0) + IIf(.CountIf(r, ini_sem + 1) > 0, 1, 0) + _
IIf(.CountIf(r, ini_sem + 2) > 0, 1, 0) + IIf(.CountIf(r, ini_sem + 3) > 0, 1, 0) + _
IIf(.CountIf(r, ini_sem + 4) > 0, 1, 0) + IIf(.CountIf(r, ini_sem + 5) > 0, 1, 0) + _
IIf(.CountIf(r, ini_sem + 6) > 0, 1, 0)
End With
End FunctionLuego en las celdas de la columna D, puedes agregar esta función (suponiendo que las fechas pueden ocupar hasta 1000 filas)
El ejemplo va para la celda D3, luego arrastras la función.
D3=fcnContarDST(B$3:B$1000;C3)
Esto te actualizará el valor de forma automática en cada celda, pero tiene un costo alto en cuanto a eficiencia (ya con 1000 filas se demoraría bastante).
Si no es vital que se refresque el dato cada vez que haces un cambio, podría agregar un botón que actualice todas las celdas de la columna D cuando lo solicites. La macro asociada al botón sería la siguiente
Sub subContarDST()
Dim rango As Range, c As Range
'Rango de fechas'
Set rango = Range("B3:B" & Range("B" & Rows.Count).End(xlUp).Row)
'Calcular el número de días por cada celda del rango'
For Each c In rango
Range("D" & c.Row).Value = fcnContarDST(rango, c)
Next
End SubToma en consideración que esta macro depende la función anterior, así que ambas son necesarias para que el botón funcione.
Sin son muchos datos, igual toma unos segundos, pero no se me ocurre cómo hacerlo más eficiente.
Me comentas si te sirve.
Muchas gracias Isaac, no me funciono porque tengo más de 1000 registros, y efectivamente lo hace muy lento, pero agradezco tu interés, Saludos y gracias.
H0la Roberto:
Le hice unas modificaciones
Sub subContarDST2(anio As Integer)
Dim r As Range
Dim diasTrab As Integer
't = Timer'
Application.ScreenUpdating = False
strColIni = "B" 'Columna donde está la fecha'
strColRes = "D" 'Columna donde se agrega el numero de dias trabajados'
'Application.ScreenUpdating = False
fil = Range("B" & Rows.Count).End(xlUp).Row
Set r = Range("B1:D" & fil)
Set r2 = Range("D2:D" & fil)
dia_sem = Weekday("1/1/" & anio, vbMonday)
ini_sem = DateAdd("d", 1 - dia_sem, "1/1/" & anio)
With WorksheetFunction
For i = 1 To 53
r.AutoFilter Field:=1, Criteria1:= _
">=" & Format(ini_sem, "mm-dd-yyyy"), Operator:=xlAnd, _
Criteria2:="<=" & Format(DateAdd("d", 6, ini_sem), "mm-dd-yyyy")
If r.SpecialCells(xlCellTypeVisible).Count > r.Columns.Count Then
diasTrab = IIf(.CountIf(r, ini_sem) > 0, 1, 0) + IIf(.CountIf(r, ini_sem + 1) > 0, 1, 0) + _
IIf(.CountIf(r, ini_sem + 2) > 0, 1, 0) + IIf(.CountIf(r, ini_sem + 3) > 0, 1, 0) + _
IIf(.CountIf(r, ini_sem + 4) > 0, 1, 0) + IIf(.CountIf(r, ini_sem + 5) > 0, 1, 0) + _
IIf(.CountIf(r, ini_sem + 6) > 0, 1, 0)
r2.SpecialCells(xlCellTypeVisible) = diasTrab
End If
ini_sem = DateAdd("d", 7, ini_sem)
Next
End With
r.AutoFilter
Set r = Nothing
Set r2 = Nothing
Application.ScreenUpdating = True
'MsgBox Timer - t'
End SubLa forma de utilizarla es, dentro del código de un evento llamas al procedimiento de la siguiente forma.
SubContarDST2 2019
Donde 2019 es el año que deseas recorrer.
Esta macro se demora unos 12 segundos en 100.000 datos (al menos en mi computador).
