Excel зберігає значення дати та часу як серійні номери на внутрішній панелі.
Це означає, що хоча ви можете побачити дату, таку як “10 січня 2020 року” або “10 січня 2020 року” у комірці, у внутрішній частині програми Excel зберігає її як число. Це корисно, оскільки також дозволяє користувачам легко додавати/віднімати дату та час у Excel.
У Microsoft Excel для Windows "01 січня 1900" зберігається як 1, "02 січня 1900" зберігається як 2 тощо.
Нижче обидва стовпці мають однакові номери, але стовпець А показує цифри, а стовпець В - дату, представлену цим номером.
Це також причина того, що іноді ви можете очікувати дати в комірці, але в підсумку побачити число. Я бачу, що це відбувається весь час, коли я завантажую дані з баз даних або веб-інструментів.
Це відбувається, коли формат комірки налаштований відображати число як число замість дати.
То як ми можемо перетворити ці серійні номери на дати?
Ось про що цей підручник.
У цьому посібнику я б показав вам два дійсно простих способи перетворення серійних номерів у дати в Excel
Примітка: Excel для Windows використовує систему дат 1900 (це означає, що 1 буде означати 01 січня 1900 року, тоді як Excel для Mac використовує систему дат 1904 року (це чоловіки, які 1 представлятимуть 01 січня 1904 року в Excel на Mac)Тож почнемо!
Перетворення серійних номерів у дати за допомогою форматування чисел
Найпростіший спосіб перетворити серійний номер дати на дату - це змінити форматування клітинок з цими номерами.
Деякі з часто використовуваних форматів дати можна знайти на вкладці Домашня сторінка на стрічці
Дозвольте мені показати вам, як це зробити.
Використання вбудованих параметрів форматування дати на стрічці
Припустимо, у вас є набір даних, як показано нижче, і ви хочете перетворити всі ці числа у стовпці А на відповідну дату, яку він представляє.
Нижче наведено кроки для цього.
- Виберіть клітинки з числом, яке потрібно перетворити на дату
- Перейдіть на вкладку «Домашня сторінка»
- У групі "Номер" натисніть спадне меню Форматування номера
- У спадному меню виберіть опцію "Довга дата" або Коротка дата "(залежно від того, у якому форматі ви хочете, щоб ці числа були)
Це воно!
Наведені вище кроки перетворять числа у вибраний формат дати.
Наведені вище кроки не змінили значення в клітинці, лише спосіб її відображення.
Зауважте, що Excel підбирає коротке форматування дати залежно від регіональних налаштувань вашої системи. Наприклад, якщо ви перебуваєте в США, тоді формат дати буде MM/DD/РРРР, а якщо ви перебуваєте у Великобританії, тоді формат дати буде DD/MM/РРРР.
Хоча це швидкий метод перетворення серійних номерів у дати, він має кілька обмежень:
- Існує лише два формати дати - коротка дата та довга дата (і це теж у певному форматі). Отже, якщо ви хочете отримувати лише місяць і рік, а не день, ви не зможете це зробити за допомогою цих опцій.
- Ви не можете відображати дату, а також час, використовуючи параметри форматування у спадному меню. Ви можете вибрати відображення дати або часу, але не обидва.
Отже, якщо вам потрібна більша гнучкість у способі відображення дат у Excel, вам потрібно скористатися опцією Формування користувацьких чисел (розглянута далі у цьому підручнику).
Іноді, коли ви перетворюєте число в дату в Excel, замість дати ви можете побачити деякі знаки хешування (щось на зразок #####). це відбувається, коли ширини стовпця недостатньо для розміщення всієї дати. У такому випадку просто збільште ширину стовпчика.Створення власного формату дати за допомогою діалогового вікна Форматування цифр
Припустимо, у вас є набір даних, як показано нижче, і ви хочете перетворити серійні номери у стовпці А на дати у такому форматі - 01.01.2020
Зауважте, що ця опція недоступна за замовчуванням у методі стрічки, який ми розглянули раніше.
Нижче наведено кроки для цього.
- Виберіть клітинки з числом, яке потрібно перетворити на дату
- Перейдіть на вкладку «Домашня сторінка»
- У групі «Номери» натисніть на значок запуску діалогового вікна (це маленька нахилена стрілка внизу праворуч від групи)
- У діалоговому вікні «Форматування клітинок» переконайтеся, що вибрано вкладку «Номер»
- У параметрах "Категорія" ліворуч виберіть "Дата"
- Виберіть потрібне форматування у полі "Тип".
- Натисніть OK
Наведені вище кроки перетворять числа у вибраний формат дати.
Як бачите, у цьому випадку є більше варіантів форматування дати (порівняно з короткою та довгою датою, яку ми отримали зі стрічкою).
І якщо ви не знайшли потрібний формат, ви також можете створити власний формат дати.
Наприклад, припустимо, що я хочу показати дату в такому форматі - 01/01/2020 (що ще не є опцією у діалоговому вікні форматування комірок).
Ось як я можу це зробити:
- Виберіть клітинки з числами, які потрібно перетворити на дату
- Натисніть вкладку Домашня сторінка
- У групі "Числа" натисніть на значок запуску діалогового вікна (це маленька стрілка нахилу внизу праворуч від групи)
- У діалоговому вікні «Форматування клітинок» переконайтеся, що вибрано вкладку «Номер»
- У параметрах "Категорія" ліворуч виберіть "Користувацький"
- У полі праворуч введіть
мм/дд/рррр
- Натисніть OK
Наведені вище кроки змінять усі числа у зазначений формат.
За допомогою користувацького формату ви отримуєте повний контроль, і він дозволяє відображати дату в будь -якому форматі, який вам потрібен. Ви також можете створити формат, де він відображатиме дату, а також час у тій самій комірці.
Наприклад, у тому ж прикладі, якщо ви хочете показати дату, а також час, ви можете використовувати такий формат:
мм/дд/рррр чч: мм AM/PM
Нижче наведено таблицю, яка показує коди формату дати, які можна використовувати:
| Код форматування дати | Як він форматує дату |
| м | Показує місяць як число від 1 до 12 |
| мм | Покажіть місяці як двозначні числа від 01-12 |
| ммм | Показує місяць у вигляді трьох літер, як у січні-грудні |
| мммм | Показує повну назву місяців за січень-грудень |
| мммммм | Показує назву першого алфавіту місяця, як у J-D |
| d | Показує дні як 1-31 |
| дд | Показує дні як 01-31 |
| ддд | Показує дні як НД-СБ |
| dddd | Показує дні як неділя-субота |
| рр | Показує роки як 00-99 |
| рррр | Показує роки як 1900-9999 |
Перетворення серійних номерів у дати за допомогою формули ТЕКСТ
Вищеописані методи працюють шляхом зміни форматування клітинок з номерами.
Але в деяких випадках ви не зможете використовувати ці методи.
Наприклад, нижче у мене є набір даних, де числа мають провідний апостроф - який перетворює ці числа в текст.
Якщо ви спробуєте за допомогою вбудованих параметрів змінити форматування осередків із цим набором даних, це нічого не дасть. Це пояснюється тим, що Excel не розглядає їх як числа, а оскільки дати для чисел, Excel відмовляється від вашого бажання їх відформатувати.
У такому випадку ви можете або перетворити цей текст на числа, а потім скористатися описаними вище методами форматування, або скористатися іншим прийомом для перетворення серійних номерів у дати - за допомогою функції TEXT.
Функція TEXT бере два аргументи - число, яке потрібно відформатувати, і код формату.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете перетворити всі числа та стовпець А у дати.
Нижче формули, яка може це зробити:
= ТЕКСТ (А2, "ММ/ДД/РРРР")
Зауважте, що у формулі я вказав форматування дати у форматі MM/DD/YYYY. Якщо вам потрібно використати будь -яке інше форматування, ви можете вказати код для нього як другий аргумент формули TEXT.
Ви також можете використовувати цю формулу для показу дати, а також часу.
Наприклад, якщо ви хочете, щоб кінцевий результат був у форматі - 01/01/2020 12:00, скористайтеся формулою нижче:
= ТЕКСТ (A2, "мм/дд/рррр. Чч: мм AM/PM")
У наведеній вище формулі я також додав формат часу, тому якщо в числах є десяткові частини, це буде показано як час у годинах і хвилинах.
Якщо вам потрібна лише дата (а не основна формула), перетворіть формули в статичні значення за допомогою спеціальної вставки.
Однією з великих переваг використання функції TEXT для перетворення серійних номерів у дати є те, що ви можете об’єднати результат формули TEXT з іншими формулами. Наприклад, ви можете об’єднати отриману дату з текстом і показати щось на кшталт - Термін сплати-01 січня 2020 року або Дедлайн-01 січня 2020 року
Примітка: Пам’ятайте, що дати та час зберігаються у вигляді чисел, де кожне ціле число буде представляти один повний день, а десяткова частина - більшу частину дня. Отже, 1 означатиме 1 повний день, а 0,5-півдня або 12 годин.
Отже, це два простих способи, які можна використовувати для перетворення серійних номерів у дати в Microsoft Excel.
Сподіваюся, вам цей підручник був корисним!
