Аналіз корпоративних даних часто передбачає маніпуляції з датами в Excel для отримання відповідей на питання, наприклад, “який наш дохід за сьогодні?” або “якщо порівняти з минулим тижнем?”. Це може бути складно, якщо Excel не ідентифікує значення як дати.
На жаль, це не рідкість, особливо коли декілька користувачів вводять інформацію, копіюють-вставляють з інших систем або імпортують з баз даних.
У цій статті ми розглянемо чотири різноманітні сценарії та відповідні рішення для трансформації текстових даних у значення дати.
Дати з крапками як роздільниками
Однією з найпоширеніших помилок серед новачків при введенні дат в Excel є використання крапки для розділення дня, місяця та року.
Excel не розпізнає це як формат дати і збереже як текст. Однак, цю проблему легко вирішити за допомогою інструменту “Знайти та замінити”. Заміна крапок на косі риски (/) дозволить Excel автоматично розпізнати значення як дати.
Виділіть стовпці, в яких потрібно виконати заміну.
Перейдіть до “Головна” > “Знайти і виділити” > “Замінити” або скористайтеся комбінацією клавіш Ctrl+H.
У діалоговому вікні “Знайти та замінити” введіть крапку (.) в поле “Знайти” і косу риску (/) в поле “Замінити на”. Натисніть “Замінити все”.
Усі крапки будуть замінені на косі риски, і Excel розпізнає новий формат як дату.
Якщо дані у вашій таблиці регулярно оновлюються, і вам потрібне автоматизоване рішення, ви можете скористатися функцією ЗАМІНИТИ.
=VALUE(SUBSTITUTE(A2,".","/"))
Функція SUBSTITUTE призначена для роботи з текстом і не може сама по собі перетворити текст на дату. Тому функція VALUE перетворює текстове значення в числове.
Результати показано нижче. Значення потребують форматування як дати.
Форматування можна виконати через список “Формат чисел” на вкладці “Головна”.
Приклад з роздільником-крапкою є типовим, але цей метод можна застосовувати для заміни будь-якого символу роздільника.
Трансформація формату yyyymmdd
Якщо ви отримуєте дати в такому форматі, як показано нижче, вам знадобиться інший підхід.
Цей формат є досить поширеним у технологіях, оскільки виключає неоднозначність щодо представлення дат у різних країнах. Однак Excel спочатку не розпізнає його.
Для швидкого ручного перетворення можна скористатися функцією “Текст за стовпцями”.
Виділіть діапазон значень, які потрібно змінити, потім перейдіть до “Дані” > “Текст за стовпцями”.
З’явиться вікно майстра “Текст за стовпцями”. Натискайте “Далі” на перших двох кроках, поки не досягнете третього. Виберіть “Дата”, потім оберіть у списку формат дати, що використовується у комірках. У цьому прикладі ми маємо формат РМД (рік-місяць-день).
Для створення формульного рішення можна скористатися функцією DATE для конструювання дати.
Це використовується в поєднанні з текстовими функціями “ЛІВ”, “СЕРЕД” і “ПРАВ”, щоб витягти складові дати (день, місяць, рік) з вмісту комірки.
Формула нижче ілюструє це з нашими зразковими даними.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Застосовуючи будь-який з цих методів, ви зможете перетворити будь-яке восьмизначне числове значення. Наприклад, ви можете отримати дату у форматі ддммрррр або ммдддррр.
Функції DATEVALUE та VALUE
Інколи проблема полягає не в символі-роздільнику, а в незручній структурі дати, через те що вона збережена як текст.
Нижче представлені дати в різноманітних структурах, але всі вони для нас легко розпізнавані як дати. На жаль, вони збережені як текст і потребують конвертації.
У таких випадках легко виконати конвертацію за допомогою різноманітних підходів.
У цій статті я хочу згадати дві функції для обробки цих сценаріїв: DATEVALUE і VALUE.
Функція DATEVALUE перетворює текст у значення дати, а функція VALUE – у загальне числове значення. Відмінності між ними невеликі.
На зображенні вище одне зі значень також містить інформацію про час, що стане демонстрацією відмінностей між цими функціями.
Формула DATEVALUE, наведена нижче, перетворить кожне зі значень у значення дати.
=DATEVALUE(A2)
Зверніть увагу, що час було виключено з результату в рядку 4. Ця формула повертає лише значення дати. Результат потрібно буде відформатувати як дату.
У наступній формулі використано функцію VALUE.
=VALUE(A2)
Ця формула дасть ті ж самі результати, за винятком рядка 4, де також зберігається значення часу.
Результати можна форматувати як “Дата й час” або “Дата”, щоб приховати (але не видалити) значення часу.