Salt la conținutul principal

Cum să verificați dacă o dată este sărbătoare publică și să numărați zile, cu excepția sărbătorilor în Excel?

De exemplu, aveți o listă de date și doriți să verificați dacă există liste de sărbători legale, cum ați putea face acest lucru rapid? În acest articol vă voi arăta modalitățile de a calcula toate datele sărbătorilor publice americane în anii dați și apoi verificați dacă o dată este o sărbătoare publică americană în Excel cu ușurință.


Partea 1: Calculați sărbătorile legale cu anul dat în Excel

Înainte de a verifica dacă o dată este o sărbătoare publică, trebuie să enumerați toate sărbătorile în anii dați în Excel. Prin urmare, pregătirea unui tabel după cum este prezentată următoarea captură de ecran vă va ușura munca.

doc verifică dacă sărbătoarea publică

Există trei tipuri de sărbători legale americane:

(1) Primul tip este o sărbătoare publică la o dată fixă, cum ar fi ziua de Anul Nou este pe 1 ianuarie. putem calcula cu ușurință ziua de Anul Nou cu formula = DATA (Anul dat, 1,1);

(2) Al doilea tip este o sărbătoare publică într-o zi fixă ​​a săptămânii, cum ar fi Ziua Președintelui. Putem calcula cu ușurință ziua președintelui cu formula = DATA (Anul dat, 1,1) + 14 + ALEGE (SĂPTĂMÂNĂ (DATA (Anul dat, 1,1)), 1,0,6,5,4,3,2);

(3) Și ultimul tip este Ziua Memorialului, putem calcula cu ușurință Ziua Memorialului cu formula = DATA (Anul dat, 6,1) -ZIUA (DATA (Anul dat, 6,6)).

Aici listez formule pentru calcularea tuturor sărbătorilor legale în tabelul următor. Doar introduceți formulele în celula corespunzătoare și apăsați Intrați cheie una câte una.

Vacanță Celulă Formule
Ziua de Anul Nou C2 = DATA (C1,1,1)
Ziua lui Martin Luther King Jr. C3 = DATE (C1,1,1) + 14 + ALEGE (SĂPTĂMÂNĂ (DATE (C1,1,1)), 1,0,6,5,4,3,2)
Ziua Președintelui C4 = DATE (C1,2,1) + 14 + ALEGE (SĂPTĂMÂNĂ (DATE (C1,2,1)), 1,0,6,5,4,3,2)
Memorial Day C5 = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6))
Ziua Independenței C6 = DATA (C1,7,4)
Ziua muncii C7 = DATA (C1,9,1) + ALEGE (SĂPTĂMÂNĂ (DATA (C1,9,1)), 1,0,6,5,4,3,2)
Ziua lui Columb C8 = DATE (C1,10,1) + 7 + ALEGE (SĂPTĂMÂNĂ (DATE (C1,10,1)), 1,0,6,5,4,3,2)
Ziua Veteranilor C9 = DATA (C1,11,11)
Sărbătoare naţională în S.U.A. C10 = DATE (C1,11,1) + 21 + ALEGE (SĂPTĂMÂNĂ (DATE (C1,11,1)), 4,3,2,1,0,6,5)
ziua de Craciun C11 = DATA (C1,12,25)

Notă: În formulele din tabelul de mai sus, C1 este celula de referință care localizează anul dat. În exemplul nostru, înseamnă Anul 2015 și îl puteți schimba în funcție de nevoile dvs.

Cu aceste formule, puteți calcula cu ușurință datele sărbătorilor legale cu anii dați. Vedeți captura de ecran de mai jos:

doc verifică dacă sărbătoarea publică

Salvați un interval ca intrare AutoText (formate și formule de celule rămase) pentru reutilizare în viitor

Trebuie să fie foarte obositor să se trimită celulele și să se aplice formule pentru calcularea fiecărei sărbători. Kutools pentru Excel oferă o soluție drăguță Text automat utilitar pentru a salva intervalul ca intrare AutoText, care poate rămâne formatele și formulele de celule din interval. Și apoi veți reutiliza acest interval cu un singur clic. Munca devine ușoară printr-un singur clic pentru a insera acest tabel și a schimba anul în acest tabel!


ad auto American Holidays 1

Kutools pentru Excel - Supercharge Excel cu peste 300 de instrumente esențiale. Bucurați-vă de o încercare GRATUITĂ completă de 30 de zile, fără card de credit necesar! Ia-L Acum

Partea 2: Verificați dacă o dată este sărbătoare publică în Excel

După enumerarea tuturor datelor sărbătorilor legale ale anumitor ani, putem verifica cu ușurință dacă o dată este sărbătoare publică sau nu cu formule în Excel. Presupunând că aveți o listă de date așa cum este afișată următoarea captură de ecran și voi introduce modalitățile de a o face cu ușurință.

Selectați o celulă goală pe lângă lista de date, spune Celula B18, introduceți formula = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), „Vacanță”, „Nu”) în el și apoi trageți mânerul de umplere în intervalul de care aveți nevoie. Vedeți captura de ecran de mai sus:

notițe:

