Harold está tratando de idear una fórmula que le diga los últimos cinco años en los que una fecha particular (digamos, el 10 de mayo) ocurrió en un día particular de la semana (digamos, el jueves).

Hay varias formas de determinar esta información. Una forma sencilla es, en una nueva hoja de trabajo, ingresar la fecha que desea probar (como 5/10/18) en la celda A1. Justo debajo de eso, en la celda A2, ingrese una fecha un año antes: 5/10/17. Seleccione estas dos celdas y arrastre el controlador de relleno hacia abajo para la cantidad de celdas que desee, y terminará con una columna que contiene la fecha del 10 de mayo en los años descendentes.

A continuación, en la celda B1 ingrese la siguiente fórmula:

=WEEKDAY(A1)

Copie esta fórmula para tantas filas como fechas tenga en la columna A.

Esto devuelve un valor, del 1 al 7, que representa el día de la semana para las fechas en la columna A. Dado que 1 = domingo, 2 = lunes, etc., eso significa que cualquier valor de 5 representaría un jueves. Por lo tanto, puede colocar esta fórmula en la celda C1 y copiarla:

=IF(B1=5,YEAR(A1),"")

Esto «indica» los años en los que la fecha en la columna A es un jueves.

Si lo prefiere, puede simplemente renunciar a agregar la columna C y, en su lugar, usar el filtrado para mostrar solo aquellas fechas donde el valor en la columna B es 5 (jueves).

También puede utilizar una fórmula de matriz para encontrar la información que desee.

Primero, seleccione cinco celdas en cualquier columna que desee. Luego, escriba lo siguiente en la barra de fórmulas:

=LARGE((WEEKDAY(DATE(ROW(1918:2018),5,10))=5)*ROW(1918:2018),ROW(1:5))

Ingrese la fórmula presionando Ctrl + Shift + Enter. El resultado es que las cinco celdas contendrán los últimos cinco años en los que el 10 de mayo fue jueves. Puede hacer que la fórmula sea más versátil reemplazando los elementos clave con rangos con nombre, de esta manera:

=LARGE((WEEKDAY(DATE(ROW(1918:2018),MO,DA))=DW)*ROW(1918:2018),ROW(1:5))

Ahora todo lo que necesita hacer es crear los rangos nombrados MO (mes), DA (día) y DW (día de la semana). A medida que cambia los valores en estos rangos con nombre, la fórmula de matriz actualiza las cinco celdas para reflejar los años deseados.

Una tercera forma de encontrar la información es crear una macro que solicite una fecha de inicio y un día de la semana. La macro puede retroceder un año a la vez hasta que tome cinco años que cumplan con los criterios.

Sub CalcDates()

Dim sTemp As String     Dim dBegin As Date     Dim dWork As Date     Dim J As Integer     Dim iDoW As Integer     Dim iYears(5) As Integer

sTemp = InputBox("Beginning Date?")

dBegin = CDate(sTemp)

If dBegin > 0 Then         sTemp = InputBox("Day of Week?")

sTemp = LCase(Trim(sTemp))

iDoW = 0         For J = 1 To 7             If sTemp = LCase(WeekdayName(J)) Then iDoW = J         Next J         If iDoW > 0 Then             dWork = dBegin             J = 0             While J < 5                 If Weekday(dWork) = iDoW Then                     J = J + 1                     iYears(J) = Year(dWork)

End If                 dWork = DateAdd("yyyy", -1, dWork)

Wend

sTemp = "These are the previous five years in which "

sTemp = sTemp & MonthName(Month(dBegin)) & " " & Day(dBegin)

sTemp = sTemp & " falls on a " & WeekdayName(iDoW) & ":"

sTemp = sTemp & vbCrLf & vbCrLf             For J = 5 To 1 Step -1                 sTemp = sTemp & iYears(J) & vbCrLf             Next J             MsgBox sTemp         End If     End If End Sub

Cuando la macro encuentra los cinco años, los muestra en un cuadro de mensaje.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13529) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.