Създаден е план за задачи в работен лист на Excel. Една колона показва крайната дата за всяка задача за завършване. За да подчертаем цветно просрочените срокове за възложени задачи, ни трябва точната разлика между датите в Excel. За да направим това, ще използваме условно форматиране с формулата RAZNDAT.

Как да изчислим разликата между датите в Excel

Необходимо е да се маркират с цвят имената на задачите, чиито срокове изтичат след 7 дни. Пример за таблица с план на задачите:

Крайният ефект от подчертаването на края на сроковете след 7 дни:

Всички задачи, които трябва да бъдат изпълнени за 7 дни, са маркирани в зелено. Ако промените стойностите в клетка D2, други задачи ще бъдат маркирани.

Полезен съвет!

В клетка D2 можете да използвате функция, за да получите днешната дата: =TODAY().

Формула за разлика в дата в Excel

Формулата връща разликата в датите между днешната и целевата дата в дни. За да разрешите този проблем, използвайте функцията RAZNDAT в Excel: къде мога да намеря тази формула?<7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.

Ако е необходимо, можете да добавите ново правило за условно форматиране към този диапазон, което ще ни предупреди за края на крайния срок 2 седмици предварително. За да направите това, трябва да добавите ново правило за условно форматиране за диапазона A2:A7 и леко да промените формулата в новото правило:

Цветът на форматиране за второто правило може да бъде зададен на жълто. Трябва да има 2 правила за условно форматиране, приложени към един и същи диапазон. За да проверите, изберете инструмента: „НАЧАЛО“ - „Стилове“ - „Условно форматиране“ - „Управление на правила“. Тъй като първо изпълняваме горното правило, трябва да променим техния ред в прозореца, който се появява: „Диспечер на правилата за условно форматиране“. В противен случай всички избрани задачи ще имат запълване на жълти клетки. Просто маркирайте първото правило и натиснете бутона надолу (CTRL+стрелка надолу), както е показано на фигурата:

В резултат на това планът ни предупреждава първо две седмици, а след това седмица преди края на задачите:

Полезен съвет!

Ако има много правила, присвоени на един и същи диапазон, следвайте приоритетната йерархия на реда, в който се изпълняват в диспечера за управление на правила. Колкото по-високо е правилото, толкова по-висок е приоритетът му на изпълнение спрямо другите, разположени под него.

За да изпълните определени задачи в Excel, трябва да определите колко дни са изминали между определени дати. За щастие програмата разполага с инструменти, които могат да разрешат този проблем. Нека разберем как можете да изчислите разликата в датата в Excel.

Преди да започнете да работите с дати, трябва да форматирате клетките, за да отговарят на този формат. В повечето случаи, когато въведете набор от знаци, подобни на дата, самата клетка се преформатира. Но е по-добре да го направите ръчно, за да се предпазите от изненади.

Сега програмата ще разпознае всички данни, които ще се съдържат в избраните клетки като дата.

Метод 1: просто изчисление

Най-лесният начин да изчислите разликата в дните между датите е да използвате обичайната формула.

Метод 2: Функция RAZNDAT Можете също да използвате специална функция за изчисляване на разликата в датитеРАЗНДЪТ

. Проблемът е, че не е в списъка на съветника за функции, така че ще трябва да въведете формулата ръчно. Синтаксисът му изглежда така:

RAZNDAT(начална_дата, крайна_дата, единица)"единица"

• “y” - пълни години;
• “m” - пълни месеци;
• "d" - дни;
• "YM" - разлика в месеци;
• “MD” е разликата в дните (месеците и годините не се вземат предвид);
• „YD“ е разликата в дните (годините не се вземат предвид).

Трябва също така да имате предвид, че за разлика от метода с проста формула, описан по-горе, когато използвате тази функция, началната дата трябва да бъде на първо място, а крайната дата на второ. В противен случай изчисленията ще бъдат неправилни.

Метод 3: изчисляване на броя на работните дни

В Excel също е възможно да се изчислят работните дни между две дати, т.е. без уикендите и празниците. За да направите това, използвайте функцията ЧИСТРАБНИ. За разлика от предишния оператор, той присъства в списъка на съветника за функции. Синтаксисът за тази функция е както следва:

NETWORKDAYS(начална_дата, крайна_дата, [празници])

В тази функция основните аргументи са същите като тези на оператора Можете също да използвате специална функция за изчисляване на разликата в датите– начална и крайна дата. Има и незадължителен аргумент "празници".

Вместо това трябва да замените датите на неработни празници, ако има такива, за периода, който се обхваща. Функцията изчислява всички дни от посочения диапазон, с изключение на събота, неделя, както и дните, добавени от потребителя към аргумента "празници".

След горните манипулации броят на работните дни за посочения период ще се покаже в предварително избраната клетка.

Както можете да видите, Excel предоставя на своите потребители доста удобни инструменти за изчисляване на броя на дните между две дати. В същото време, ако просто трябва да изчислите разликата в дни, тогава по-оптималният вариант би бил да използвате проста формула за изваждане, вместо да използвате функцията Можете също да използвате специална функция за изчисляване на разликата в датите. Но ако имате нужда например да преброите броя на работните дни, тогава функцията ще дойде на помощ МРЕЖИ ДНИ. Тоест, както винаги, потребителят трябва да вземе решение за инструмента за изпълнение, след като е поставил конкретна задача.

Калкулаторът за дати е предназначен да изчислява броя на дните между датите, както и да намира дата чрез добавяне или изваждане на определен брой дни към известна дата.

Добавете дни към датата

За да разберете коя дата ще бъде след определен брой дни, използвайте тази опция. Въведете началната дата и броя на дните, които да добавите към нея. За да извадите, използвайте минус стойност. Калкулаторът има и опция за добавяне само на работни дни.

Изчисляване на броя на дните между датите

Този метод на изчисление ще отговори на въпроса "колко дни са изминали от датата". Въведете началната и крайната дата и щракнете върху бутона "изчисли". Калкулаторът ще покаже колко дни има между въведените дати. Отделно калкулаторът ще покаже броя на работните дни.

Използвайки тази опция, можете да изчислите колко дни остават до определено събитие, например рожден ден или празник. За да направите това, въведете днешната дата в полето за начална дата и датата на събитието в полето за крайна дата.

празници

Калкулаторът може да изчислява, събира и изважда както календарни, така и работни дни. Официални неработни празници са:

• 1,2,3,4,5,6,8 януари - новогодишни празници
• 7 януари - Православна Коледа
• 23 февруари - Ден на защитника на отечеството
• 8 март - Международен ден на жената
• 1 май - Ден на пролетта и труда
• 9 май - Ден на победата
• 12 юни - Ден на Русия
• 4 ноември - Ден на народното единство

Ако празник се пада събота или неделя, той се премества за следващия работен ден. Но понякога уикендът се премества на съвсем друго място в календара. Например събота и неделя, които се падат на новогодишните празници, могат да бъдат преместени през май, за да се удължат майските празници.

И така, през 2019 г. ситуацията е следната...

Отлагане на празниците през 2019г

Освен официалните празнични дати, през 2019 г. почивните дни са и 2, 3 и 10 май поради изместването на почивните дни от новогодишните празници.

При изчисляване на дните нашият калкулатор взема предвид както официалните празници, така и всички трансфери.

За да изчислите продължителността на интервалите от време, най-удобно е да използвате недокументираната функция RAZNDAT( ) , английска версия на DATEDIF().

Функцията RAZNDAT() не е в помощта и в EXCEL2007 Съветник за функции (SHIFT+ Е3 ), но работи, макар и не без недостатъци.

Синтаксис на функцията:

ДАТА(начална_дата; крайна_дата; метод_на_измерване)

Аргумент начална_дататрябва да дойде преди аргумента крайна_дата.

Аргумент метод_на_измерванеопределя как и в какви единици ще се измерва интервалът между началната и крайната дата. Този аргумент може да приема следните стойности:

По-долу е дадено подробно описание на всички 6 стойности на аргумента метод_на_измерване, както и алтернативни формули (функцията RAZNDAT() може да бъде заменена с други формули (макар и доста тромави). Това се прави в примерния файл).

В примерния файл стойността на аргумента е начална_датапоставени в клетка A2 и стойността на аргумента крайна_дата– в клетка B2 .

1. Разлика в дните ("d")

Формулата =DATEDAT(A2;B2,"d") ще върне простата разлика в дни между две дати.

