Функції управління датами

Excel пропонує не так багато функцій, призначених для роботи з датою. Ці функції представлені в списку, що розкривається Формули → Бібліотека функцій → Дата і час.

У таблиці наведено функції, пов'язані з використанням дати, які доступні в Excel.

Відображення поточної дати

Функція, наведена нижче, відображає в осередку поточну дату (враховуйте, що якщо встановлено текстовий або загальний формат комірки, буде відображено порядкове число сьогоднішньої дати).

= СЕГОДНЯ ()

Можна відобразити не тільки поточну дату, а й об'єднати її з будь-яким текстом. Наприклад, наступна формула буде виводити на екран такий текст: Сьогодні субота, 9 Квітень, 2010 року.

= "Сегодня" & ТЕКСТ (СЬОГОДНІ (); "дддд, д ММММ, рррр")

Пам'ятайте, що результат функції СЬОГОДНІ змінюється кожен раз при розрахунку робочого листа. Наприклад, якщо ввести в комірку робочого аркуша будь-яку з розглянутих формул, вони будуть відображати поточну дату. Але відкрийте цей же робочий лист на наступний день, і ви побачите, що формули відображають зовсім іншу поточну дату.

Щоб ввести в клітинку штамп дати (значення дати, яка не буде змінюватися кожен наступний день), натисніть клавіші Ctrl + Shift +; (Крапка з комою)>. Поточна дата буде введена в комірку в текстовому форматі без використання формули, тому вона залишиться незмінною.

Відображення довільних дат

Дату можна додати в клітинку, безпосередньо ввівши її в будь-якому форматі, підтримуваному в додатку Excel. Дату також можна створити; для цього скористайтеся функцією ДАТА. Ця функція використовує три аргументи: рік, місяць і день. Наприклад, наведена нижче формула повертає дату, що складається з року, взятого з осередку А1, місяці, взятого з осередку В1, і дня - з осередку С1.

= ДАТА (А1; В1; С1)

Примітка

Зверніть увагу, що функція ДАТА приймає також неприпустимі аргументи і призводить результат у відповідність очікуваному. Наприклад, наступна формула використовує число 13 як аргумент місяці і повертає 1 Січень, 2011 року. Аргумент місяці автоматично перетворюється в перший місяць наступного року.

= ДАТА (2010; 13; 1)

Досить часто в функції ДАТА в якості аргументів використовуються інші функції. Наприклад, у наведеній нижче формулі функції РІК і СЬОГОДНІ, використовувані як аргументи, повертають День Незалежності США (4 Липня) поточного року.

= ДАТА (РІК (СЬОГОДНІ ()); 7; 4)

Функція ДАТАЗНАЧ перетворює текстовий рядок, що представляє собою дату, в порядкове число дати. Наступна формула повертає значення порядкового числа дати 22 Августа 2010 року - 40412:

= ДАТАЗНАЧ ( "22.8.2010")

Для того щоб відобразити результат цієї формули у вигляді дати, досить застосувати до осередку формат дати.

попередження

Будьте особливо уважні при використанні функції ДАТАЗНАЧ. Текстовий рядок, яка може бути використана як дата в одній країні, абсолютно не підходить для введення дати в іншій країні. Попередній приклад працює прекрасно в тому випадку, якщо система налаштована для форматів дати Росії або України. Однак при використанні інших регіональних форматів дати (зокрема, форматів США) формула може повертати помилку, оскільки додаток Excel буде шукати восьмий день двадцять другого місяця.

Створення послідовності дат

В окремих випадках може виникнути необхідність вставити в робочий лист цілий ряд послідовних дат. Наприклад, при щотижневому відстеження продажів потрібно ввести декілька послідовних дат з інтервалом в сім днів. Ці дати будуть служити основою для систематизації комерційної інформації.

Найефективніший спосіб введення ряду послідовних дат не вимагає ніяких формул. Просто скористайтеся інструментом, вбудованим в Excel, - автозаповненням. Для цього введіть у комірку першу дату, а потім, утримуючи праву кнопку миші, перетягніть в необхідному напрямку маркер автозаповнення (маленький чорний квадрат в правому нижньому кутку контуру виділення). Відпустіть праву кнопку миші і виберіть в контекстному меню команду Заповнити по днях.

