Harold sta cercando di escogitare una formula che gli dirà gli ultimi cinque anni in cui una data particolare (diciamo il 10 maggio) si è verificata in un particolare giorno della settimana (diciamo, giovedì).

Esistono diversi modi per determinare queste informazioni. Un modo semplice è, in un nuovo foglio di lavoro, inserire la data che si desidera testare (ad esempio 5/10/18) nella cella A1. Subito sotto, nella cella A2, inserisci una data un anno prima: 5/10/17. Seleziona queste due celle e trascina il quadratino di riempimento verso il basso per tutte le celle desiderate e ti ritroverai con una colonna contenente la data del 10 maggio negli anni discendenti.

Successivamente, nella cella B1 inserisci la seguente formula:

=WEEKDAY(A1)

Copia questa formula per tante righe quante sono le date nella colonna A.

Restituisce un valore, compreso tra 1 e 7, che rappresenta il giorno della settimana per le date nella colonna A. Poiché 1 = domenica, 2 = lunedì e così via, significa che qualsiasi valore di 5 rappresenterebbe un giovedì. Quindi, puoi inserire questa formula nella cella C1 e copiarla:

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

Questo “richiama” gli anni in cui la data nella colonna A è un giovedì.

Potresti, se preferisci, semplicemente rinunciare all’aggiunta della colonna C e, invece, utilizzare il filtro per mostrare solo quelle date in cui il valore nella colonna B è 5 (giovedì).

È inoltre possibile utilizzare una formula di matrice per trovare le informazioni desiderate.

Innanzitutto, seleziona cinque celle in qualsiasi colonna che desideri. Quindi, digita quanto segue nella barra della formula:

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

Immettere la formula premendo Ctrl + Maiusc + Invio. Il risultato è che le cinque celle conterranno gli ultimi cinque anni in cui il 10 maggio era un giovedì. Potresti rendere la formula più versatile sostituendo gli elementi chiave con intervalli denominati, in questo modo:

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

Ora tutto ciò che devi fare è creare gli intervalli denominati MO (mese), DA (giorno) e DW (giorno della settimana). Man mano che si modificano i valori in questi intervalli denominati, la formula di matrice aggiorna le cinque celle per riflettere gli anni desiderati.

Un terzo modo per trovare le informazioni è creare una macro che richieda una data di inizio e un giorno della settimana. La macro può quindi tornare indietro di un anno alla volta fino a ottenere cinque anni che soddisfano i criteri.

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

Quando la macro trova i cinque anni, li visualizza in una finestra di messaggio.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (13529) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.