Пример1:начална_дата 25.02.2007, крайна_дата 26.02.2007
Резултат: 1 (ден).

Този пример показва, че при изчисляване на трудов стаж е необходимо да използвате функцията RAZNDAT() с повишено внимание. Очевидно, ако служител е работил на 25 и 26 февруари, той е работил 2 дни, а не 1. Същото важи и за изчисляването на пълните месеци (вижте по-долу).

Пример2:начална_дата 01.02.2007, крайна_дата 01.03.2007
Резултат: 28 (дни)

Пример3:начална_дата 28.02.2008, крайна_дата 01.03.2008
Резултат: 2 (дни), защото 2008 е високосна година

Забележка: Ако се интересувате само от работни дни, тогава k между две дати може да се изчисли по формулата = NETWORKDAYS(B2;A2)

2. Разлика в пълните месеци ("m")

Формулата =ДАТА(A2;B2;"m") ще върне броя на пълните месеци между две дати.

Пример1:начална_дата 01.02.2007, крайна_дата 01.03.2007
Резултат: 1 (месец)

Пример2:начална_дата 01.03.2007, крайна_дата 31.03.2007
Резултат: 0

При изчисляване на трудовия стаж се счита, че работник или служител, който е работил всички дни от месеца, е работил 1 пълен месец. Функцията RAZNDAT() не мисли така!

Пример3:начална_дата 01.02.2007, крайна_дата 01.03.2009
Резултат: 25 месеца

=12*(YEAR(B2)-YEAR(A2))-(MONTH(A2)-MONTH(B2))-(DAY(B2)<ДЕНЬ(A2))

внимание: В помощта на MS EXCEL (вижте раздела Изчисляване на възрастта) има извита формула за изчисляване на броя на месеците между 2 дати:

=(YEAR(TDATE())-YEAR(A3))*12+MONTH(TDATE())-MONTH(A3)

Ако вместо функцията TDATE() - текущата дата, използвате датата 10/31/1961 и въведете 11/01/1962 в A3, тогава формулата ще върне 13, въпреки че всъщност са изминали 12 месеца и 1 ден (ноември и декември 1961 г. + 10 месеца 1962 г.).

3. Разлика в пълните години ("y")

Формулата =ДАТА(A2;B2;"y") ще върне броя на пълните години между две дати.

Пример1:начална_дата 01.02.2007, крайна_дата 01.03.2009
Резултат: 2 години)

Пример2:начална_дата 01.04.2007, крайна_дата 01.03.2009
Резултат: 1 (година)

Формулата може да бъде заменена с алтернативен израз:
=АКО(ДАТА(ГОДИНА(B2),МЕСЕЦ(A2),ДЕН(A2))<=B2;
YEAR(B2)-YEAR(A2);YEAR(B2)-YEAR(A2)-1)

4. Разлика в пълните месеци без годините („ym“)

Формулата =DASDAT(A2;B2;"ym") ще върне броя на пълните месеци между две дати, с изключение на години (вижте примерите по-долу).

Пример1:начална_дата 01.02.2007, крайна_дата 01.03.2009
Резултат: 1 (месец), т.к Сравняват се крайната дата 01.03.2009 г. и променената начална дата 01.02. 2009 (годината на началната дата се заменя с годината на крайната дата, тъй като 01.02 е по-малко от 01.03)

Пример2:начална_дата 01.04.2007, крайна_дата 01.03.2009
Резултат: 11 (месеца), т.к Сравняват се крайната дата 01.03.2009 г. и променената начална дата 01.04. 2008 (годината на началната дата се заменя с годината на крайната дата минус 1 година, защото 01.04 повече от 01.03)

Формулата може да бъде заменена с алтернативен израз:
=REMAT(C7;12)
В клетка C7 разликата трябва да е в цели месеци (виж клауза 2).

5. Разлика в дните без месеци и години („md“)

Формулата =DASDAT(A2;B2;"md") ще върне броя на дните между две дати, без да се вземат предвид месеците и годините. Не се препоръчва използването на функцията RAZNDAT() с този аргумент (вижте примерите по-долу).