Функція автозаповнення вельми гнучка. Наприклад, можна створити послідовність дат, розділених десятьма днями. Для цього введіть дві перші дати в два осередки, виділіть ці осередки, перетягніть правою кнопкою миші маркер автозаповнення і виберіть в контекстному меню команду Заповнити по днях.

Використання формул для створення послідовності дат все ж має свої переваги. У цьому випадку досить вказати першу дату, після чого інші автоматично приводяться їй у відповідність. Спочатку введіть у комірку початкову дату, а потім додайте формули, скопіювавши їх вниз по стовпчику, щоб отримати дати, наступні за вихідної.

У представленому далі прикладі ми введемо першу дату ряду в осередок А1, а формулу - в клітинку А2. Скопіюйте формулу в комірці вниз по стовпчику стільки раз, скільки це необхідно.

Дана формула дозволяє створити ряд послідовних дат, які йдуть одна за одною з інтервалом в сім днів.

= А1 + 7

Щоб створити ряд послідовних дат, віддалених один від одного на місяць, скористайтеся такою формулою:

= ДАТА (РІК (А1); МІСЯЦЬ (А1) +1; ДЕНЬ (А1))

Формула, наведена нижче, повертає дати, наступні одна за одною з інтервалом в рік.

= ДАТА (РІК (А1) +1; МІСЯЦЬ (А1); ДЕНЬ (А1))

Щоб створити ряд, який складається з дат, що відносяться тільки до робочих днях (тобто виключаючи суботи та неділі), скористайтеся наведеної нижче формулою. Майте на увазі: вона передбачає, що дата, введена в комірку А1, - робочий день.

= ЕСЛИ (ДЕНЬНЕД (А1) = 6; Al + 3; А1 + 1)

Перетворення рядка в дату

Дати, що імпортуються як текстові рядки, теж можна перетворити в дату. Припустимо, в результаті імпорту з іншої програми отримана наведена нижче текстовий рядок, який в дійсності представляє собою дату 21 серпня 2010 року. Цей текст складається з чотирьох знаків, що відносяться до року, двох знаків місяці і двох знаків дня.

20100821

Щоб перетворити цю рядок в фактичну дату, скористайтеся формулою, наведеною нижче. Ця формула передбачає, що вихідні дані занесені в осередок А1.

= ДАТА (ЛЕВСИМВ (А1; 4); ПСТР (А1; 5; 2); ПРАВСИМВ (А1; 2))

Представлена формула використовує текстові функції ЛЕВСИМВ, ПСТР і ПРАВСИМВ для вилучення із заданої рядки певних чисел. Після цього вона застосовує їх як аргументи функції ДАТА.

Обчислення кількості днів між двома датами

Найпоширеніший тип обчислень, пов'язаний з датами, - це визначення кількості днів між двома заданими датами. Припустимо, що на одному з листів робочої книги обчислюється відсоток, одержуваний за строковим вкладом. Розмір отриманого відсотка залежить від кількості днів, на яку відкрито рахунок. Якщо робочий аркуш містить дату відкриття і дату закриття рахунку, то можна легко визначити термін у днях, на який був відкритий рахунок.

Оскільки дати на робочому аркуші, як правило, зберігаються у вигляді послідовних значень, то для обчислення кількості днів можна використовувати просте віднімання. Припустимо, що осередки А1 і В1 містять деякі дати. Наступна формула повертає кількість днів між цими датами:

= А1-В1

Найімовірніше, Excel автоматично отформатирует результуючу клітинку як дату, а не як числове значення. Вам буде потрібно вручну змінити формат цього осередку на числовий. Якщо осередок В1 містить більш пізню дату, ніж осередок А1, то результат, що повертається формулою, буде негативним.

Примітка

Якщо наведена вище формула відображає некоректне значення, переконайтеся, що осередки A1 і В1 дійсно містять дати, а не текстову інформацію, схожу на дати.

У деяких випадках обчислення різниці між двома датами вимагатиме значних зусиль. Щоб продемонструвати це, проведемо аналогію з "телеграфними стовпами". Наприклад, якщо потрібно визначити кількість телеграфних стовпів, то завдання їх підрахунку можна вирішити двома способами: безпосередньо порахувавши кількість стовпів або визначивши кількість прольотів між ними. При цьому майте на увазі, що кількість стовпів завжди на один більше, ніж прольотів.

