Microsoft обучениe и професионални IT консултации за всеки бизнес

"Power BI for Excel Essentials - курс на ITraining - 27-28-ми април!"

Двудневен тренинг, който ще ви запознае с основните процеси и ще ви научи да работите с инструментите, включени в Power BI еко системата, основно – Power Query и Power Pivot.

Научете повече!

 

Наскоро имахме запитване за формула в Microsoft Excel, която да изчислява трудовия стаж на служителите в дадена компания или организация.

Най-практичният начин е да се използва функцията DATEDIF. Една от малкото недокументирани функции в Excel, и тъй като е „скрита“, няма да я намерите в раздел Formula, нито ще можете да я изберете от падащия списък при опит за въвеждане.

Автор: Валентина Стефанова – сертифициран Microsoft Office Specialist

valentina-stefanova Валентина Стефанова е  Trainer в ITraining.

Тя е сертифициран Microsoft Office Specialist (MOS) и е инструктор по най-популярните продукти в Office пакета – Excel, Word, PowerPoint и Outlook.

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

 

 

Фиг. 1

Фиг. 1

DATEDIF – Приложение

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

  • Изчисляване на трудов стаж
  • Изчисляване продължителност на проект
  • Изчисляване на точна възраст

Синтаксис

=DATEDIF(Date1, Date2, Interval)

Date1Дата 1;

Date2 – втора дата, която задължително е по-късна от Дата 1;

Interval – параметър, който определя крайния резултат. Този аргумент може да приема следните стойности:

Стойност Значение Описание
m Месеци (Months) Пълният брой месеци между двете дати.
d Дни (Days) Пълният брой дни между двете дати
y Години (Years) Пълният брой години между двете дати.
ym Месеци изключат се години (Months Excluding Years) Разликата между двете дати в месеци, изключвайки дни и години.
yd Дни изключват се годините (Days Excluding Years) Разликата между двете дати в дни, изключвайки години.
md Дни, изключват се години и месеци (Days Excluding Years And Months) Разликата между двете дати в дни, изключвайки месеци и години.

Таблица 1

Начин за въвеждане на функцията

Както споменахме в началото, функцията е “скрита“ и не бихте могли да я намерите в списъка с всички функции:

Фиг. 2

Фиг. 2

 

Нито може да се избере при опит за въвеждане:

Фиг. 3

Фиг. 3

 

Затова, следвайте стъпките:

  • Първо избере клетката, в която трябва да е резултът;
  • Въведете знак „=“, цялото име на функцията DATEDIF с отворена кръгла скоба “(“.
fig4

Фиг. 4

  • Въведете аргументите. При работа с дати е добре датите да са въведени в отделни клетки. Уверете се, че Excel да ги разпознава като дати и са подравнени в дясно.(вижте тази статия).
Фиг. 5

Фиг. 5

Последният аргумент се въвежда в кавички!

 

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

За да изчислим трудовия стаж е необходимо да намерим колко дни, месеци и години има между две дати.

Фиг. 6

Фиг. 6

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

За да изчислим дните между двете дати (колона D) използваме Datedif, като за последен аргумент използваме „md“. Така като резултат имаме разликата между дните, изключвайки месеци и години.

За броя на месеците (колона E) ще използваме „ym”:

Фиг. 8

Фиг. 8

Така получаваме само разликата в месеците, все едно те са в една година.

За годините (колона F) стойността на последния аргумент ще бъде „y“:

Фиг. 9

Фиг. 9

Тези три отделни функции биха могли да се обединят в една.

За тази цел се използва текстовата функция Concatenate. (Вижте тази статия).

 

Datedif & Concatenate

Тази функция ще „слепи“ стойностите за години месеци и дни заедно с текста към тях както е показано на фиг. 10.

Фиг. 10

Фиг. 10

 

За първи аргумент на Concatenate поставяме този Datedif, които изчислява разликата в годините. Вторият аргумент ще е думата „години“ в кавички.

Фиг. 11

Фиг. 11

 

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

Без интервали преди и след думата:

Фиг. 12

Фиг. 12

 

С интервали преди и след думата:

Фиг. 13

Фиг. 13

 

За трети аргумент на Concatenate се поставя Datedif, с която изчислява разликата между двете дати в месеци, изключвайки дни и години „ym”. И така за всички останали аргументи.

 

Примери

Фиг. 14

Фиг. 14

 

Възможни грешки:

#NUM! Error – Ако Date1 е по-късна дата от Date2;

Ако последния аргумент не е коректно зададен;

#VALUE Error – Ако Date1 или Date2 не са разпознати от Excel като дата.

Какво трябва запомним?

DATEDIF намира разликата между две дати в дни, месеци и години. Може да се използва за изчисление на трудов стаж, точна възраст и продължителност на проекти.

Не може да се намери в списъка с функции на Excel, за това трябва да я записваме внимателно. Състои се от 3 аргумента: дата1, дата2 (която задължително е по-късна от дата1) и интервал. Последният аргумент (интервал) работи със строго определени стойности, описани в Таблица 1.

 

 

Прочетете още:

Още публикации по темата: