Існує 6 простих способів конвертації тексту у дати.

 

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

 

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

 

Наприклад: 01-Jan-2023 є прийнятним форматом дати. При введенні в комірку вона відображатиметься як дата, але буде збережена як 44927 у серверній частині (44927 — це номер, що відповідає даті 01-01-2023).

 

Натомість 01.Jan.2023 є неприйнятним форматом дати, тому він буде введений як текстовий рядок. Дата не буде збережена як число у серверній частині, тому ми не зможемо використовувати її в обчисленнях.

 

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

 

Тепер розглянемо способи перетворення тексту на дати

 

1. Використання функції DATEVALUE (ДАТАЗНАЧЕНИЕ)

 

Коли ви завантажуєте дані з бази даних або копіюєте з веб сторінки, дати можуть відформатуватися як текст. Якщо дата має прийнятний для Excel формат, її можна легко перетворити на дату за допомогою функції DATEVALUE (ДАТАЗНАЧЕНИЕ)

 

Крок 1. Перетворюємо дати з тексту назад

 

Наприклад, нам треба конвертувати наступні дані:

 

1 Jan 2023

15 March

 

Синтаксис:

 

=DATEVALUE(A2)

 

Скопіюйте формулу в усі клітинки стовпця B:

 

Так ми отримали числове значення, яке відповідає даті у клітинці А2:

 

Опанувати основні та просунуті функції табличного редактора ви зможете на курсі Excel: бізнес-аналіз та прогнозування

 

Крок 2. Тепер нам потрібно відформатувати число:

 

Виберіть клітинки, які містять результати DATEVALUE

 

Крок 3. Перейдіть на вкладку Головна

 

Крок 4. У групі команд Числа оберіть меню форматування

 

Крок 5. Оберіть потрібний формат відображення даних: Коротка дата або Довга дата.

У результаті отримуємо відформатовані дані:

 

2.  Використання функції VALUE (ЗНАЧЕН)

 

Схожа до DATEVALUE функція VALUE (ЗНАЧЕН), що може працювати як з датами, так і з даними.

 

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

 

Візьмемо аналогічний приклад і введемо:

 

=VALUE(A2)

 

Аналогічно перетворюємо числа на дати: переходимо у меню Числа — обираємо опцію Коротка дата/Довга дата.

 

3. Використання математичних операторів (додавання, множення, подвійний мінус, ділення)

 

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

 

Знову скористаємося нашим прикладом:

 

Щоб отримати числове значення дати, оберіть:

 

=A2+0

або

=A2*1

або

=A2/1

або

=--A2

 

У стовпчику В отримаємо числове значення:

Аналогічно до попереднього способу перетворюємо числа на дати через меню Числа — Коротка дата/Довга дата.

 

*Щоб налаштувати додаткові параметри дати (або створити власний формат), скористайтеся гарячими клавішами Control+1 для відкриття вікна Формат клітинок.

 

4. Використання спеціальної вставки

 

Крок 1. Скопіюйте та введіть в окрему клітинку дані, які потрібно перетворити

 

Крок 2. Клацніть правою кнопкою мишки для виклику контекстного меню

 

Крок 3. Оберіть Спеціальна вставка

Крок 4. Оберіть Значення в категорії Вставити

 

Крок 5. У категорії Операція оберіть Додати та натисніть ОК

Ми отримали числовий формат, до якого тепер аналогічно застосуємо форматування чисел у дати:

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

 

5. Перетворення тексту на дату (з форматом, який Excel не розпізнає як дати

 

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

 

Наприклад: 01.Jan.2023, 01.Feb.2023

 

Якщо ви спробуємо застосувати функції DATEVALUE або VALUE, отримаємо помилку значення.

 

Щоб перетворити це текстове значення на дані, необхідно замінити крапку та змінити дату на таку, яку Excel може розпізнати.

 

Тут можемо скористатися двома способами.

 

Спосіб 1. Знайти і замінити

 

Крок 1. Виділіть клітинки, в яких міститься дата

 

Крок 2. Перейдіть на вкладку Головна

 

Крок 3. У групі команд Редагування натисніть Знайти та виділити

 

Крок 4. Натисніть Замінити

 

Крок 5. У полі Знайти введіть . (крапка)

У полі Замінити на введіть – тире

Крок 6. Натисніть Замінити все

Отримаємо результат:

*Для відкриття діалогового вікна Знайти та замінити скористатися гарячими клавішами Control+H

 

Спосіб 2. Використання тексту у стовпчиках

 

Крок 1. Оберіть клітинки, в яких містяться дати

 

Крок 2. Перейдіть на вкладку Дані

 

Крок 3. У групі команд Інструменти даних оберіть Текст по стовпчиках

Крок 4. Виберіть Роздільники

Крок 5. Зніміть галочки з усіх роздільників та натисніть Далі

Крок 6. Оберіть параметр Дата та вкажіть комірку призначення, після чого натисніть Готово

Ось так виглядатиме результат:

 

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

 

6. Використання функції SUBSTITUTE

 

Функція заміни (у даному випадку — крапок на тире) SUBSTITUTE у комбінації з функцією DATEVALUE перетворить дати на числове значення. Далі ми можемо їх відформатувати, як робили раніше:

 

=DATEVALUE(SUBSTITUTE(A2,".","-"))

 

Якщо у вас замість крапки встановлено інший роздільник (наприклад, пробіл), використовуйте його у формулі замість крапки.

 

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

 

 

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"

 

 

Дізнайтеся більше про повну програму "EXCEL ADVANCED: поглиблені інструменти"