Пример1:начална_дата 01.02.2007, крайна_дата 06.03.2009
Резултат1: 5 (дни), защото Сравняват се крайната дата 03/06/2009 и модифицираната начална дата 01. 03 .2009 (годината и месецът на началната дата се заменят с годината и месецът на крайната дата, тъй като 01 е по-малко от 06)

Пример2:начална_дата 28.02.2007, крайна_дата 28.03.2009
Резултат2: 0, защото Сравняват се крайната дата 28.03.2009 г. и променената начална дата 28. 03 .2009 (годината и месецът на началната дата се заменят с годината и месецът на крайната дата)

Пример3:начална_дата 28.02.2009, крайна_дата 01.03.2009
Резултат3: 4 (дни) - напълно неразбираем и НЕТОЧЕН резултат. Отговорът трябва да бъде =1. Освен това резултатът от изчислението зависи от версията на EXCEL.

EXCEL 2007 версия с SP3:

Резултат – 143 дни! Повече от дни в месеца!

EXCEL 2007 версия:

Разликата между 28.02.2009 и 01.03.2009 е 4 дни!

Освен това в EXCEL 2003 с SP3 формулата връща правилния резултат след 1 ден. За стойностите 31.12.2009 г. и 01.02.2010 г. резултатът като цяло е отрицателен (-2 дни)!

Не препоръчвам да използвате формула с горната стойност на аргумента. Формулата може да бъде заменена с алтернативен израз:
=АКО(ДЕН(A2)>ДЕН(B2);
DAY(MONTH(DATEMONTH(B2,-1),0))-DAY(A2)+DAY(B2);
ДЕН(B2)-ДЕН(A2))

Тази формула е само еквивалентен (в повечето случаи) израз за RAZNDAT() с параметъра md. Прочетете за правилността на тази формула в раздела „Още веднъж за кривината на RAZNDAT()“ по-долу.

6. Разлика в дните без години ("yd")

Формулата =DASDAT(A2;B2,"yd") ще върне броя на дните между две дати, с изключение на годините. Не се препоръчва използването му поради причините, посочени в предходния параграф.

Резултатът, върнат от формулата =DATEDAT(A2;B2,"yd"), зависи от версията на EXCEL.

Формулата може да бъде заменена с алтернативен израз:
=АКО(ДАТА(ГОДИНА(B2),МЕСЕЦ(A2),ДЕН(A2))>B2;
B2-ДАТА(ГОДИНА(B2)-1,МЕСЕЦ(A2),ДЕН(A2));
B2-ДАТА (ГОДИНА (B2), МЕСЕЦ (A2), ДЕН (A2)))

Още веднъж за кривината на RAZNDAT()

Нека намерим разликата между датите 03/16/2015 и 01/30/15. Функцията RAZNDAT() с параметри md и ym ще изчисли, че разликата е 1 месец и 14 дни. Това наистина ли е вярно?

Имайки формула, еквивалентна на RAZNDAT(), можете да разберете напредъка на изчислението. Очевидно в нашия случай броят на пълните месеци между датите е = 1, т.е. цял февруари. За да изчисли дните, функцията намира броя на дните в предходния месец спрямо крайната дата, т.е. 28 (крайната дата принадлежи на март, предходният месец е февруари, а през 2015 г. имаше 28 дни през февруари). След това изважда началния ден и добавя крайния ден = ДЕН(МЕСЕЦ(ДАТАМЕСЕК(B6,-1),0))-ДЕН(A6)+ДЕН(B6), т.е. 28-30+16=14. Според нас все още има 1 пълен месец между датите и всички дни на март, т.е. 16 дни, а не 14! Тази грешка възниква, когато има по-малко дни в предходния месец спрямо крайната дата, отколкото дните от началната дата. Как да излезем от тази ситуация?

Нека променим формулата за изчисляване на дните разлика, без да вземаме предвид месеците и годините:

=IF(DAY(A18)>DAY(B18);IF((DAY(MONTH(DATEMONTH(B18,-1),0))-DAY(A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))

При прилагането на новата функция е необходимо да се има предвид, че разликата в дните ще бъде еднаква за няколко начални дати (вижте фигурата по-горе, дати 28-31.01.2015 г.). В останалите случаи формулите са еквивалентни. Каква формула да използвам? Това зависи от потребителя да реши в зависимост от условията на задачата.