Come verificare se una data è un giorno festivo e contare i giorni tranne i giorni festivi in Excel?
Ad esempio, hai un elenco di date e vuoi controllare se nell'elenco sono presenti giorni festivi, come puoi farlo rapidamente? In questo articolo ti mostrerò i modi per calcolare tutte le date dei giorni festivi americani entro determinati anni, e quindi verificare facilmente se una data è un giorno festivo americano in Excel.
- Parte 1: calcola i giorni festivi in determinati anni in Excel
- Parte 2: controlla se una data è un giorno festivo in Excel
- Parte 3: conta i giorni tra due giorni tranne i fine settimana e i giorni festivi in Excel
Parte 1: calcola i giorni festivi con un determinato anno in Excel
Prima di verificare se una data è un giorno festivo, è necessario elencare tutte le festività di determinati anni in Excel. Pertanto preparare una tabella come mostrato nella seguente schermata renderà il tuo lavoro più facile.
Esistono tre tipi di giorni festivi americani:
(1) Il primo tipo è un giorno festivo in una data fissa, ad esempio il capodanno è il 1 ° gennaio. possiamo facilmente calcolare il capodanno con la formula = DATA (Anno indicato, 1,1);
(2) Il secondo tipo è un giorno festivo in un giorno feriale fisso, come il President Day. Possiamo facilmente calcolare il giorno del presidente con la formula = DATA (Anno dato, 1,1) + 14 + SCEGLI (GIORNO DELLA SETTIMANA (DATA (Anno dato, 1,1)), 1,0,6,5,4,3,2);
(3) E l'ultimo tipo è il Memorial Day, possiamo facilmente calcolare il Memorial Day con la formula = DATA (Anno indicato, 6,1) -WEEKDAY (DATA (Anno indicato, 6,6)).
Qui elenco le formule per calcolare tutti i giorni festivi nella tabella seguente. Basta inserire le formule nella cella appropriata e premere entrare chiave uno per uno.
Vacanza | Cella | Formule |
Capodanno | C2 | = DATA (C1,1,1) |
Martin Luther King Jr. Day | C3 | = DATA (C1,1,1) + 14 + SCEGLI (WEEKDAY (DATE (C1,1,1)), 1,0,6,5,4,3,2) |
Giornata del Presidente | C4 | = DATA (C1,2,1) + 14 + SCEGLI (WEEKDAY (DATE (C1,2,1)), 1,0,6,5,4,3,2) |
Memorial Day | C5 | = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6)) |
Independence Day | C6 | = DATA (C1,7,4) |
Labor Day | C7 | = DATA (C1,9,1) + SCEGLI (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4,3,2) |
Columbus Day | C8 | = DATA (C1,10,1) + 7 + SCEGLI (WEEKDAY (DATE (C1,10,1)), 1,0,6,5,4,3,2) |
Veterans Day | C9 | = DATA (C1,11,11) |
Giorno del Ringraziamento | C10 | = DATA (C1,11,1) + 21 + SCEGLI (WEEKDAY (DATE (C1,11,1)), 4,3,2,1,0,6,5) |
Il giorno di Natale | C11 | = DATA (C1,12,25) |
Nota: Nelle formule nella tabella sopra, C1 è la cella di riferimento che individua l'anno specificato. Nel nostro esempio, significa Anno 2015 e puoi modificarlo in base alle tue esigenze.
Con queste formule puoi facilmente calcolare le date dei giorni festivi con determinati anni. Vedi la schermata qui sotto:
Salva un intervallo come voce di glossario (formati di celle e formule rimanenti) per riutilizzarli in futuro
Deve essere molto noioso fare riferimento alle celle e applicare le formule per il calcolo di ogni festività. Kutools per Excel fornisce una soluzione alternativa carina Testo automatico utilità per salvare l'intervallo come voce di glossario, che può rimanere i formati delle celle e le formule nell'intervallo. E poi riutilizzerai questa gamma con un solo clic. Il lavoro diventa facile con un solo clic per inserire questa tabella e cambiare l'anno in questa tabella!
Kutools for Excel - Potenzia Excel con oltre 300 strumenti essenziali. Goditi una prova GRATUITA di 30 giorni con tutte le funzionalità senza carta di credito richiesta! Get It Now
Parte 2: controlla se una data è un giorno festivo in Excel
Dopo aver elencato tutte le date dei giorni festivi di anni specifici, possiamo facilmente verificare se una data è festiva o meno con le formule in Excel. Supponendo che tu abbia un elenco di date come mostrato nella schermata seguente, introdurrò i modi per farlo facilmente.
Seleziona una cella vuota oltre all'elenco delle date, dice Cell B18, inserisci la formula = SE (CONTA.SE ($ C $ 2: $ D $ 11, A18), "Festività", "No") in esso, quindi trascina la maniglia di riempimento nell'intervallo che ti serve. Vedi screenshot sopra:
Note:
(1) Nella formula = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No"), $ C $ 2: $ D $ 11 è l'intervallo di giorni festivi in anni specifici e A18 è la cella con la data che vuoi controllare se è un giorno festivo e puoi modificarli in base alle tue esigenze. E questa formula restituirà "Festività" se la data specifica è un giorno festivo e restituirà "No" se non lo è.
(2) Puoi anche applicare questa formula di matrice = SE (OR ($ C $ 2: $ D $ 11 = A18), "Festività", "NO") per verificare se la data corrispondente è festiva o meno.
Parte 3: conta i giorni tra due giorni tranne i fine settimana e i giorni festivi in Excel
Nella Parte 1 abbiamo elencato tutte le festività in un determinato anno e ora questo metodo ti guiderà a contare il numero di giorni tranne tutti i fine settimana e le festività in un intervallo di date.
Seleziona una cella vuota per restituire il numero di giorni e inserisci la formula = NETWORKDAYS (E1, E2, B2: B10) e premere il pulsante entrare chiave.
Note:: Nelle celle sopra, E1 è la data di inizio dell'intervallo di date specificato, E2 è la data di fine e B2: B10 è l'elenco delle festività che calcoliamo nella Parte 1.
Ora otterrai il numero di giorni tranne i fine settimana e i giorni festivi nell'intervallo di date specificato.
Copia le formule esattamente / staticamente senza modificare i riferimenti di cella in Excel
Kutools for Excel Copia esatta può aiutarti a copiare facilmente più formule esattamente senza modificare i riferimenti di cella in Excel, impedendo l'aggiornamento automatico dei riferimenti di cella relativi.
Kutools for Excel - Potenzia Excel con oltre 300 strumenti essenziali. Goditi una prova GRATUITA di 30 giorni con tutte le funzionalità senza carta di credito richiesta! Get It Now
I migliori strumenti per la produttività in ufficio
Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Fai clic qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!