Як використовувати функції дати в Excel 2016

Excel 2016 містить ряд вбудованих функцій дати, які можна використовувати у своїх електронних таблицях. Коли ви встановлюєте та активуєте надбудову Analysis ToolPak, у вас є доступ до ряду додаткових функцій Date — багато з яких спеціально розроблені для роботи зі звичайним п’ятиденним робочим тижнем з понеділка по п’ятницю (за винятком, звичайно, вашої дорогоцінної вихідні дні з розрахунків).

СЬОГОДНІ

Найпростішою функцією дати має бути СЬОГОДНІ. Ця функція не приймає аргументів і завжди вводиться таким чином:

=СЬОГОДНІ()

Коли ви вводите функцію СЕГОДНЯ в клітинку, клацнувши її в розкривному списку командної кнопки «Дата й час» на вкладці «Формули» стрічки, або ввівши її, Excel повертає поточну дату, використовуючи такий формат дати:

15.09.2016

Майте на увазі, що дата, вставлена ​​в комірку з функцією СЬОГОДНІ, не є статичною. Щоразу, коли ви відкриваєте аркуш, який містить цю функцію, Excel перераховує функцію та оновлює її вміст до поточної дати. Це означає, що ви зазвичай не використовуєте СЕГОДНЯ для введення поточної дати, коли ви робите це для історичних цілей (наприклад, рахунка-фактури) і ніколи не хочете, щоб вона змінювалася.

Якщо ви використовуєте СЬОГОДНІ, а потім хочете зробити поточну дату статичною в електронній таблиці, вам потрібно перетворити функцію на її серійний номер. Ви можете зробити це для окремих клітинок: спочатку виділіть клітинку, натисніть F2, щоб активувати режим редагування, натисніть F9, щоб замінити =СЬОГОДНІ() на сьогоднішній серійний номер на рядку формул, і натисніть кнопку Enter, щоб вставити цей серійний номер у клітинка.

Ви можете виконати це перетворення для діапазону комірок, виділивши діапазон, скопіювавши його в буфер обміну, натиснувши кнопку Копіювати на вкладці Головна стрічки (або натиснувши Ctrl+C), а потім негайно вставивши обчислені значення в те саме діапазон, вибравши параметр «Вставити значення» зі спадного меню командної кнопки «Вставити» (або натиснувши Alt+HVV).

DATE і DATEVALUE

Функція ДАТА у спадному меню командної кнопки «Дата й час» повертає порядковий номер дати для дати, визначеної аргументом року, місяця та дня . Ця функція використовує такий синтаксис:

DATE (рік, місяць, день)

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

Використання функції ДАТА для об’єднання окремої інформації про дату в один запис.

Функція DATEVALUE у спадному меню кнопки «Дата й час» на вкладці «Формули» повертає порядковий номер дати для дати, введеної в електронну таблицю, у вигляді тексту, щоб ви могли використовувати його для обчислення дати. Ця функція приймає один аргумент:

DATEVALUE ( текст_дати )

Припустимо, наприклад, що ви зробили такий текстовий запис у клітинці B12:

'21.05.2016

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

=ДАТАЗНАЧЕННЯ(B12)

Потім Excel повертає серійний номер дати, 42511, у клітинку C12, яку можна перетворити на більш зрозумілу дату, відформатувавши її в одному з форматів дати Excel (Ctrl+1).

Щоб відсортувати та відфільтрувати функції DATE і DATEVALUE, необхідно перетворити їх у обчислені порядкові номери дати. Щоб конвертувати ці функції окремо, виберіть клітинку, натисніть F2, щоб активувати режим редагування, а потім натисніть F9, щоб замінити функцію серійним номером обчисленої дати; нарешті, натисніть кнопку Enter на рядку формул, щоб вставити цей серійний номер у клітинку. Щоб здійснити це перетворення в діапазоні комірок, виділіть діапазон, скопіюйте його в буфер обміну, натиснувши Ctrl+C, а потім негайно вставте обчислені серійні номери в той самий діапазон, вибравши параметр Вставити значення зі спадної кнопки командної кнопки «Вставити». вниз (або натисніть Alt+HVV).

