Установка дня тижня по даті в Microsoft Excel. Визначення за датою дня тижня Визначення дня тижня по даті в excel

День тижня, а саме "Понеділок", "Вівторок", "середовище" і так далі, можна отримати в Excel формулою за датою. Тобто, коли є дата і необхідно, щоб поруч в осередку з'явилося назви дня тижня, можна це зробити формулою. Причому, в Інтернеті "гуляє" кілька формул для цього. Ми не будемо розглядати їх, наведемо лише одну (на нашу думку, найбільш оптимальну), тож

Формула-який день тижня за датою

ТЕКСТ (B8; "ДДДД")

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

ПРОПНАЧ (ТЕКСТ (B8; "ДДДД"))

Інші формули повертають день тижня більш громіздкі, тому тут не розглядаються, але є одне "але". Наведена формула не буде працювати, якщо у якому її файл відкриють в англомовному (або будь-якому іншому, «не російською" Excel)

Ми коротко розглянули чергову формулу в рамках серії статей, які ми назвали "1 формула в день". Якщо Вас цікавить базовий рівень Excel, або Вам необхідний "просунутий",, щоб отримати допомогу, навчання або консультацію. Сайт ExcelSkype - віддалене навчання Excel через Skype.

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

Визначення дня в році

1 січня - це перший день року, а 31 грудня - останній. А як щодо інших днів, що йдуть між ними? Наступна формула повертає день року для дати, що зберігається в осередку A1: \u003d A1-ДАТА (РІК (A1); 1; 0). Наприклад, якщо комірка A1 містить дату 16 лютого 2010 року, формула повертає 47, тому що ця дата є 47-м днем \u200b\u200bв році.

Наступна формула повертає кількість днів, що залишилися в році з моменту певної дати (передбачається, що вона міститься в осередку A1): \u003d ДАТА (РІК (A1); 12; 31).

Визначення дня тижня

Якщо вам необхідно визначити день тижня для дати, функція ДЕНЬНЕД впорається з цим завданням. Функція приймає в якості аргументу дату і повертає ціле число від 1 до 7, відповідне дню тижня. Наступна формула, наприклад, повертає 6, тому що перший день 2010 року припадає на п'ятницю: \u003d ДЕНЬНЕД (ДАТА (2010; 1; 1)).

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

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

Визначення дати останньої неділі

Формула в цьому розділі повертає останній зазначений день. Ви можете використати таку формулу для отримання дати минулого неділі. Якщо поточний день - неділя, то формула повертає поточну дату. результатом буде серійний номер дати (вам потрібно відформатувати осередок для відображення читабельною дати): \u003d СЕГОДНЯ () - ОСТАТ (СЬОГОДНІ () - 1; 7).

Щоб змінити цю формулу для знаходження дати іншого дня, а не неділі, поміняйте 1 на інше число в діапазоні від 2 (понеділок) до 7 (субота).

Визначення дня тижня після дати

Наступна формула повертає зазначений день тижня, який настає після певної дати. Наприклад, ви можете застосовувати цю формулу для визначення дати першої п'ятниці після 4 липня 2010 року. Формула передбачає, що осередок А1 містить дату, а осередок А2 - число від 1 до 7 (1 відповідає неділі, 2 - понеділка і т. Д.): \u003d A1 + A2-ДЕНЬНЕД (A1) + (А2

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

Знаходження n-го певного дня тижня в місяці

Вам може знадобитися формула для знаходження дати певного за рахунком дня тижня. Припустимо, що день виплати зарплати у вашій компанії доводиться на другу п'ятницю кожного місяця і вам потрібно визначити ці
дні виплат для кожного місяця року. Наступна формула виконає необхідний розрахунок:
\u003d ДАТА (А1; А2; 1) + А3-ДЕНЬНЕД (ДАТА (А1; А2; 1)) + (А4 (А3\u003e \u003d ДЕНЬНЕД (ДАТА (А1; А2; 1)))) * 7

Ця формула передбачає, що:

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

При використанні цієї формули для визначення дати другий п'ятниці в червні 2010 року результатом буде 11 червня, 2010 року.

Визначення останнього дня місяця

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

Наступна формула передбачає, що дата зберігається в осередку А1. Формула повертає дату, якої відповідає останній день місяця: \u003d ДАТА (РІК (А1); МІСЯЦЬ (А1) +1; 0).

Ви можете змінювати цю формулу, щоб визначити, скільки днів включає в себе зазначений місяць. Наступна формула повертає ціле число, яке відповідає кількості днів у місяці для дати з осередку А1 (переконайтеся, що ви відформатували осередок як число, а не як дату): \u003d ДЕНЬ (ДАТА (РІК (А1); МІСЯЦЬ (А1) +1; 0))

Визначення кварталу дати

Для фінансових звітів може виявитися корисним подання інформації по кварталах. Наступна формула повертає ціле число від 1 до 4, яке відповідає календарному кварталу для дати в осередку А1: \u003d ОКРУГЛ ВГОРУ (МІСЯЦЬ (A1) / 3; 0). Ця формула ділить номер місяця на 3, а потім округлює результат.

У цьому уроці Ви познайомитеся з різними корисними формулами додавання і віднімання дат в Excel. Наприклад, Ви дізнаєтеся, як відняти від однієї дати іншу, як додати до дати кілька днів, місяців або років, і т.д.

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

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

Як віднімати дати в Excel

Припустимо, що у Вас в осередках A2 і B2 містяться дати, і потрібно відняти одну дату з іншого, щоб дізнатися, скільки між ними днів. Як часто буває в Excel, цей результат можна отримати декількома способами.

Приклад 1. Безпосередньо віднімаємо одну дату з іншого

Думаю, Ви знаєте, що Excel зберігає дати у вигляді цілих чисел, починаючи з 1, що відповідає 1 січня 1900 г. Тому Ви можете просто арифметично відняти одне число з іншого:

Приклад 2. Віднімання дат за допомогою функції РАЗНДАТ

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

РАЗНДАТ (A2; B2; "d")
\u003d DATEDIF (A2, B2, "d")

Наступний малюнок показує, що обидві формули повертають однаковий результат, за винятком ряду 4, де функція РАЗНДАТ (DATEDIF) повертає помилку # ЧИСЛО! (#NUM!). Подивимося, чому це відбувається.

Коли Ви віднімаєте більш пізню дату (6 травня 2015) з більш ранньої (1 травня 2015), операція віднімання повертає негативне число. Однак синтаксис функції РАЗНДАТ (DATEDIF) не дозволяє, щоб початкова дата була більше кінцевої дати і, природно, повертає помилку.

Приклад 3. Віднімаємо дату з поточної дати

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

СЬОГОДНІ () - A2
\u003d TODAY () - A2

РАЗНДАТ (A2; СЬОГОДНІ (); "d")
\u003d DATEDIF (A2, TODAY (), "d")

Як і в попередньому прикладі, формули відмінно працюють, коли поточна дата більше, ніж віднімається. В іншому випадку функція РАЗНДАТ (DATEDIF) повертає помилку.

Приклад 4. Віднімання дат за допомогою функції ДАТА

Якщо Ви віддаєте перевагу вводити дати безпосередньо в формулу, вказуйте їх за допомогою функції ДАТА (DATE), а потім віднімайте одну дату з іншого.

функція ДАТА має наступний синтаксис: ДАТА ( рік; місяць; день) .

Наприклад, наступна формула віднімає 15 травня 2015 року з 20 травня 2015 року та повертає різницю - 5 днів.

ДАТА (2015; 5; 20) -Дата (2015; 5; 15)
\u003d DATE (2015,5,20) -DATE (2015,5,15)

Якщо потрібно підрахувати кількість місяців або років між двома датами, Тоді функція РАЗНДАТ (DATEDIF) - єдине можливе рішення. У продовженні статті Ви знайдете кілька прикладів формул, детально розкривають цю функцію.

Тепер, коли Ви знаєте, як віднімати одну дату з іншого, подивимося, як можна додати або відняти з дати певну кількість днів, місяців або років. Для цього існує кілька функцій Excel. Що саме вибрати залежить від того, які одиниці часу потрібно додати або відняти.

Як додати (відняти) дні до дати в Excel

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

Приклад 1. Додаток днів до дати в Excel

Загальна формула додавання певної кількості днів до дати виглядає так:

= Дата + N днів

Дату можна задати декількома способами:

• Власний кореспондент осередок:
• Зверненням до функції ДАТА (DATE):

  ДАТА (2015; 5; 6) +10
  \u003d DATE (2015,5,6) +10

• Зверненням до іншої функції. Наприклад, щоб додати кілька днів до поточної дати, використовуйте функцію СЬОГОДНІ (TODAY):

  СЬОГОДНІ () + 10
  \u003d TODAY () + 10

Наступний малюнок показує дію цих формул. На момент написання тексту поточною датою було 6 травня 2015 р

Примітка: Результатом виконання цих формул є ціле число, яке представляє дату. Щоб показати його як дату, необхідно вибрати комірку (або осередку) та натиснути Ctrl + 1. Відкриється діалогове вікно формат ячеек (Format Cells). на вкладці число (Number) в списку числових форматів Оберіть Дата (Date) і потім вкажіть потрібний вам формат. більш докладний опис Ви знайдете в статті.

Приклад 2. Віднімання днів з дати в Excel

Щоб відняти певну кількість днів з дати, Вам знову потрібно використовувати звичайну арифметичну операцію. Єдина відмінність від попереднього прикладу - мінус замість плюса

= Дата - N днів

Ось кілька прикладів формул:

A2-10
\u003d ДАТА (2015; 5; 6) -10
\u003d СЕГОДНЯ () - 10

Як додати (відняти) кілька тижнів до дати

Там, де необхідно додати (відняти) кілька тижнів до певної дати, Ви можете скористатися тими ж формулами, що і раніше. Просто потрібно помножити кількість тижнів на 7:

• Додаємо N тижнів до дати в Excel:

  A2 + N тижнів * 7

  Наприклад, щоб додати 3 тижні до дати в осередку А2, Використовуйте наступну формулу:

• Віднімаємо N тижнів з дати в Excel:

  А2 - N тижнів * 7

  Щоб відняти 2 тижні з сьогоднішньої дати, використовуйте цю формулу:

  СЬОГОДНІ () - 2 * 7
  \u003d TODAY () - 2 * 7

Як додати (відняти) кілька місяців до дати в Excel

Щоб додати (або відняти) певну кількість місяців до дати, потрібно використовувати функцію ДАТА (DATE) або ДАТАМЕС (EDATE), як показано нижче.

Приклад 1. Додаємо кілька місяців до дати за допомогою функції ДАТА

Якщо список дат знаходиться, наприклад, в стовпці A, Вкажіть кількість місяців, які Ви хочете додати (позитивне число) або відняти (негативне число) в якийсь осередку, скажімо, в C2.

Введіть в клітинку B2 зазначену нижче формулу, клацніть по виділеному кутку комірки і протягніть його мишкою вниз по стовпчику B до останньої заповненої комірки в стовпці A. Формула з осередку B2 буде скопійована в усі комірки стовпчика B.

ДАТА (РІК (A2); МІСЯЦЬ (A2) + $ C $ 2; ДЕНЬ (A2))
\u003d DATE (YEAR (A2), MONTH (A2) + $ C $ 2, DAY (A2))

Подивимося, що робить ця формула. Логіка формули ясна і очевидна. функція ДАТА ( рік; місяць; день) отримує такі аргументи:

• рік з дати в осередку A2;
• місяць з дати в осередку A2 + Кількість місяців, вказане в осередку C2;
• день з дати в осередку A2;

Все просто! Якщо Ви введете в C2 негативне число, формула відніме місяці, а не додасть.

Природно, ніщо не заважає Вам ввести мінус прямо в формулі, щоб відняти місяці:

ДАТА (РІК (A2); МІСЯЦЬ (A2) - $ C $ 2; ДЕНЬ (A2))
\u003d DATE (YEAR (A2), MONTH (A2) - $ C $ 2, DAY (A2))

І, звичайно, ви можете вказати число додаються або віднімаються місяців безпосередньо у формулі без посилання на осередок. Готові формули будуть виглядати приблизно так:

• додати місяці до дати:

  ДАТА (РІК (A2); МІСЯЦЬ (A2) +2; ДЕНЬ (A2))
  \u003d DATE (YEAR (A2), MONTH (A2) + 2, DAY (A2))

• відняти місяці з дати:

  ДАТА (РІК (A2); МІСЯЦЬ (A2) -2; ДЕНЬ (A2))
  \u003d DATE (YEAR (A2), MONTH (A2) -2, DAY (A2))

Приклад 2. Додаємо або віднімаємо місяці з дати за допомогою функції ДАТАМЕС

В Excel передбачена спеціальна функція, яка повертає дату, віддалену від заданої на певну кількість місяців назад або вперед, - це функція ДАТАМЕС (EDATE). Вона доступна в останніх версіях Excel 2007, 2010, 2013 і нової Excel 2016.

При використанні ДАТАМЕС (EDATE) Ви вказуєте два наступних аргументу:

• початкова дата - дата, від якої відраховується кількість місяців.
• місяці - кількість місяців, які потрібно додати (позитивне число) або відняти (негативне число).

Ці формули дадуть той же самий результат, що і формули з функцією ДАТА (DATE) в попередньому прикладі:

При використанні функції ДАТАМЕС (EDATE) початкову дату і кількість місяців можна вказувати безпосередньо у формулі. Дати можна задавати за допомогою функції ДАТА (DATE) або як результат виконання інших формул. наприклад:

• Ця формула додає 10 місяців до 7 травня 2015 року

  ДАТАМЕС (ДАТА (2015; 5; 7); 10)
  \u003d EDATE (DATE (2015,5,7), 10)

• Ця формула забирає 10 місяців від сьогоднішньої дати

  ДАТАМЕС (СЬОГОДНІ (); - 10)
  \u003d EDATE (TODAY (), - 10)

Примітка: функція ДАТАМЕС (EDATE) повертає просто ціле число. Щоб уявити його як дату, необхідно застосувати до осередку формат дати. Як це зробити, зазначено в статті Як змінити формат дати в Excel.

Як додати (відняти) роки до дати в Excel

Додавання років до дат в Excel здійснюється так само, як додавання місяців. Вам необхідно знову використовувати функцію ДАТА (DATE), але на цей раз потрібно вказати кількість років, які Ви хочете додати:

ДАТА (РІК ( дата) + N років; МІСЯЦЬ ( дата); ДЕНЬ ( дата))
\u003d DATE (YEAR ( дата) + N років, MONTH ( дата), DAY ( дата))

на аркуші Excel, Формули можуть виглядати наступним чином:

• Додаємо 5 років до дати, зазначеної в осередку A2:

  ДАТА (РІК (A2) +5; МІСЯЦЬ (A2); ДЕНЬ (A2))
  \u003d DATE (YEAR (A2) + 5, MONTH (A2), DAY (A2))

• Віднімаємо 5 років з дати, зазначеної в осередку A2:

  ДАТА (РІК (A2) -5; МІСЯЦЬ (A2); ДЕНЬ (A2))
  \u003d DATE (YEAR (A2) -5, MONTH (A2), DAY (A2))

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

Додаток (віднімання) днів, місяців і років до дати

Якщо Ви уважно читали два попередніх прикладу, то, думаю, здогадалися, як додати (або відняти) відразу роки, місяці і дні до дати в одній формулі. Так, за допомогою старої доброї функції ДАТА (DATA)!

• для додавання X років, Y місяців і Z днів:

  ДАТА (РІК ( дата) + X років; МІСЯЦЬ ( дата) + Y місяців; ДЕНЬ ( дата) + Z днів)
  \u003d DATE (YEAR ( дата) + X років, MONTH ( дата) + Y місяців, DAY ( дата) + Z днів)

• для вирахування X років, Y місяців і Z днів:

  ДАТА (РІК ( дата) - X років; МІСЯЦЬ ( дата) - Y місяців; ДЕНЬ ( дата) - Z днів)
  \u003d DATE (YEAR ( дата) - X років, MONTH ( дата) - Y місяців, DAY ( дата) - Z днів)

Наприклад, наступна формула додає 2 роки і 3 місяці, і віднімає 15 днів з дати в осередку A2:

ДАТА (РІК (A2) +2; МІСЯЦЬ (A2) +3; ДЕНЬ (A2) -15)
\u003d DATE (YEAR (A2) + 2, MONTH (A2) + 3, DAY (A2) -15)

Щодо нашого одну з датами, формула приймає наступний вигляд:

ДАТА (РІК (A2) + $ C $ 2; МІСЯЦЬ (A2) + $ D $ 2; ДЕНЬ (A2) + $ E $ 2)
\u003d DATE (YEAR (A2) + $ C $ 2, MONTH (A2) + $ D $ 2, DAY (A2) + $ E $ 2)

Як додавати і віднімати час у Excel

В Microsoft Excel додавати і віднімати час можна за допомогою функції ЧАС (TIME). Вона дозволяє звертатися з одиницями часу (годинами, хвилинами і секундами) так само, як і з роками, місяцями і днями в функції ДАТА (DATE).

• додати час в Excel:

  A2 + ЧАС ( годинник; хвилини; секунди)
  \u003d A2 + TIME ( годинник, хвилини, секунди)

• відняти час в Excel:

  A2 - ЧАС ( годинник; хвилини; секунди)
  \u003d A2 - TIME ( годинник, хвилини, секунди)

  де A2 - це осередок з часом, яке потрібно змінити.

Наприклад, щоб додати 2 години 30 хвилин і 15 секунд до часу в осередку A2 треба використовувати наступну формулу:

A2 + ЧАС (2; 30; 15)
\u003d A2 + TIME (2,30,15)

A2 + ЧАС (2; 30; -15)
\u003d A2 + TIME (2,30, -15)

Також Ви можете ввести потрібні значення в осередку аркуша і посилатися на них у формулі:

A2 + ЧАС ($ C $ 2; $ D $ 2; $ E $ 2)
\u003d A2 + TIME ($ C $ 2, $ D $ 2, $ E $ 2)