Функція Excel визначення дня тижня. Як складати та віднімати дати, дні, тижні, місяці та роки в Excel. Визначення дня тижня за допомогою функції деннед

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тут розглянемо, як перетворити дату в день тижня на Excel. Цей прийом стане в нагоді при складанні календаря, бланка, табеля, тощо. Ми маємо таблицю. У стовпці В стоять дати. У стовпці А хочемо написати дні тижня (Пн, Вт, т.д.). Встановлюємо у стовпці А дати.
Тепер у стовпці А замінимо числа дат на день тижня. Виділяємо осередки стовпця А (А3: А10). Заходимо у формат осередків, вибираємо функцію "Всі формати".
У рядку "Тип" діалогового вікна "Формат осередків" пишемо такий формат - ДДД.
Натискаємо "ОК". Вийшло так.
Цей спосіб можна застосувати у табелі обліку робочого часу. У рядку з датами встановлюємо формат дати - лише число (Д). Тоді, в осередку писатимемо всю дату, наприклад - 12.01.2016. А в осередку з цим форматом буде видно лише число - 12. Достатньо встановити такий формат у першому осередку, написати в ньому дату. Потім, протягнути цей осередок по стовпцю або рядку, і, одночасно, скопіюється формат дати і осередки заповняться послідовними датами.
Які формати дат можна встановити, дивіться у статті "Як створити формат користувача в Excel" .
Бланк табеля обліку робочого часу можна переглянути та завантажити у статті "Табель обліку робочого часу в Excel".
Як визначити за датою номер кварталу, початок та кінець кварталу, читайте у статті "Номер кварталу в Excel".
Як визначити дату за днем тижня або, наприклад, дату третьої неділі березня 2016 р., дивіться у статті "Дізнатися дату в Excel по дню тижня, тощо" . У такий спосіб можна дізнатися дату і майбутню і минулу.
Як скласти дати, додати до дати дні, місяці, роки, як порахувати стаж за датами, вік, т.д., дивіться у статті "Дата в Excel. Формула".
Excel можна швидко пересунути стовпець, таблицю, рядок. Як це зробити, дивіться у статті

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

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

Спосіб 1: Застосування форматування

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

В полі «Тип»вікна форматування замість значення «ДДДД»можна також ввести вираз:

У цьому випадку на аркуші буде відображено скорочену назву дня тижня.

Спосіб 2: використання функції тексту

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

Більше того, якщо в комірці змінити значення дати, відповідно автоматично зміниться і день тижня. Таким чином, змінюючи дату можна дізнаватися на який день тижня вона припадатиме.

Спосіб 3: застосування функції ДЕНЬНЕД

Існує ще один оператор, який може виводити день тижня за заданою датою. Це функція ДЕНЬНЕД. Щоправда, вона виводить не назву дня тижня, а його номер. При цьому користувач може встановлювати з якого дня (з неділі або з понеділка) нумерація буде відраховуватись.

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

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

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

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

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

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

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

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

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

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

Ви повинні пам'ятати, що є як мінімум кілька походів до нумерації номера тижня.

Перший підхід використовується в Росії. Відповідно до стандарту ГОСТ ИСО 8601-2001 першим робочим тижнемроку вважається та, яку випадає перший четвер року.
Другий підхід полягає в тому, що тиждень, на який припадає 1 січня і вважається першим тижнем року.

Ще раз нагадаємо вам, що в Росії використовується перший із описаних вище походів.

ЦІЛО((A1 -ДАТА(РІК(A1 -ДЕНЬНЕД(A1 -1)+4);1;3)+ДЕНЬНЕД(ДАТА(РІК(A1 -ДЕНЬНЕД(A1 -1)+4);1;3))+) 5)/7)

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

INT ((A1 -DATE (YEAR (A1 -WEEKDAY (A1 -1)+4),1,3)+WEEKDAY (DATE (YEAR (A1 -WEEKDAY (A1 -1)+4),1,3)))+ 5)/7)

Якщо вам все ж таки необхідний другий підхід, то для визначення номера тижня використовуйте формулу:

1+ЦЕЛИЙ((A1 -ДАТА(РІК(A1 +4-ДЕНЬНЕД(A1 +6)));1;5)+ДЕНЬНЕД(ДАТА(РІК(A1 +4-ДЕНЬНЕД(A1 +6));1;3)) ))/7)

формула для англомовної версії:

1+INT ((A1 -DATE (YEAR (A1 +4-WEEKDAY (A1 +6)),1,5))+WEEKDAY (DATE (YEAR (A1 +4-WEEKDAY (A1 +6)),1,3)) ))/7)

У всіх вищезгаданих формулах A1 змінюємо на дату або посилання на дату.

Все набагато простіше, якщо ви використовуєте Excel 2007або 2010 . Починаючи з цих версій, з'явилася спеціальна функція, що дозволяють визначати номер тижня за датою. (Примітка: цю функцію можна увімкнути і в Excel 2003, для цього необхідно підключити надбудову Пакет аналізучерез меню Сервіс-Надбудови, але в даному випадку, якщо документ відкриватиме людина у якої ця настройка не включена, то він замість номера тижня побачить помилку #ЗНАЧ!, тому застосовувати її в Excel 2003 ми не будемо)

Функція називається НЕМИЖНІ (в англійській версії WEEKNUM). Синтаксис дуже простий.

НОМТИЖНІ(Дата; Тип)

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

Для Росії необхідно використати формулу у наступному форматі.

НОМТИЖНІ(А1 ; 21)

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

Дуже сподіваємося, що наша стаття допомогла Вам у вирішенні Вашої проблеми. Будемо вдячні, якщо Ви натиснете +1 та/або Мені подобаєтьсявнизу цієї статті або поділіться з друзями за допомогою кнопок, розташованих нижче.

сайт - Безмежні можливості Excel