Formula generica
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Sommario
Per calcolare le ore di lavoro tra due date con una pianificazione personalizzata, è possibile utilizzare una formula basata sulle funzioni WEEKDAY e SUMPRODUCT, con l'aiuto di ROW, INDIRECT e MID. Nell'esempio mostrato, la formula in F8 è:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Che restituisce 36 ore, in base a una pianificazione personalizzata in cui vengono lavorate 8 ore dal lunedì al venerdì, 4 ore il sabato e lunedì 3 settembre è un giorno festivo. I giorni festivi vengono forniti nella gamma denominata G6: G8. Il programma di lavoro viene inserito come stringa di testo nella colonna D e può essere modificato a piacere.
Nota: questa è una formula di matrice che deve essere inserita con Control + Maiusc + Invio. Se hai una giornata lavorativa standard di 8 ore, questa formula è più semplice.
Spiegazione
Fondamentalmente, questa formula utilizza la funzione WEEKDAY per calcolare il giorno della settimana (cioè lunedì, martedì, ecc.) Per ogni giorno compreso tra le due date specificate. GIORNO.SETTIMANA restituisce un numero compreso tra 1 e 7. Con le impostazioni predefinite, domenica = 1 e sabato = 7.
Il trucco per questa formula sta nell'assemblare una serie di date che puoi inserire nella funzione WEEKDAY. Questo viene fatto con ROW con INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpreta le date concatenate come numeri di riga e restituisce un array come questo:
(43346;43347;43348;43349;43350;43351;43352)
Ogni numero nella matrice rappresenta una data. La funzione WEEKDAY valuta quindi l'array e restituisce un array di valori della settimana:
(2;3;4;5;6;7;1)
Questi numeri corrispondono al giorno della settimana di ciascuna data. Sono forniti alla funzione MID come argomento del numero iniziale, insieme al valore in D6, "0888884" per il testo:
MID("0888884",(2;3;4;5;6;7;1),1)
Poiché stiamo dando a MID un array di numeri iniziali, restituisce un array di risultati come questo:
("8";"8";"8";"8";"8";"4";"0")
Questi valori corrispondono alle ore lavorate ogni giorno dalla data di inizio alla data di fine. Notare che i valori in questa matrice sono testo, non numeri. Per convertire in numeri effettivi, moltiplichiamo per un secondo array creato per gestire le festività, come spiegato di seguito. L'operazione di matematica forza il testo a valori numerici.
Vacanze
Per gestire le festività, utilizziamo ISNA, MATCH e l'intervallo denominato "vacanze" in questo modo:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Questa espressione utilizza MATCH per individuare le date che si trovano nell'intervallo di festività denominato utilizzando lo stesso array di date generato sopra con INDIRETTO e RIGA. CONFRONTA restituisce un numero quando vengono trovate le festività e l'errore # N / D in caso contrario. La funzione ISNA "capovolge" i risultati in modo che TRUE rappresenta le festività e FALSE rappresenta le non festività. ISNA restituisce un array o risultati come questo:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Infine, entrambi gli array vengono moltiplicati l'uno per l'altro all'interno di SUMPRODUCT. L'operazione matematica forza TRUE e FALSE a 1 e zero, e i valori di testo nel primo array a valori numerici (come spiegato sopra), quindi alla fine abbiamo:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Dopo la moltiplicazione, abbiamo un singolo array all'interno di SUMPRODUCT contenente tutte le ore lavorative nell'intervallo di date:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT quindi somma tutti gli elementi nell'array e restituisce un risultato di 36.