(1) În formula = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), „Vacanță”, „Nu”), $ C $ 2: $ D $ 11 este intervalul de sărbători legale în anii specifici și A18 este celula cu data pe care doriți să o verificați dacă este o sărbătoare publică și le puteți modifica în funcție de nevoile dvs. Și această formulă va returna „Vacanță” dacă data specifică este o sărbătoare publică și va returna „Nu” dacă nu este.

(2) Puteți aplica, de asemenea, această formulă matrice = IF (SAU ($ C $ 2: $ D $ 11 = A18), „Vacanță”, „NU”) pentru a verifica dacă data corespunzătoare este sau nu sărbătoare.


Partea 3: Numărați zilele între două zile, cu excepția weekendurilor și a sărbătorilor în Excel

În partea 1 am enumerat toate sărbătorile dintr-un anumit an, iar acum această metodă vă va ghida să numărați numărul de zile, cu excepția tuturor weekendurilor și a sărbătorilor într-un interval de date.

Selectați o celulă goală, veți returna numărul de zile și introduceți formula = ZILE DE REȚEA (E1, E2, B2: B10) în el și apăsați tasta Intrați cheie.

notițe: În celulele de mai sus, E1 este data de începere a intervalului de date specificat, E2 este data de sfârșit și B2: B10 este lista de vacanță pe care o calculăm în partea 1.

doc verifică dacă sărbătoarea publică

Acum veți obține numărul de zile, cu excepția weekendurilor și a sărbătorilor, în intervalul de date specificat.

Copiați formulele exact / static fără a modifica referințele de celule în Excel

Kutools pentru Excel Copie exactă utilitarul vă poate ajuta să copiați cu ușurință mai multe formule exact fără a modifica referințele de celule în Excel, împiedicând actualizarea automată a referințelor de celule relative.


formula de copiere exactă a anunțului 3

Kutools pentru Excel - Supercharge Excel cu peste 300 de instrumente esențiale. Bucurați-vă de o încercare GRATUITĂ completă de 30 de zile, fără card de credit necesar! Ia-L Acum

Cele mai bune instrumente de productivitate de birou

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp.  Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...

fila kte 201905


Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Like the Observed Holiday, I need to also recognize additional days off outside of the Holiday or observed holidays. Example: If Christmas is on a Friday, I need to calculate the days before and after (until New Years) off.
This comment was minimized by the moderator on the site
How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
This comment was minimized by the moderator on the site
How to make it show the actual name of the holiday instead of just "holiday"?
This comment was minimized by the moderator on the site
Hi KC,
You can change the text “holiday” to INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) in the formula, and the whole formula will be changed to
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Please note that the dates you will check should be placed in one column.
This comment was minimized by the moderator on the site
Thanks, I have worked out a system to determine whether a public holiday is a weekday, but this also gives an alternative. The problem is that if one does it per month, then there are gaps between days where public holidays occur during weekdays. An example as below taking part of December 2017. the figures to immediate right of dates (Col B) are the WEEKDAY values. If date falls on a Saturday or Sunday (value 6 or 7) then the C Column reflects a blank cell ("") if a weekday the Cell has a "1", if a Public Holiday during a weekday then a "0" 21/12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28/12/2017 4 1 29/12/2017 5 1 30/12/2017 6 31/12/2017 7 I can then sort manually using the Filter approach to get the 1's in one continuous column of rows without the blanks or 0's. Copy and paste to a worksheet where I can import the data into the temperature charts. I am trying to get the filter section automated either via formula by deleting all the 0's and blank cells with the resultant shifting up of cells containing the 1's, or via VBA. The ultimate prize would be combining the steps in Column A and Column C into one formula. The end game is to populate a temperature chart with the workday name and in the next corresponding row the day of the required month Mon Tue Thu Fri 7 8 10 11 Using August as an example where the 9th is a public holiday that falls during a work day, resulting in the data relating to the Wed being removed and the rest of the column shifting up one (or more) places. Then transposed into the above cells. I hope I am explaining with sufficient clarity :-)
This comment was minimized by the moderator on the site
How could I make this work for Federal Holiday? Meaning if the date of a holiday happens to fall on a weekend then the Federal holiday would either be Friday or Monday.
This comment was minimized by the moderator on the site
I used the formulas above to calculate the actual day of the holiday and made a second column for Observed holiday. I made this formula to accomplish this: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). The cell reference B15 is referring to the holiday which is in the actual holiday column, in this case New Years Day. When the actual holiday falls on a Saturday, the Observed holiday will be listed as Friday and for actual holidays falling on Sunday, the observed holiday will be listed as Monday. Hope this helps.
This comment was minimized by the moderator on the site
This is an accurate function which will work for New Years Day that would fall on a weekend (years 2022 and 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
This comment was minimized by the moderator on the site
trying to make a formula for subtracting CALENDAR DAYS and holidays. I have been able to figure out for WORKDAYS and HOLIDAY, but I cannot figure out how to do CALENDAR days and holidays. here is what I am currently using for WORKDAYS AND HOLIDAYS. Help! So I need this to be CALENDAR days instead of WORKDAYS.] =WORKDAY(B28-5,1,HOLIDAYS)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations