För att utföra vissa uppgifter i Excel måste du bestämma hur många dagar som har gått mellan vissa datum. Lyckligtvis har programmet verktyg som kan lösa detta problem. Låt oss ta reda på hur du kan beräkna datumskillnaden i Excel.
Innan du börjar arbeta med datum måste du formatera cellerna så att de passar detta format. I de flesta fall, när du anger en uppsättning tecken som liknar ett datum, formateras själva cellen om. Men det är bättre att göra det manuellt för att skydda dig från överraskningar.
Nu kommer programmet att känna igen all data som kommer att finnas i de valda cellerna som ett datum.
Metod 1: enkel beräkning
Det enklaste sättet att beräkna skillnaden i dagar mellan datum är att använda den vanliga formeln.
Metod 2: RAZNDAT-funktion
Du kan också använda en speciell funktion för att beräkna skillnaden i datum RAZNDAT. Problemet är att det inte finns i funktionsguidens lista, så du måste ange formeln manuellt. Dess syntax ser ut så här:
RAZNDAT(startdatum, slutdatum, enhet)
"Enhet"— detta är formatet som resultatet kommer att visas i den valda cellen. De enheter som summan kommer att returneras i beror på vilket tecken som infogas i denna parameter:
- "y" - hela år;
- "m" - hela månader;
- "d" - dagar;
- "YM" - skillnad i månader;
- "MD" är skillnaden i dagar (månader och år beaktas inte);
- "YD" är skillnaden i dagar (år beaktas inte).
Du bör också notera att, till skillnad från den enkla formelmetoden som beskrivs ovan, när du använder den här funktionen, bör startdatumet vara i första hand och slutdatumet i det andra. Annars blir beräkningarna felaktiga.
Metod 3: beräkna antalet arbetsdagar
I Excel är det även möjligt att beräkna arbetsdagar mellan två datum, det vill säga exklusive helger och helgdagar. För att göra detta, använd funktionen CHISTRABNI. Till skillnad från den tidigare operatören finns den i funktionsguidens lista. Syntaxen för denna funktion är följande:
NÄTVERKDAGAR(startdatum, slutdatum, [helgdagar])
I denna funktion är huvudargumenten desamma som operatörens RAZNDAT– initial och slutdatum. Det finns också ett valfritt argument "Högtider".
Istället bör du ersätta datumen för icke-arbetande helgdagar, om några, för den period som omfattas. Funktionen beräknar alla dagar i det angivna intervallet, exklusive lördagar, söndagar, samt de dagar som lagts till av användaren i argumentet "Högtider".
Efter ovanstående manipulationer kommer antalet arbetsdagar för den angivna perioden att visas i den förvalda cellen.
Som du kan se ger Excel sina användare ganska praktiska verktyg för att beräkna antalet dagar mellan två datum. Samtidigt, om du bara behöver beräkna skillnaden i dagar, skulle det bästa alternativet vara att använda en enkel subtraktionsformel istället för att använda funktionen RAZNDAT. Men om du till exempel behöver räkna antalet arbetsdagar så kommer funktionen till undsättning NÄTVERKDAGAR. Det vill säga, som alltid, ska användaren bestämma exekveringsverktyget efter att han har ställt in en specifik uppgift.
En uppgiftsplan har skapats på ett Excel-kalkylblad. En kolumn visar förfallodatum för varje slutförande uppgift. För att markera försenade deadlines för tilldelade uppgifter i färg behöver vi den exakta skillnaden mellan datumen i Excel. För att göra detta kommer vi att använda villkorlig formatering med RAZNDAT-formeln.
Hur man beräknar skillnaden mellan datum i Excel
Det är nödvändigt att markera i färg namnen på uppgifter vars deadlines löper ut om 7 dagar. Exempel på en arbetsplanstabell:
Den slutliga effekten av att markera slutet av deadlines efter 7 dagar:
Markerad grön alla uppgifter som ska vara klara inom 7 dagar. Om du ändrar värdena i cell D2 kommer andra uppgifter att markeras.
Användbara råd! I cell D2 kan du använda en funktion för att få dagens datum: =TODAY().
Formel för datumskillnad i Excel
Formeln returnerar skillnaden i datum mellan idag och måldatumet i dagar. För att lösa detta problem, använd RAZNDAT-funktionen i Excel: var kan jag hitta den här formeln?
Du hittar inte denna funktion i funktionsguiden eller ens i FORMLER-panelen. Den måste alltid matas in manuellt. Det första argumentet till funktionen ska alltid vara det senaste datumet, och det andra argumentet ska alltid vara det högsta datumet. Funktionens tredje argument bestämmer måttenheten för den kvantitet som funktionen =RAZNDAT() returnerar. I det här fallet är det symbolen "d" - dagar. Det betyder att funktionen returnerar antalet dagar. Därefter kommer operatören<7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.
Om det behövs kan du lägga till en ny villkorlig formateringsregel till det här intervallet, som varnar oss om utgången av deadline två veckor i förväg. För att göra detta måste du lägga till en ny villkorlig formateringsregel för intervallet A2:A7 och bara ändra formeln i den nya regeln:
Formateringsfärgen för den andra regeln kan ställas in till gul. Det måste finnas två regler för villkorlig formatering som tillämpas på samma intervall. För att kontrollera, välj verktyget: "HEM" - "Stilar" - "Villkorlig formatering" - "Hantera regler". Eftersom vi kör den översta regeln först, bör vi ändra deras ordning i fönstret som visas: "Conditional Formatting Rules Manager". Annars kommer alla valda uppgifter att ha en gul cellfyllning. Markera bara den första regeln och tryck på nedknappen (CTRL+nedåtpil), som visas i bilden:
Som ett resultat varnar planen oss först två veckor, och sedan en vecka före slutet av uppgifterna:
Användbara råd! Om det finns många regler tilldelade till samma intervall, följ prioriteringshierarkin i den ordning i vilken de exekveras i regelhanteringshanteraren. Ju högre regel, desto högre exekveringsprioritet i förhållande till andra som ligger under den.
Här är ett kodavsnitt:
Query = New Query("SELECT | SELECT | WHENDATE DIFFERENCE(&D1, ADDCDATE(&D2, DAY, 1), DAY) - DATE DIFFERENCE(BEGINNING OF PERIOD(&D1, MONTH), BEGINNING OF PERIOD(ADDCDATE(&D2, DAY, 1 ), MÅNAD), DAG)< 0 | ТОГДА ДЕНЬ(КОНЕЦПЕРИОДА(&Д1, МЕСЯЦ)) + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) | КОНЕЦ КАК Дни, | ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) < 0 | ТОГДА ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА 12 + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | КОНЕЦ - 1 | ИНАЧЕ ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА 12 + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | КОНЕЦ | КОНЕЦ КАК Месяцы, | ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД) - 1 | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД) | КОНЕЦ КАК Годы"); Запрос.УстановитьПараметр("Д2",КонечнаяДата); Запрос.УстановитьПараметр("Д1",НачальнаяДата); ТабДМГ=Запрос.Выполнить().Выгрузить(); Стр = ТабДМГ; Сообщить("Разность дат составляет: "+Стр.Дни+" дней "+Стр.Месяцы+" месяцев "+Стр.Годы+" лет ");
Naturligtvis kan du även lägga till korrekt stavning av dagar, månader och år beroende på antalet. Men det är ett annat ämne.
Här räknar vi:
* i dagar- vi tar det totala antalet dagar i intervallet och subtraherar det redan beaktade antalet dagar, och detta är skillnaden mellan början av våra månader från datumen. Detta antal kommer att beaktas i månadsberäkningsraden. Men när vi beräknar dagar kontrollerar vi antalet mottagna dagar för negativitet. Om antalet dagar är negativt betyder det att vi har flyttat till nästa månad, men i själva verket har månaden mellan datumen inte passerat ännu. För att ta hänsyn till detta lägger vi till antalet dagar i månaden för det första datumet till det negativa antalet dagar.
*i månader- vi tar också det totala antalet månader och subtraherar det redan beaktade antalet månader, och detta är skillnaden mellan början av våra ben. Detta antal kommer också att tas med i beräkningsraden för år. Men när vi beräknar månaden kontrollerar vi också antalet erhållna månader för negativitet. Om antalet månader är negativt betyder det att vi har flyttat in i det nya året, men i själva verket har året mellan datumen inte passerat ännu. För att ta hänsyn till detta lägger vi till 12 till det negativa antalet månader.
* för året- ta antalet år mellan datum. Men om antalet månader var negativt, så subtraherar vi ett år, eftersom han har fortfarande inte passerat än.
Och jag lägger till en dag till den andra dejten, eftersom... Jag behöver den här sista dagen för att vara med i beräkningen.
På något sätt så här. Om du har några frågor, skriv.
Använd DATEDIF-funktionen när du behöver beräkna skillnaden mellan två datum. Ange först ett startdatum i en cell och ett slutdatum i en annan. Ange sedan en formel, till exempel en av följande.
Dagsskillnad
I det här exemplet är startdatumet i cell D9 och slutdatumet i E9. Formeln kommer att visas på F9. "D" returnerar antalet hela dagar mellan två datum.
Veckans skillnad
I det här exemplet är startdatumet i cell D13 och slutdatumet i E13. "D" returnerar antalet dagar. Men lägg märke till vad som är på slutet /7 . Detta delar antalet dagar med 7, eftersom det finns 7 dagar i en vecka. Observera att detta resultat också måste formateras som ett tal. Tryck på CTRL + 1. Klicka sedan siffra _gt_ decimaler: 2.
Skillnad i månader
I det här exemplet finns startdatumet i cell D5 och slutdatumet i den nedre cellen. I formeln "m" returnerar antalet hela månader mellan två dagar.
Årsskillnad
I det här exemplet är startdatumet i cell D2 och slutdatumet i E2. "Y"
Beräknar ålder i ackumulerade år, månader och dagar
1. Använd RAZNDAT för att hitta det totala antalet år.
I det här exemplet finns startdatumet i cell D17 och slutdatumet i E17. I formeln "y" returnerar antalet hela år mellan två dagar.
2. För att söka i månader, använd RAZNDAT igen och anger "GM".
I en annan cell använder du RAZNDAT-formeln med parametern "GM". "GM" returnerar antalet återstående månader efter det senaste hela året.
3. Använd en annan formel för att hitta dagar.
Nu måste vi hitta antalet återstående dagar. Detta kan göras genom att skriva en annan typ av formel som visas ovan. Denna formel subtraherar den första dagen i månadsslutet (05/01/2016) från det ursprungliga slutdatumet i cell E17 (05/06/2016). Så här görs det: Först skapar DATE-funktionen datumet 05/01/2016. Den skapas med årtalet i cell E17 och månaden i cell E17. 1 betecknar den första dagen i månaden. Resultatet av DATE-funktionen blir 05/01/2016. Vi subtraherar sedan detta datum från det ursprungliga slutdatumet i cell E17 (05/06/2016), vilket resulterar i 5 dagar.
4. valfritt: Kombinera tre formler till en.
Du kan placera alla tre beräkningarna i en cell, som visas i detta exempel. Användning av et-tecken, citattecken och text. Det här är en längre formel att ange, men det finns åtminstone allt i en av dem. Råd. Tryck på Alt+Enter för att placera radbrytningar i formeln. Detta gör det lättare att läsa. Om du inte kan se hela formeln trycker du också på CTRL+SHIFT+U.
Ladda ner exempel
Du kan ladda ner en exempelbok med alla exempel som ges i den här artikeln. Du kan prenumerera på dem eller skapa dina egna formler.
Andra datum- och tidsberäkningar
Beräkning mellan idag och ett annat datum
Som visas ovan beräknar DATEDIF-funktionen skillnaden mellan startdatum och slutdatum. Men istället för att ange specifika datum kan du också använda funktionen I dag() i formeln. När du använder funktionen TODAY() använder Excel det aktuella datumet på din dator. Tänk på att om du öppnar filen igen i framtiden kommer filen att ändras.
Beräkning av arbetsdagar med eller utan helgdagar
Använd NETWORKDAYS. INTL om du vill beräkna antalet arbetsdagar mellan två datum. Dessutom kan du också utesluta helger och helgdagar.
Innan du börjar, följ dessa steg: Bestäm om du vill utesluta semesterdatum. Om så är fallet, ange listan över semesterdatum i ett separat område eller ark. Varje semesterdag placeras i en separat cell. Välj sedan dessa celler och välj sedan formler _gt_ tilldela namn. Namnge intervallet micholidais och tryck på knappen OK. Skapa sedan en formel med hjälp av stegen nedan.
1. Ange startdatum och slutdatum.
I det här exemplet finns startdatumet i cell D53 och slutdatumet i cell E53.
2. I en annan cell anger du en formel, till exempel:
Ange en formel, som exemplet ovan. 1 i formeln definierar "lördag" och "söndag" som lediga dagar och exkluderar dem från summan.
Notera. Excel 2007 har inte NETWORKDAYS. INTERNATIONELL Däremot har han KLARA DAGAR. Exemplet ovan skulle se ut så här i Excel 2007: = NÄTVERKDAGAR (D53, E53). Du anger inte 1 eftersom NETWORKDAYS antar att helgerna är lördagar och söndagar.
3. Ändra värde 1 vid behov.
Om lördag och söndag inte är helgdagar, ändra värdet 1 till något annat i IntelliSense-listan. Till exempel, 2 uppsättningar söndag och måndag som helger.
Om du använder Excel 2007, hoppa över det här steget. Funktionen NÄTVERKDAGAR i Excel 2007 förutsätter alltid att helger är lördagar och söndagar.
4. Ange ett namn för semesterintervallet.
Om du skapade ett semesterintervallsnamn i avsnittet Komma igång ovan anger du det i slutet som visas nedan. Om du inte har semester kan du lämna komma och Micholydays. Om du använder Excel 2007 skulle exemplet ovan se ut så här: = NÄTVERKDAGAR (D53, E53, micholidays).
Fjäder Om du inte vill referera till semesterintervallets namn kan du också ange ett intervall som D35:E:39. Alternativt kan du ange varje helgdag i formeln. Till exempel, om helgdagarna är 1 och 2 januari 2016, skriv in dem enligt följande: = NÄTVERKSDAGAR. Int (D53, E53, 1, ("1/1/2016", "1/2/2016")). I Excel 2007 kommer det att se ut så här: = NÄTVERKDAGAR (D53, E53, ("1/1/2016", "1/2 . 2016"})
Beräkning av nedlagd tid
För att beräkna tidsåtgången kan du subtrahera en tid från den andra. Ange först starttiden i en cell och sluttiden i en annan. Se till att alla tider, inklusive timmar, minuter och mellanrum, är ifyllda före middagstid eller PM. Här är vad du behöver göra för att göra detta:
1. Ange starttid och sluttid.
I det här exemplet är starttiden i cell D80 och sluttiden är i E80. Se till att du anger timmar, minuter och mellanslag före tecknen AM och PM.
2. Ställ in h/pm-formatet.
Välj båda datumen och tryck på Ctrl+1 (eller +1 på en Mac). Se till att alternativet är valt beställnings- _gt_ h/pm om den inte redan är installerad.
3. subtrahera två värden.
I en annan cell subtraherar du startcellen från cellen "sluttid".
4. Ställ in timformatet.
Tryck på CTRL+1 (eller +1 på Mac). Välj " beställnings- _gt_" för att utesluta resultaten av "AM" och "PM".
MS Excel har en extremt intressant funktion som få människor känner till. Så lite att Excel inte ens ger en kontextuell ledtråd för denna funktion när man går in, även om den konstigt nog finns i programhjälpen och beskrivs ganska bra. Det heter RAZNDAT() eller DATEDIF() och tjänar till att automatiskt beräkna skillnaden i dagar, månader eller år mellan två givna datum.
Låter inte mycket? Faktum är att ibland möjligheten att snabbt och exakt beräkna hur lång tid som har gått sedan en händelse kan vara väldigt användbar. Hur många månader har gått sedan din födelsedag, hur länge har du suttit på det här jobbet, eller hur många dagar har du gått på diet - men vem vet hur många användningsområden denna användbara funktion har? Och viktigast av allt, beräkningen kan automatiseras och varje gång du öppnar en MS Excel-arbetsbok kan du få korrekta data specifikt för idag! Låter intressant, eller hur?
Funktionen RAZNDAT() tar tre argument:
- Start datum- datum från vilket kontot förs
- Sista datum- till vilken räkningen görs
- Enhet- dagar, månader, år.
Det är skrivet så här:
=DATE(startdatum, slutdatum, måttenhet)
Måttenheter skrivs som:
- "y"— datumskillnad i hela år
- "m"— datumskillnad i hela månader
- "d"— datumskillnad i hela dagar
- "yd"— Datumskillnad i dagar från början av året, exklusive år
- "md"— Datumskillnad i dagar exklusive månader och år
- "ym"— Datumskillnad i hela månader exklusive år
Med andra ord, för att beräkna min nuvarande ålder i år, skriver jag funktionen som:
=RASDAT(1984-07-14; 2016-03-22;"y")
Observera att det sista argumentet alltid står inom citattecken.
Om jag vill få den exakta åldern kommer jag att skriva en komplicerad formel:
=RAZNDAT(F2;G2;"y")&" år "&RAZNDAT(F2;G2;"ym")&" månader"
I vilken RAZNDAT()-funktionen anropas två gånger samtidigt, med olika värden, och orden "år" och "månader" är helt enkelt dockade till resultatet. Det vill säga att den verkliga kraften i funktionen endast visas när den kombineras med andra MS Excel-funktioner.
Ett annat intressant alternativ är att lägga till en räknare till funktionen som rör sig dagligen i förhållande till dagens datum. Till exempel, om jag bestämmer mig för att skriva en formel som beräknar antalet dagar till min semester i standardform, kommer det att se ut ungefär så här:
Och allt skulle vara korrekt om jag, när jag öppnade det här bladet en vecka senare, skulle se att antalet dagar till semester har minskat. Jag kommer dock att se samma siffra - eftersom de ursprungliga datumen inte har ändrats. Följaktligen skulle jag behöva ändra det aktuella datumet, och då skulle funktionen RAZNDAT() göra allt korrekt.
För att undvika denna irriterande lilla sak, som det första argumentet (dagens datum), kommer jag inte att ersätta en referens till värdet lagrat i cellen, utan en annan funktion. Denna funktion kallas TODAY() och dess huvudsakliga och enda uppgift är att returnera dagens datum.
En gång, och problemet är löst - från och med nu, när jag öppnar detta MS Excel-ark, kommer funktionen RAZNDAT() alltid att visa mig det exakta värdet beräknat med hänsyn till dagens datum.