Щоб розглянути цю ситуацію стосовно до деякого діапазону дат, як приклад наведемо кампанію по просуванню на ринок деякого товару. Припустимо, що початкова дата кампанії по просуванню товару - 1 лютого, а завершальна дата - 9 лютого. Просто віднімаючи одну цифру з іншої, ми отримаємо відповідь: вісім днів. Але в дійсності термін просування товару - дев'ять днів. У цьому випадку, щоб отримати правильну відповідь, необхідно порахувати дні "включно", а не визначати кількість днів між датами. Нижче наведена формула, що дозволяє обчислити тривалість кампанії по просуванню товару (припустимо, що у нас є осередки, відповідні цим назвам).

= ПоследнійДень-НачальнийДень + 1

Обчислення кількості робочих днів між двома датами

Excel вміє не тільки обчислювати кількість днів між двома датами. У разі необхідності можна виключати з числа цих днів вихідні та святкові дні. Наприклад, обчислимо, скільки робочих днів випадає на листопад місяць. Для цього необхідно виключити з розрахунку суботи, неділі та святкові дні (якщо такі є), скориставшись функцією ЧІСТРАБДНІ.

Примітка

Назва функції ЧІСТРАБДНІ часто вводить в оману. Вона не має ніякого відношення до чистоти або рабству, а, натомість, повертає кількість робочих днів між двома заданими датами.

Отже, функція ЧІСТРАБДНІ обчислює різницю між двома датами, виключаючи вихідні дні (суботи та неділі). Крім того, як додаткова умова можна, визначити діапазон комірок, що містить дати, на які припадають святкові дні. Ці дати також можуть бути виключені при розрахунку. Природно, Excel не має ніякої можливості самостійно визначати святкові дні, тому ви повинні забезпечити програму цією інформацією, вказавши відповідний діапазон комірок.

На малюнку показаний робочий лист, на якому обчислюється кількість робочих днів між певними датами. В діапазоні А2: А8 міститься перелік дат, що припадають на святкові дні. Формули, які введені в стовпці С, обчислюють кількість робочих днів, розташованих між датами в шпальтах А і В. Наприклад, формула в комірці С11 матиме такий вигляд:

= ЧІСТРАБДНІ (A11; В11; А2: А8)

Формула повертає значення 3. Це означає, що семиденний період, що починається з 1 січня, містить три робочих дня. Іншими словами, при розрахунку виключаються два святкових і два вихідних дня. Осередок С12 містить формулу, яка обчислює загальну кількість робочих днів у першому півріччі 2010 року.

новинка

В Excel 2010 додана вдосконалена версія функції ЧІСТРАБДНІ, яка називається ЧІСТРАБДНІ.МЕЖД. Вона корисна, коли потрібно вважати вихідними днями не суботу та неділю, а інші дні тижня.

Обчислення дати по графіку робочих днів

Функція РАБДЕНЬ є прямою протилежністю функції ЧІСТРАБДНІ. Наприклад, при запуску в роботу певного проекту, на виконання якого потрібно десять днів, функція допоможе обчислити дату закінчення проекту.

