Як перетворити текст у значення дати в Microsoft Excel

| | 0 Comments| 9:22 AM
Categories:

Аналіз бізнес-даних часто вимагає роботи зі значеннями дати в Excel, щоб відповісти на такі запитання, як «скільки грошей ми заробили сьогодні» або «як це порівнюється з тим же днем ​​минулого тижня?» І це може бути важко, якщо Excel не розпізнає значення як дати.

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

У цій статті ми опишемо чотири різні сценарії та рішення для перетворення тексту на значення дати.

Дати, які містять крапку/крапку

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

Excel не розпізнає це як значення дати і збереже його як текст. Однак цю проблему можна вирішити за допомогою інструмента «Знайти та замінити». Замінивши крапки косими рисками (/), Excel автоматично визначить значення як дати.

Виберіть стовпці, для яких потрібно виконати пошук і заміну.

Натисніть «Додому» > «Знайти та вибрати» > «Замінити» або натисніть Ctrl+H.

  Як зв’язати одну презентацію PowerPoint з іншою

У вікні «Знайти та замінити» введіть крапку (.) у полі «Знайти що» та косу риску (/) у полі «Замінити на». Потім натисніть «Замінити все».

Усі крапки перетворюються на косі риски, і 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

Іноді проблема не спричинена символом роздільника, а має незручну структуру дати просто тому, що вона зберігається як текст.

Нижче наведено список дат у різноманітних структурах, але всі вони впізнавані для нас як дати. На жаль, вони були збережені як текст і потребують конвертації.

  Як налаштувати Центр керування на Mac

Для цих сценаріїв легко конвертувати, використовуючи різноманітні методи.

У цій статті я хотів згадати дві функції для обробки цих сценаріїв. Це DATEVALUE і VALUE.

Функція DATEVALUE перетворить текст у значення дати (ймовірно, це побачила), а функція VALUE перетворить текст у загальне числове значення. Відмінності між ними мінімальні.

На зображенні вище одне зі значень також містить інформацію про час. І це буде демонстрацією незначних відмінностей функцій.

Формула DATEVALUE, наведена нижче, перетворить кожну з них у значення дати.

=DATEVALUE(A2)

Зверніть увагу, як час було вилучено з результату в рядку 4. Ця формула суворо повертає лише значення дати. Результат все одно потрібно буде відформатувати як дату.

У наступній формулі використовується функція VALUE.

=VALUE(A2)

Ця формула дасть ті самі результати, за винятком рядка 4, де також зберігається значення часу.

Потім результати можна відформатувати як дату й час або як дату, щоб приховати значення часу (але не видаляти).