Існує багато способів перетворити дати, відформатовані як текст, у фактичні дати в Excel, і цей підручник спрямований на вивчення їх усіх.
Коли ви імпортуєте дані в Excel, вони можуть мати багато різних форм, які Excel не розпізнає. Іноді, коли дати імпортуються із зовнішнього джерела, вони надходять у форматі тексту.
Також важливо знати, що Excel застосовує формати дати на основі налаштувань регіону вашої системи. Отже, коли ви імпортуєте в Excel дані, які надійшли з іншої країни, Excel може не розпізнати їх і зберегти як текстові записи.
Якщо у вас виникла така проблема, ви можете використовувати багато способів для перетворення тексту в дату в Excel, і ми розглянемо їх усі в цьому підручнику.
Методи перетворення тексту в дату
Якщо ваш аркуш містить дати, відформатовані у вигляді тексту, замість фактичних дат, їх неможливо використовувати в жодних обчисленнях. Отже, вам потрібно перетворити їх назад на фактичні дати.
Якщо ви бачите, що ваші дати вирівняні ліворуч, це означає, що вони відформатовані як текст, оскільки тексти за замовчуванням вирівнюються за лівим краєм. А номер і дати завжди вирівнюються праворуч.
Існує кілька різних способів конвертувати текст у дату в Excel, це:
- Параметр перевірки помилок
- Функція Excel Text To Columns
- Знайти та замінити
- Вставити Спеціальний інструмент
- Формула та функції Excel
Перетворіть текст на дату за допомогою параметра перевірки помилок
Excel має вбудовану функцію перевірки помилок, яка виявляє деякі очевидні помилки у ваших даних. Якщо він виявить помилку, він покаже вам маленький зелений трикутник (індикатор помилки) у верхньому лівому куті клітинки, де є помилка. Якщо вибрати цю клітинку, з’явиться попереджувальний знак із жовтим знаком оклику. Коли ви наведете курсор на цей знак, Excel повідомить вас про можливу проблему з цією коміркою.
Наприклад, коли ви вводите рік у двозначному форматі в дату, Excel приймає цю дату як текст і зберігає її як текст. І якщо ви виберете цю клітинку, з’явиться знак оклику з попередженням: «Ця клітинка містить рядок дати, представлений лише 2-ма цифрами року».
Якщо ваші клітинки показують цей індикатор помилки, клацніть знак оклику, і він відобразить кілька параметрів для перетворення дат, відформатованих як текст, у фактичні дати. Excel покаже вам варіанти перетворення його на 19XX або 20XX (19XX для 1915, 20XX для 2015). Виберіть відповідний варіант.
Потім текст буде перетворено у відповідний формат дати.
Як увімкнути параметр перевірки помилок в Excel
Зазвичай параметр «Перевірка помилок» увімкнено в Excel за замовчуванням. Якщо функція перевірки помилок не працює для вас, вам потрібно ввімкнути перевірку помилок у Excel.
Для цього перейдіть на вкладку «Файл» і виберіть «Параметри» на лівій панелі.
У вікні параметрів Excel натисніть «Формули» на лівій панелі, а на правій панелі увімкніть «Увімкнути перевірку фонових помилок» у розділі «Перевірка помилок». І в розділі Правил перевірки помилок поставте прапорець «Клітинки, що містять роки, представлені двома цифрами».
Перетворіть текст на дату за допомогою функції Excel Text to Column
Text to Column — це чудова функція в Excel, яка дозволяє розділити дані на кілька стовпців, а також це потужний інструмент для перетворення текстових значень у значення дати. Цей метод розпізнає кілька різних форматів даних і перетворює їх у відповідний формат дати.
Перетворення простих текстових рядків у дати
Скажімо, ваші дати відформатовано у текстових рядках, наприклад:
Ви можете скористатися майстром «Текст у стовпці», щоб швидко переформатувати їх усі назад до дат.
Спочатку виберіть діапазон текстових записів, які потрібно перетворити на дати. Потім перейдіть на вкладку «Дані» на стрічці та виберіть параметр «Текст у стовпці» в групі «Інструменти для даних».
З’явиться майстер тексту в стовпці. На кроці 1 майстра «Текст у стовпець» виберіть параметр «З роздільниками» у розділі «Вихідний тип даних» і натисніть «Далі».
На кроці 2 зніміть усі прапорці «Розділники» та натисніть «Далі».
На останньому кроці майстра виберіть «Дата» у розділі «Формат даних стовпця», виберіть формат дати зі спадного списку поруч «Дати» та натисніть кнопку «Готово». У нашому випадку ми перетворюємо текстові дати, представлені як «01 02 1995» (день місяць рік), тому ми вибираємо «DMY» зі спадного списку «Дата:».
Тепер Excel перетворює ваші текстові дати на фактичні дати та відображає їх вирівняно по правому краю в клітинках.
Примітка: Перш ніж почати використовувати функцію «Текст у стовпець», переконайтеся, що всі ваші текстові рядки мають ідентичний формат, інакше тексти не будуть перетворені. Наприклад, якщо деякі з ваших текстових дат відформатовано у форматі місяць/день/рік (MDY), а інші – день/місяць/рік (DMY), і коли ви виберете «DMY» на кроці 3, ви отримаєте неправильні результати. Як це показано на малюнку нижче.
Перетворення рядка складного тексту в дату
Функція «Текст у стовпці» стане в нагоді, якщо ви хочете перетворити складні текстові рядки в дати. Він дозволяє використовувати роздільники, щоб визначити, де ваші дані мають бути розділені та відображені в 2 або більше стовпцях. І об’єднайте розділені частини дат у цілу дату за допомогою функції ДАТА.
Наприклад, якщо ваші дати відображаються в текстових рядках із кількох частин, наприклад:
Середа, 01 лютого 2020 року
01 лютого 2020 року о 16.10
Ви можете використовувати майстер «Текст у стовпець», щоб розділити інформацію про день, дату та час, розділені комою, і відобразити їх у кількох стовпцях.
Спочатку виберіть усі текстові рядки, які потрібно перетворити на дати. Натисніть кнопку «Текст у стовпці» на вкладці «Дані». На кроці 1 майстра «Текст у стовпець» виберіть параметр «З роздільниками» у розділі «Вихідний тип даних» і натисніть «Далі».
На кроці 2 майстра виберіть роздільники, які містять ваші текстові рядки, і натисніть «Далі». Наш приклад текстових рядків, розділених комою та пробілом – «Понеділок, 1 лютого 2015 року, 13:00». Нам слід вибрати «Кома» та «Пробіли» як роздільники, щоб розділити текстові рядки на кілька стовпців.
На останньому кроці виберіть формат «Загальний» для всіх стовпців у розділі Попередній перегляд даних. Укажіть, куди потрібно вставити стовпці в полі «Призначення», якщо цього не зробити, вихідні дані буде замінено. Якщо ви хочете проігнорувати деяку частину вихідних даних, натисніть на неї в розділі «Попередній перегляд даних» і виберіть параметр «Не імпортувати стовпець (пропустити)». Потім натисніть «Готово».
Тепер частини дат (дні тижня, місяць, рік, час) розбиті на колонки B, C, D, E, F і G.
Потім об’єднайте частини дати разом за допомогою формули ДАТА, щоб отримати всю дату.
Синтаксис функції ДАТА Excel:
=ДАТА(рік, місяць, день)У нашому прикладі частини місяця, дня та року розміщені в стовпцях C, D та E відповідно.
Функція DATE розпізнає лише числа, а не текст. Оскільки всі наші значення місяця в стовпці C є текстовими рядками, нам потрібно перетворити їх у числа. Для цього потрібно за допомогою функції MONTH змінити назву місяця на номер місяця.
Щоб перетворити назву місяця на число місяця, скористайтеся цією функцією MONTH всередині функції DATE:
=МІСЯЦІ(1&C1)Функція MONTH додає 1 до клітинки C2, яка містить назву місяця, щоб перетворити назву місяця на відповідний номер місяця.
Це функція DATE, яку нам потрібно використовувати, щоб об’єднати частини дати з різних стовпців:
=ДАТА(E1,МІСЯЦЬ(1&C1),D1)
Тепер скористайтеся маркером заповнення в нижньому куті клітинки формули та застосуйте формулу до стовпця.
Перетворіть текст на дату за допомогою методу пошуку та заміни
Цей метод використовує роздільники, щоб змінити формат тексту на дати. Якщо день, місяць і рік у ваших датах розділені якимось роздільником, відмінним від тире (-) або косої риски (/), Excel не розпізнає їх як дати і збереже їх як текст.
Щоб вирішити цю проблему, скористайтеся функцією «Знайти та замінити». Змінюючи нестандартні роздільники точки (.) на косі риски (/) або тире (-), Excel автоматично змінить значення на дати.
Спочатку виберіть усі текстові дати, які потрібно перетворити на дати. На вкладці «Головна» натисніть кнопку «Знайти та вибрати» у крайньому правому куті стрічки та виберіть «Замінити». Як варіант, натисніть Ctrl+H щоб відкрити діалогове вікно Знайти та замінити.
У діалоговому вікні «Знайти та замінити» введіть роздільник, який містить ваш текст (у нашому випадку крапку (.) у полі «Знайти», і косу риску (/) або тире (-) у полі «Замінити на»). . Натисніть кнопку «Замінити все», щоб замінити роздільники, і натисніть «Закрити», щоб закрити вікно.
Тепер Excel розпізнає, що ваші текстові рядки тепер є датами, і автоматично форматує їх як дати. Ваші дати будуть вирівняні, як показано нижче.
Перетворіть текст на дату за допомогою спеціального інструмента Paste
Ще один швидкий і простий спосіб перетворити текстові рядки в дати – це додати 0 до текстового рядка за допомогою спеціальної опції вставки. Додавання нуля до значення перетворює текст у порядковий номер дати, який ви можете відформатувати як дату.
Спочатку виділіть порожню клітинку та скопіюйте її (виділіть її та натисніть Ctrl + C копіювати).
Потім виділіть клітинки, що містять текстові дати, які потрібно перетворити, клацніть правою кнопкою миші та виберіть опцію «Спеціальна вставка».
У діалоговому вікні «Спеціальна вставка» виберіть «Усі» в розділі «Вставити», виберіть «Додати» в розділі «Операція» та натисніть «ОК».
Ви також можете виконувати інші арифметичні операції відняти/перемножити/поділити значення в клітинці призначення на вставлене значення (наприклад, помножити клітинки на 1 або поділити на 1 або відняти нуль).
Коли ви вибираєте «Додати» в операції, він додає «нуль» до всіх вибраних текстових дат, оскільки додавання «0» не змінює значення, ви отримаєте серійний номер для кожної дати. Тепер все, що вам потрібно зробити, це змінити формат комірок.
Виберіть серійні номери та на вкладці «Дім» клацніть спадний список «Формат номера» в групі «Номер». Виберіть опцію «Коротка дата» зі спадного меню.
Як ви можете бачити зараз, числа відформатовані як дати та вирівняні праворуч.
Перетворення тексту на дату за допомогою формул
Для перетворення тексту на дату в основному використовуються дві функції: DATEVALUE і VALUE.
Використання функції Excel DATEVALUE
Функція Excel DATEVALUE є одним із найпростіших способів перетворити дату, представлену у вигляді тексту, у порядковий номер дати.
Синтаксис функції DATEVALUE:
=DATEVALUE(текст_дати)Аргумент: текст_дати визначає текстовий рядок, який потрібно приховати, або посилання на клітинку, що містить текстові дати.
Формула:
=ДАТАЗНАЧЕННЯ(A1)На наступному малюнку показано, як функція DATEVALUE обробляє кілька різних форматів дати, які зберігаються як текст.
Ви отримали серійні номери дати, тепер вам потрібно застосувати формат дати до цих чисел. Для цього виділіть клітинки з серійними номерами, потім перейдіть на вкладку «Дім» і виберіть «Коротка дата» зі спадного списку «Формат чисел».
Тепер у вас є відформатовані дати в стовпці C.
Навіть якщо у вашій текстовій даті (A8) немає частини року, DATEVALUE використовуватиме поточний рік із годинника вашого комп’ютера.
Функція DATEVALUE перетворює лише текстові значення, які виглядають як дата. Він не може перетворити текст, який нагадує число, на дату, а також не може змінити числове значення на дату, для цього вам знадобиться функція VALUE в Excel.
Використання функції Excel VALUE
Функція Excel VALUE здатна перетворити будь-який текстовий рядок, який нагадує дату чи число, у числове значення, тому це дуже корисно, коли справа доходить до перетворення будь-якого числа, а не лише дат.
Функція VALUE:
=ЗНАЧЕННЯ(текст)текст– текстовий рядок, який ми хочемо перетворити, або посилання на комірку, що містить текстовий рядок.
Приклад формули для перетворення текстової дати:
=ЗНАЧЕННЯ(A1)Наведена нижче формула VALUE може змінити будь-які текстові рядки, які виглядають як дата, на число, як показано нижче.
Однак функція VALUE не підтримує всі типи значень дати. Наприклад, якщо в датах використовуються десяткові знаки (A11), він поверне #ЗНАЧЕННЯ! помилка.
Коли ви отримаєте серійний номер для дати, вам потрібно буде відформатувати комірку з серійним номером дати, щоб вона виглядала як дата, як ми зробили для функції DATEVALUE. Для цього виберіть серійні номери та виберіть опцію «Дата» зі спадного меню «Формат номера» на вкладці «Головна».
Ось і все, це 5 різних способів конвертувати дати, відформатовані як текст, у дати в Excel.