ДЕНЬ, БУДНІ, МІСЯЦЬ і РІК

Функції DAY, WEEKDAY, MONTH і YEAR Date у спадному меню кнопки «Дата й час» повертають лише частини серійного номера дати, які ви вказуєте як їх аргумент:

• DAY( порядковий_номер ), щоб повернути день місяця в даті (у вигляді числа від 1 до 31).

• WEEKDAY( серійний_номер ,[ тип_повернення ]), щоб повернути день тижня (у вигляді числа від 1 до 7 або від 0 до 6). Необов'язковий аргумент return_type є числом від 1 до 3; 1 (або без аргументу return_type ) визначає перший тип, де 1 дорівнює неділі, а 7 дорівнює суботі; 2 визначає другий тип, де 1 дорівнює понеділку, а 7 — неділі; і 3 визначає третій тип, де 0 дорівнює понеділку, а 6 — неділі.

• MONTH( serial_number ), щоб повернути номер місяця в порядковому номері дати (від 1 до 12).

• YEAR( serial_number ), щоб повернути номер року (як ціле число від 1900 до 9999) у серійному номері дати.

Наприклад, якщо ви введете таку функцію DAY у клітинку таким чином:

ДЕНЬ(ДАТА(16,4,15))

Excel повертає в цю клітинку значення 15. Якщо замість цього ви використовуєте функцію WEEKDAY наступним чином:

БУДНІ(ДАТА(16,4,15))

Excel повертає значення 7, яке представляє суботу (з використанням першого типу return_type, де неділя дорівнює 1, а субота — 7), оскільки необов’язковий аргумент return_type не вказано. Якщо ви використовуєте функцію MONTH на цю дату, як показано нижче:

МІСЯЦЬ(ДАТА(16,4,15))

Excel повертає 4 до клітинки. Однак якщо ви використовуєте функцію РІК на цю дату, як показано нижче:

РІК(ДАТА(16,4,15))

Excel повертає в комірку 1916 (замість 2016).

Це означає , що якщо ви хочете , щоб ввести в рік в 21 - му столітті , як рік аргумент функції ДАТИ, вам необхідно ввести всі чотири цифри дати, як в наступному:

ДАТА(2016,4,15)

Зверніть увагу, що ви можете використовувати функцію YEAR для обчислення різниці в роках між двома датами. Наприклад, якщо клітинка B6 містить 23.07.1978, а клітинка C6 містить 23.07.2016, ви можете ввести таку формулу, використовуючи функцію YEAR, щоб визначити різницю в роках:

=РІК(C6)-РІК(B6)

Потім Excel повертає 38 до комірки, що містить цю формулу.

Не використовуйте ці функції для дат, введених як текстові записи. Завжди використовуйте функцію DATEVALUE для перетворення цих текстових дат, а потім використовуйте функції DAY, WEEKDAY, MONTH або YEAR для серійних номерів, які повертає функція DATEVALUE, щоб забезпечити точні результати.

ДНІ360

Функція DAYS360 у спадному меню кнопки «Дата й час» повертає кількість днів між двома датами на основі 360-денного року (тобто року, у якому є 12 рівних місяців по 30 днів у кожному). Функція DAYS360 приймає такі аргументи:

DAYS360(дата_початку,дата_кінця,[метод])

Аргументи start_date і end_date є серійними номерами дати або посиланнями на клітинки, які містять такі серійні номери. Додатковим аргументом методу є TRUE або FALSE, де FALSE вказує на використання методу обчислення США, а TRUE вказує на використання європейського методу обчислення:

• Метод США (NASD) (FALSE або аргумент методу пропущено): у цьому методі, якщо дата початку дорівнює 31-му числу місяця, вона стає рівною 30-му числа того ж місяця; якщо кінцевою датою є 31-е число місяця, а датою початку раніше 30-го числа місяця, датою закінчення стає 1-е число наступного місяця; в іншому випадку кінцева дата стає рівною 30-му числу того ж місяця.

• Європейський метод (TRUE): у цьому методі дати початку та закінчення, які відбуваються 31 числа місяця, стають дорівнювати 30 числа того ж місяця.