Аналіз бізнес-даних часто вимагає роботи зі значеннями дат в Excel, щоб відповісти на такі питання, як «скільки грошей ми заробили сьогодні» або «як це співвідноситься з тим же днем на минулому тижні?». І це може бути складно, якщо Excel не визнати значення як дати.
На жаль, у цьому немає нічого незвичайного, особливо коли кілька користувачів друкують цю інформацію, копіюють і вставляють з інших систем і імпортують з баз даних.
У цій статті ми опишемо чотири різні сценарії і рішення для перетворення тексту на значення дати.
Дати, що містять повний стоп/період
Ймовірно, одна з найбільш поширених помилок, які роблять новачки при введенні дат в Excel, полягає у використанні символу повної зупинки для поділу дня, місяця і року.
Excel не розпізнає це як значення дати і збереже його як текст. Однак цю проблему можна вирішити за допомогою інструмента «Знайти і замінити». Замінивши точки з косою рисою (/), Excel автоматично визначить значення як дати.
Виберіть стовпчики, за якими ви бажаєте шукати і замінити.
Клацніть "Будинок" > "Знайти" і виберіть "Замінити" або натисніть "Ctrl + H.
У вікні «Знайти та замінити» введіть точку (.) У полі «Знайти що» і косу межу (/) у полі «Замінити на». Потім натисніть «Замінити все».
Всі точки зупину перетворюються на косові риси, і Excel розпізнає новий формат як дату.
Якщо дані вашої електронної таблиці регулярно змінюються, і вам потрібно автоматичне рішення для цього сценарію, ви можете використовувати функцію ЗАМІНА.
= ЗНАЧЕННЯ (ПІДСТАВИТИ (А2, «» «», «/»))
Функція SUBSTITUTE є текстовою функцією, тому не може самостійно перетворити її на дату. Функція VALUE перетворює текстове значення на числове значення.
Результати показані нижче. Значення має бути відформатовано як дата.
Ви можете зробити це за допомогою списку «Числовий формат» на вкладці «Головна».
Приклад розділювача повної зупинки є типовим. Але ви можете використовувати ту ж техніку для заміни або заміни будь-якого символу-роздільника.
Перетворення формату ггггмдд
Якщо ви отримуєте дати у форматі нижче, це потребуватиме іншого підходу.
Цей формат є досить стандартним у технології, оскільки він усуває будь-яку невизначеність щодо того, як різні країни зберігають свої значення дат. Тим не менш, Excel не буде спочатку розуміти це.
Для швидкого ручного вирішення ви можете використовувати текст у стовпчику.
Виберіть діапазон значень, які необхідно перетворити, та клацніть «Дані» > «Текст на стовпчики».
Відкриється майстер «Текст у стовпці». Натисніть «Далі» на перших двох кроках, щоб перейти до третього кроку, як показано на малюнку нижче. Виберіть «Дата» і виберіть формат дати у комірках зі списку. У цьому прикладі ми маємо справу з форматом YMD.
Якщо вам потрібне рішення на основі формул, ви можете використовувати функцію Date для побудови дати.
Це буде використовуватися разом з текстовими функціями Left, Mid і Right для вилучення трьох частин дати (день, місяць, рік) з вмісту комірки.
Наведена нижче формула показує цю формулу з використанням наших вибіркових даних.
= ДАТА (ЛІВИЙ (A2,4), MID (A2,5,2), праворуч (A2,2))
Використовуючи будь-який з цих методів, ви можете перетворити будь-яке восьмизначне числове значення. Наприклад, ви можете отримати дату у форматі ddmmyyyy або у форматі mmddyyyy.
Функції DATEVALUE і VALUE
Іноді проблема не пов'язана з символом-роздільником, а має незручну структуру дати просто тому, що вона зберігається у вигляді тексту.
Нижче наведено список дат у різних структурах, але всі вони пізнавані для нас як дата. На жаль, вони були збережені у вигляді тексту і потребують перетворення.
Для цих сценаріїв легко конвертувати, використовуючи різні методи.
У цій статті я хотів згадати дві функції для обробки цих сценаріїв. Це DATEVALUE і VALUE.
Функція DATEVALUE перетворює текст на значення дати (ймовірно, передбачає це), а функція VALUE перетворює текст на значення загального числа. Відмінності між ними мінімальні.
На зображенні вище, одне зі значень також містить інформацію про час. І це буде демонстрацією незначних відмінностей функцій.
Наведена нижче формула DATEVALUE перетворює кожну на значення дати.
= ДАТА ( )
Зауважте, як час було вилучено з результату у рядку 4. Ця формула строго повертає тільки значення дати. Результат все одно потрібно буде відформатувати як дату.
Наступна формула використовує функцію VALUE.
= ЗНАЧЕННЯ (А2)
Ця формула дасть ті самі результати, за винятком рядка 4, де також зберігається значення часу.
Результати можна потім відформатувати як дату і час або як дату, щоб приховати значення часу (але не видалити).
