Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako preveriti, ali je datum državni praznik in štetje dni, razen počitnic v Excelu?

Na primer, imate seznam datumov in želite preveriti, ali na državnem prazniku obstajajo kateri koli javni prazniki, kako hitro lahko to storite? V tem članku vam pokažem načine za izračun vseh datumov ameriških državnih praznikov v določenih letih in preverite, ali je datum ameriški praznik v Excelu enostavno.

Del 1: Izračun počitnic v določenih letih v Excelu

Del 2: Preverite, ali je datum praznik v Excelu

Del 3: štetje dni med dvema dnevoma razen vikendov in praznikov v Excelu

Natančno / statično kopirajte formule, ne da bi spremenili reference celic v Excelu

Kutools za Excel Natančno kopiranje pripomoček vam lahko pomaga preprosto kopirati več formul natančno brez spreminjanja referenčnih točk v Excelu, s čimer se samodejno posodabljanje referenčnih celic samodejno posodablja. Popolna brezplačna preizkusna različica 60-dan!

ad natančne formule za kopiranje 3


puščica modra desno mehurčekDel 1: Izračunajte državne praznike z določenim letom v Excelu

Pred preverjanjem, ali je datum državni praznik, morate v Excelu navesti vse počitnice v določenih letih. Zato priprava tabele na naslednji prikaz zaslona vam bo olajšala delo.

doc preveri, če je praznik 1

Obstajajo tri vrste ameriških državnih praznikov:

(1) Prva vrsta je javni praznik na določen datum, kot je novoletni dan na Janu 1st. z lahkoto lahko izračunamo novoletni dan s formulo = DATE (glede na leto, 1,1);

(2) Druga vrsta je javni praznik na določen dan v tednu, kot je dan predsednika. Z dnem lahko enostavno izračunamo dan predsednika = DATE (glede na leto, 1,1) + 14 + CHOOSE (WEEKDAY (DATE (glede na leto, 1,1)), 1,0,6,5,4,3,2);

(3) In zadnja vrsta je dan spomina, lahko zlahka izračunamo dan spomina s formulo = DATE (glede na leto, 6,1) -WEEKDAY (DATE (glede na leto, 6,6)).

Tukaj sem navedel formule za izračun vseh praznikov v naslednji tabeli. Vnesite formule v ustrezno celico in pritisnite Vnesite ključno eno za drugim.

počitnice Celica Formule
Novoletni dan C2 = DATE (C1,1,1)
Martin Luther King Jr dan C3 = DATE (C1,1,1) + 14 + CHOOSE (WEEKDAY (DATE (C1,1,1)), 1,0,6,5,4,3,2)
Predsedniški dan C4 = DATE (C1,2,1) + 14 + CHOOSE (WEEKDAY (DATE (C1,2,1)), 1,0,6,5,4,3,2)
Memorial Day C5 = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6))
Dan neodvisnosti C6 = DATE (C1,7,4)
Praznik dela C7 = DATE (C1,9,1) + IZBERITE (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4,3,2)
Columbus Day C8 = DATE (C1,10,1) + 7 + CHOOSE (WEEKDAY (DATE (C1,10,1)), 1,0,6,5,4,3,2)
Veterani dan C9 = DATE (C1,11,11)
Dan zahvalnosti C10 = DATE (C1,11,1) + 21 + CHOOSE (WEEKDAY (DATE (C1,11,1)), 4,3,2,1,0,6,5)
božični dan C11 = DATE (C1,12,25)

Opomba: V formulah v zgornji tabeli je C1 referenčna celica, ki najde določeno leto. V našem primeru pomeni leto 2015 in ga lahko spremenite glede na vaše potrebe.

S temi formulami lahko enostavno izračunate datume državnih praznikov z določenimi leti. Glej prikaz zaslona spodaj:

doc preveri, če je praznik 2


Shranite obseg kot vnos za samodejno besedilo (ostale formate in formule celic) za ponovno uporabo v prihodnosti

Mora biti zelo dolgočasno, da napotite celice in uporabite formule za izračun vsakega počitka. Kutools za Excel zagotavlja čudno rešitev Samodejni tekst pripomoček, da shranite obseg kot vnos v samobesedilo, ki lahko ostane formate in formule celic v dosegu. In potem boste ponovno uporabili to območje samo z enim klikom. Delo postane enostavno samo z enim klikom, da vstavite to tabelo in spremenite leto v tej tabeli! Popolna brezplačna preizkusna različica 60-dan!

ad auto american prazniki 1

puščica modra desno mehurčekDel 2: Preverite, ali je datum praznik v Excelu

Po navedbi vseh datumov državnih praznikov določenih let, lahko enostavno preverimo, ali je datum praznik ali ne v formulah v Excelu. Predvidevam, da imate seznam datumov, kot je prikazan spodnji prikaz zaslona, ​​in predstavil bom načine, kako to narediti enostavno.

Izberite prazno celico poleg seznama datumov, pravi Cell B18, vnesite formulo = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "praznik", "ne") in nato povlecite ročico za polnjenje v obseg, ki ga potrebujete. Oglejte si sliko zgoraj:

Opombe:

(1) V formuli = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "praznik", "ne") je $ C $ 2: $ D $ 11 obseg državnih praznikov v določena leta, in A18 je celica z datumom, ki ga želite preveriti, ali gre za državni praznik, in jih lahko spremenite glede na vaše potrebe. In ta formula bo vrnila "praznik", če je določen datum javni praznik in se vrne "Ne", če ni.

(2) To formulo matrike lahko uporabite tudi = IF (ALI ($ C $ 2: $ D $ 11 = A18), "praznik", "NE") preverite, če je ustrezen datum praznik ali ne.


puščica modra desno mehurček Del 3: štetje dni med dvema dnevoma razen vikendov in praznikov v Excelu

V delu 1 smo navedli vse počitnice v določenem letu, zdaj pa vam bo ta metoda vodila do štetja števila dni, razen vseh vikendov in praznikov v časovnem obdobju.

Izberite prazno celico, ki ji boste vrnili število dni in vnesite formulo = NETWORKDAYS (E1, E2, B2: B10) vanj in pritisnite Vnesite ključ.

Opombe: V zgornjih celicah je E1 datum začetka določenega časovnega obdobja, E2 je končni datum in B2: B10 je seznam praznikov, ki ga izračunamo v delu 1.

doc preveri, če je praznik 6

Sedaj boste prejeli število dni razen vikendov in praznikov v določenem časovnem obdobju.


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    KC · 9 months ago
    How to make it show the actual name of the holiday instead of just "holiday"?
    • To post as a guest, your comment is unpublished.
      kellytte · 5 months ago
      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.
  • To post as a guest, your comment is unpublished.
    Shawn Bull · 1 years ago
    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 :-)
  • To post as a guest, your comment is unpublished.
    Mark · 2 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Tboo Ted Marshall · 2 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Josh · 7 months ago
        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))
  • To post as a guest, your comment is unpublished.
    LINDA · 2 years ago
    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)