Аналіз бізнес-даних часто вимагає роботи зі значеннями дати в Excel, щоб відповісти на такі запитання, як «скільки грошей ми заробили сьогодні» або «як це порівнюється з тим же днем минулого тижня?» І це може бути важко, якщо Excel не розпізнає значення як дати.
На жаль, це не є незвичайним, особливо коли кілька користувачів вводять цю інформацію, копіюють та вставляють з інших систем та імпортують із баз даних.
У цій статті ми опишемо чотири різні сценарії та рішення для перетворення тексту на значення дати.
Дати, які містять крапку/крапку
Ймовірно, одна з найпоширеніших помилок початківців, вводячи дати в Excel, — це робити це за допомогою символу крапки, щоб розділити день, місяць і рік.
Excel не розпізнає це як значення дати і збереже його як текст. Однак цю проблему можна вирішити за допомогою інструмента «Знайти та замінити». Замінивши крапки косими рисками (/), Excel автоматично визначить значення як дати.
Виберіть стовпці, для яких потрібно виконати пошук і заміну.
Натисніть «Додому» > «Знайти та вибрати» > «Замінити» або натисніть Ctrl+H.
У вікні «Знайти та замінити» введіть крапку (.) у полі «Знайти що» та косу риску (/) у полі «Замінити на». Потім натисніть «Замінити все».
Усі крапки перетворюються на косі риски, і Excel розпізнає новий формат як дату.
Якщо дані вашої електронної таблиці регулярно змінюються, і вам потрібно автоматизоване рішення для цього сценарію, ви можете скористатися функція ЗАМІНИТИ.
=VALUE(SUBSTITUTE(A2,".","https://www.wdzwdz.com/"))
Функція SUBSTITUTE є текстовою функцією, тому не може перетворити її на дату самостійно. Функція VALUE перетворить текстове значення в числове значення.
Результати наведені нижче. Значення має бути відформатовано як дата.
Ви можете зробити це за допомогою списку «Формат чисел» на вкладці «Головна».
Типовим є приклад роздільника крапки. Але ви можете використовувати ту саму техніку, щоб замінити або замінити будь-який символ роздільника.
Перетворення формату yyyymmdd
Якщо ви отримуєте дати у форматі, показаному нижче, це вимагає іншого підходу.
Цей формат є досить стандартним у технології, оскільки він усуває будь-яку двозначність щодо того, як різні країни зберігають свої значення дат. Однак спочатку Excel цього не зрозуміє.
Для швидкого вирішення вручну ви можете використовувати текст у стовпці.
Виберіть діапазон значень, які потрібно перетворити, а потім натисніть Дані > Текст у стовпці.
З’явиться майстер тексту в стовпці. Натисніть «Далі» на перших двох кроках, щоб перейти до третього кроку, як показано на зображенні нижче. Виберіть «Дата», а потім виберіть зі списку формат дати, який використовується в клітинках. У цьому прикладі ми маємо справу з форматом YMD.
Якщо вам потрібне рішення формули, ви можете використовувати функцію 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, де також зберігається значення часу.
Потім результати можна відформатувати як дату й час або як дату, щоб приховати значення часу (але не видаляти).