У наведеній нижче формулі за допомогою функції РАБДЕНЬ визначається дата, наступна через десять робочих днів після 8 січня 2010 року. Робочими днями вважаються будні дні тижня (з понеділка по п'ятницю).

= РАБДЕНЬ ( "08.01.2010"; 10)

Формула повертає значення 20 січня 2010 года (між 8 та 20 січня випадає два вихідних дня).

попередження

Майте на увазі, що результат, що повертається цією формулою, повністю залежить від регіональних форматів дати. В інших країнах наведена вище жорстко закодована дата може інтерпретуватися як 1 серпня 2010 року. Щоб уникнути цього, використовуйте наступну формулу:

= РАБДЕНЬ (ДАТА (2010; 1; 8); 10)

Цікаво, що другий аргумент функції РАБДЕНЬ може бути негативним. Як і у випадку з функцією ЧІСТРАБДНІ, функція РАБДЕНЬ може використовувати третій не обов'язковий аргумент, який визначає діапазон комірок з датами, що відносяться до святкових днів і днів відпустки.

Обчислення кількості років між двома датами

Наступна формула обчислює кількість років між двома датами. В даному випадку мається на увазі, що осередки А1 і В1 містять дати.

= ГОД (А1) -рік (В1)

Для вилучення року з кожної заданої дати в цій формулі використовується функція РІК, після чого один рік віднімається з іншого. Майте на увазі наступне: якщо В1 містить ближчий до поточної дати рік, ніж дата в осередку А1, результат буде негативним.

Слід звернути увагу, що ця функція не обчислює частину року. Наприклад, якщо комірка А1 містить дату 31.12.2010, а осередок В1 - дату 01.01.2011, то формула поверне різницю в один рік, незважаючи на те, що ці дати відрізняються лише на один день.

Для обчислення кількості років між двома датами можна також використовувати функцію ДОЛЯГОДА. Ця функція повертає кількість років, включаючи неповні, наприклад:

= ДОЛЯГОДА (А1; В1; 1)

Так як функцію ДОЛЯГОДА часто використовують в фінансовихдодатках, в ній існує необов'язковий третій аргумент, який представляє "базис" року. Значення 1 цього аргументу вказує на поточний рік.

Обчислення віку людини

Вік людини - це кількість повних років, які людина вже прожив. Формула, наведена для обчислення кількості років між двома датами в попередньому розділі, не зможе коректно визначити це значення. В цьому випадку потрібно використовувати дві інші формули.

Наступна формула повертає вік людини, дата народження якого міститься в осередку А1. Ця формула використовує функцію ДОЛЯГОДА.

= ЦІЛЕ (ДОЛЯГОДА (СЬОГОДНІ (); A1; 1))

Нижче наведена формула, яка застосовує для обчислення віку функцію РАЗНДАТ.

= РАЗНДАТ (А1; СЬОГОДНІ (); "у")

Де знайти функцію РАЗНДАТ

В цьому розділі неодноразово згадувалася функція РАЗНДАТ. Зверніть увагу, що її немає в числі доступних в діалоговому вікні Майстер функцій, також вона не міститься в списку автозавершення формули. Тому для того, щоб використовувати цю функцію, її необхідно ввести вручну.

Функція РАЗНДАТ була відома ще з часів Lotus 1-2-3, і, очевидно, з метою забезпечення сумісності Excel підтримує її. З певних причин Microsoft намагається зберегти цю функцію в секреті. Цікаво, що в версіях програми нижче Excel 2000 функція РАЗНДАТ навіть не згадувалася. З довідки Excel 2002 було видалені всі посилання на неї, хоча сама функція в цій версії додатка все ще доступна.

Проте, функція РАЗНДАТ дуже зручна. Вона дозволяє обчислити кількість днів, місяців і років, які перебувають між двома датами. У ній використовується три аргументи: начальная_дата, конечная_дата і певний код_едініци_ізмеренія, за допомогою якого задається одиниця часу. Код в функції завжди полягає в лапки. Нижче наведена таблиця, в якій представлені допустимі коди для третього аргументу.

Майте на увазі, що аргумент начальная_дата повинен знаходитися лівіше, ніж аргумент конечная_дата, в іншому випадку функція повертає помилку. Крім того, в русифікованої Excel коди одиниць вимірювання повинні вводитися англійськими літерами.

Обчислення дня року

Отже, 1 січня - це перший день року, а 31 грудня - останній. Як визначити, який за рахунком день припадає на довільну дату року? Наступна формула повертає порядкове число дня року для дати, що міститься в осередку А1:

= А1-ДАТА (РІК (A1); 1; 0)

Якщо задати аргумент день рівним нулю, функція ДАТА інтерпретує його як останній день попереднього місяця. Можна задавати негативні значення, вони інтерпретуються аналогічно.

Наведена нижче формула повертає день року поточної дати:

= СЕГОДНЯ () - ДАТА (РІК (СЬОГОДНІ ()); 1; 0)

Наступна формула повертає кількість днів у році, які залишилися після настання дати, заданої в осередку А1:

= ДАТА (РІК (А1); 12; 31) -А1

Зверніть увагу, що при введенні будь-який з цих формул Excel застосовує до осередку формат дати, і щоб побачити отриманий результат, необхідно застосувати до осередку числовий формат.

Наступну формулу рекомендується використовувати для того, щоб перетворити заданий порядкове число дня року (наприклад, дев'яностий день) в певну дату і рік, які відповідають цьому дню. У цій формулі передбачається, що вміст комірки А1 - це рік, а вміст комірки В1 - це день року.

= ДАТА (A1; 1; В1)

Обчислення дня тижня

Для обчислення дня тижня часто Використовують функцію ДЕНЬНЕД. Вона приймає в якості аргументу дату і повертає ціле число, відповідне дню тижня, в діапазоні між 1 і 7. Слід зазначити, що в даному випадку відлік днів починається з неділі, як це прийнято в англомовних програмних продуктах. Наприклад, наступна формула повертає значення 5, оскільки перший день 2010 випадає на п'ятницю:

= ДЕНЬНЕД (ДАТА (2010; 1; 1))

Функція ДЕНЬНЕД може приймати і другий, необов'язковий аргумент, який визначає систему нумерації днів тижня для виведення результату. Якщо в якості другого аргументу введено значення 2, то функція повертає значення 1 для понеділка, 2 - для вівторка і так далі Якщо ж в якості другого аргументу ввести значення 3, то функція поверне 0 для понеділка, 1 - для вівторка і так далі

Для того, щоб обчислити день тижня дати в осередку, можете використовувати власний числовий формат. Виберіть команду Головна → Число → Числовий формат → Інші числові формати. В поле Тип діалогового вікна Формат ячеек введіть дддд і клацніть на кнопці ОК. Введіть в клітинку дату (наприклад, 22.07.2010). В результаті в комірці буде відображено четвер - день тижня введеної дати.

Обчислення дати минулого неділі

Хочете дізнатися, на яку дату доводилося минулої неділі? Скористайтеся формулою, наведеною нижче. Але майте на увазі, якщо поточна дата випадає на неділю, то формула повертає поточну дату.

= СЕГОДНЯ () - ОСТАТ (СЬОГОДНІ () - 1; 7)

Для того щоб знайти дату іншого дня, а не неділі, змініть аргумент 1 у формулі на будь-який інший в діапазоні між 2 (знайти понедельник) і 7 (субота).

Обчислення дня тижня, наступного після заданої дати

Наведена нижче формула повертає дату заданого дня тижня, наступного за певною датою. Наприклад, цю формулу можна використовувати, щоб обчислити дату першого понеділка після 1 червня 2010 року. Припустимо, що осередок А1 містить дату, а осередок А2 - число між 1 і 7 (1 відповідає неділі, 2 - понеділка і т.д.).

= А1 + А2-ДЕНЬНЕД (А1) + (А2Еслі осередок А1 містить дату 1 червня 2010 року, а осередки А2 - значення 2 (понеділок), то формула поверне дату 4 червня 2010 року. Це буде перший понеділок, наступний після П'ятниця 1 червня 2010 року.

Обчислення дати n-го дня тижня в заданому місяці

Іноді виникає потреба обчислити дату, яка припадає на заданий день тижня. Наведемо приклад. Кожну другу п'ятницю місяця в компанії - день виплати заробітної плати. Як визначити дату виплат в кожному місяці? Скористайтеся для цього формулою, наведеною нижче.

= ДАТА (А1; А2; 1) + А3-ДЕНЬНЕД (ДАТА (А1; А2; 1)) +
(А4 (А3> = ДЕНЬНЕД (ДАТА (А1; А2; 1)))) * 7

Для цієї формули прийняті наступні умови:

• осередок А1 містить значення року;
• осередок А2 містить значення місяця;
• осередок А3 містить значення дня (1 для неділі, 2 - для понеділка і т.д.);
• осередок А4 містить номер входження (наприклад, 2 для вибору другого входження дня тижня, заданого в осередку А3).

Наприклад, якщо використовувати цю формулу для визначення дати другий п'ятниці в червні 2010 року, формула поверне дату 8 червня 2010 року.

Примітка

Якщо значення, що міститься в осередку А4, не належить до діапазону днів зазначеного місяця, то формула поверне дату, яка відноситься до наступного місяця. Наприклад, якщо задати визначення дати шостий п'ятниці червня 2010 года (а такої дати не існує), то формула поверне дату першої п'ятниці липня.

Обчислення кількості заданих днів тижня в місяці

Формула, наведена нижче, дозволяє обчислити кількість певних днів тижня заданого місяця. Припустимо, осередок А1 містить дату, а осередок А2 - порядкове число дня тижня (значення 1 відповідає неділі, 2 - понеділка і т.д.). Майте на увазі, що це формула масиву, тому, щоб ввести її, за допомогою клавіш Ctrl + Shift + Enter>.

{= СУММ ((ДЕНЬНЕД (ДАТА (РІК (А1); МІСЯЦЬ (А1); СТРОКА (ДВССИЛ ( "1:"
& ДЕНЬ (ДАТА (РІК (А1); МІСЯЦЬ (А1) +1; 0)))))) = В1) * 1)}

Якщо осередок А1 містить дату 8 січня 2010 року, а осередок В1 - значення 3, тобто вівторок, формула повертає значення 4. Це означає, що січень 2010 містить чотири вівторка.

Наведена вище формула масиву обчислює рік і місяць, використовуючи функції РІК і МІСЯЦЬ. Однак її можна трохи спростити. Для цього введіть рік і місяць заданої дати в окремі осередки. Для наведеної нижче формули, яка теж є формулою масиву, прийняті умови, що осередок А1 містить значення року, осередок А2 - місяці і осередок В1 - значення дня тижня.

{= СУММ ((ДЕНЬНЕД (ДАТА (A1; А2; СТРОКА (ДВССИЛ ( "1:"
& ДЕНЬ (ДАТА (А1; А2 + 1; 0)))))) = В1) * 1)}

На малюнку показаний результат використання цієї формули на робочому аркуші. В цьому випадку використовуються і абсолютні, і змішані посилання на клітинки, тому можна скопіювати цю формулу. Наприклад, формула в комірці СЗ виглядає наступним чином:

{= СУММ ((ДЕНЬНЕД (ДАТА ($ В $ 2; $ А3; СТРОКА (ДВССИЛ ( "1:"
& ДЕНЬ (ДАТА ($ В $ 2; $ А3 + 1; 0)))))) = С $ 1) * 1)}

Підсумкові формули на цьому аркуші використовують функцію СУММ. Вони обчислюють загальну кількість днів у місяці (стовпець J) і кількість кожного дня тижня в році (рядок 15).

Відображення дати в текстовому вигляді

Іноді може знадобитися відобразити дату у вигляді, який не передбачений ні в одному вбудованому форматі дат. Наприклад, за допомогою наведеної нижче формули дату 16.3.2010 можна відобразити, як 16 березня 2010 г. Передбачається, що стандартна запис дати знаходиться в осередку А1.

= ДЕНЬ (А1) & "" & ТЕКСТ (A1; "ММММ") & "а" & РІК (А1) & "м"

Примітка

Результат цієї формули має текстовий формат, а не формат дати.

Звичайно, таке перетворення може бути корисним, тільки якщо передбачити вибір суфікса за допомогою ряду функцій ЯКЩО (наприклад, для квітня треба застосувати суфікс я і видалити ь).

Обчислення святкових дат

Обчислення дат, що випадають на специфічні святкові дні, вимагає використання спеціальних методів. Безумовно, це не стосується таких дат, як Новий рік чи День Незалежності США, оскільки вони визначені здавна. Для них можна просто використовувати функцію ДАТА, про яку йшлося раніше. Наступна формула відображає дату Нового року. Новий рік завжди доводиться на 1 січня; потрібний рік введіть у комірку А1.

= ДАТА (А1; 1; 1)

Насправді проблем не виникає тільки зі святами, постійно відзначаються по строго певних дат. А як бути зі святковими днями, дату яких визначає часовий період? До таких свят в США, наприклад, відноситься День праці, який відзначається в перший понеділок вересня.

На малюнку показана робоча книга Excel, в якій обчислюються дати кількох святкових днів. Всі формули містять посилання на осередок А1 - рік, для якого обчислюються дати. Оскільки такі свята, як Новий рік і День Незалежності, відзначаються в строго певні дні року, для обчислення їх дат використовується тільки функція ДАТА.

Новий рік

Новий рік відзначається першого січня.

= ДАТА (А1; 1; 1)

День Мартіна Лютера Кінга

Цей святковий день припадає на третій понеділок січня. Далі ми дізнаємося, на яку дату випаде цей день в 2010 році, визначеному в осередку А1.

= ДАТА (А1; 1; 1) + ЯКЩО (2 День президентів

День президентів святкується в третій понеділок лютого. Наступна формула обчислює дату цього дня в зазначеному році (осередок А1):

= ДАТА (А1; 2; 1) + ЯКЩО (2 День пам'яті полеглих

День пам'яті полеглих у громадянській війні в США 1861-1865 рр., В іспано-американської та інших війнах відзначається в США в останній понеділок травня. Дата, на яку він доведеться в зазначеному році (осередок А1), визначається за допомогою наступної формули:

= ДАТА (А1; 6; 1) + ЯКЩО (2Обратіте увагу, що дана формула обчислює перший понеділок червня, а потім, щоб визначити попередній понеділок травня, з отриманого результату віднімає 7 днів.

День Незалежності США

Це свято з року в рік відзначається строго 4 липня.

= ДАТА (А1; 7; 4)

День праці

День праці - це перший понеділок вересня. Формула, наведена нижче, визначає його дату в зазначеному році (осередок А1).

= ДАТА (А1; 9; 1) + ЯКЩО (2 День ветеранів

Це свято випадає на 11 листопада.

= ДАТА (A1; 11; 11)

день Колумба

Це свято випадає на другий понеділок жовтня. Для обчислення його дати в зазначеному році (осередок А1) використовується наступна формула:

= ДАТА (А1; 10; 1) + ЯКЩО (2 День Подяки

День Подяки святкується в четвертий четвер листопада. Наступна формула обчислює дату цього дня в зазначеному році (осередок А1):

= ДАТА (А1; 11; 1) + ЯКЩО (5 Різдво

Католицьке Різдво святкується 25 грудня.

= ДАТА (A1; 12; 25)

Обчислення дати останнього дня місяця

Для того щоб обчислити дату, відповідну останнього дня місяця, можна скористатися функцією ДАТА. При цьому число місяців необхідно збільшити на одиницю, а для дня використовувати значення 0. Іншими словами, в розрахунок приймається умова, що нульовий день місяця - це останній день попереднього місяця.

Наведена нижче формула повертає дату, яка відповідає останньому дню місяця. Дана формула передбачає, що вихідна дата міститься в осередку А1.

= ДАТА (РІК (А1); МІСЯЦЬ (А1) +1; 0)

Цю формулу можна модифікувати, після чого вона дозволить обчислювати кількість днів, що містяться в певних місцях. Наступна формула повертає ціле числове значення, яке представляє собою кількість днів у місяці, заданому в осередку А1:

= ДЕНЬ (ДАТА (РІК (А1); МІСЯЦЬ (А1) +1; 0))

Перевірка високосного року

Безумовно, для того щоб з'ясувати, чи є високосним певний рік, можна написати формулу, яка встановить дату шестидесятого дня року: 29 лютого або 1 березня. Але набагато ефективніше скористатися формулою ДАТА, яка на додаток відкоригує результат в тому випадку, якщо введений неправильний аргумент (наприклад, коли для дня введено значення 29, в той час як в лютому цього року всього 28 днів).

Дана формула повертає значення ІСТИНА, якщо дата, яка перебуває в осередку А1, відноситься до високосному році. В іншому випадку формула повертає значення БРЕХНЯ.

= ЕСЛИ (МІСЯЦЬ (ДАТА (РІК (А1); 2; 29)) = 2; ІСТИНА; ЛОЖЬ)

попередження

Пам'ятайте, що для 1900 року результат істина, що повертається цією функцією, буде неправильним.

Обчислення квартального періоду

У фінансових звітах часто корисно відображати інформацію поквартально. Формула, наведена нижче, повертає числове значення в діапазоні від 1 до 4, яке відповідає календарному кварталу для дати, що міститься в осередку А1.

= ОКРУГЛВВЕРХ (МІСЯЦЬ (А1) / 3; 0)

Дана формула ділить кількість місяців на три, а потім округлює отриманий результат.

Відображення року римськими цифрами

Любителям старих кінофільмів напевно сподобається ця особливість Excel. Наступна формула відображає 1945 рік римськими цифрами і повертає MCMXLV:

= Римське (1945)

Функція Римське повертає текстовий рядок, тому її результат не можна використовувати для виконання обчислень. Крім того, Excel не має зворотної функції, яка дозволила б перетворити римські цифри в арабські.

на початок

Корисне