Excel съхранява стойностите за дата и час като серийни номера в задната част.
Това означава, че въпреки че може да видите дата като „10 януари2021-2022 г.“ или „01/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 избира форматирането на кратка дата въз основа на регионалните настройки на вашата система. Например, ако сте в САЩ, тогава форматът на датата ще бъде ММ/ДД/ГГГГ, а ако сте във Великобритания, тогава форматът на датата ще бъде ДД/ММ/ГГГГ.
Въпреки че това е бърз метод за преобразуване на серийни номера в дати, той има няколко ограничения:
- Има само два формата за дата - кратка дата и дълга дата (и това също в конкретен формат). Така че, ако искате да получите само месеца и годината, а не деня, няма да можете да направите това, като използвате тези опции.
- Не можете да показвате дата и час, като използвате опциите за форматиране в падащото меню. Можете да изберете да покажете датата или часа, но не и двете.
Така че, ако имате нужда от повече гъвкавост в начина, по който искате да показвате дати в Excel, трябва да използвате опцията Персонализирано форматиране на числа (обхваната по -долу в този урок).
Понякога, когато преобразувате число в дата в Excel, вместо датата може да видите някои хеш знаци (нещо като #####). това се случва, когато ширината на колоната не е достатъчна, за да побере цялата дата. В такъв случай просто увеличете ширината на колоната.Създаване на персонализиран формат на дата с помощта на диалоговия прозорец Форматиране на числа
Да предположим, че имате набор от данни, както е показано по -долу и искате да преобразувате серийните номера в колона А в дати в следния формат - 01.01.2020 г.
Имайте предвид, че тази опция не е налична по подразбиране в метода на лентата, който разгледахме по -рано.
По -долу са описани стъпките за това:
- Изберете клетките, които имат номера, който искате да преобразувате в дата
- Щракнете върху раздела „Начало“
- В групата Номера кликнете върху иконата за стартиране на диалоговия прозорец (това е малка наклонена стрелка в долния десен ъгъл на групата)
- В диалоговия прозорец „Форматиране на клетки“, който се отваря, уверете се, че е избран раздел „Номер“
- В опциите „Категория“ вляво изберете „Дата“
- Изберете желаното форматиране от полето „Тип“.
- Щракнете върху OK
Горните стъпки биха преобразували числата в избрания формат за дата.
Както можете да видите, в този случай има повече опции за форматиране на дата (в сравнение с късата и дългата дата, която получихме с лентата).
И в случай, че не намерите формата, който търсите, можете също да създадете свой собствен формат за дата.
Например, да кажем, че искам да покажа датата в следния формат - 01/01/2020 (което вече не е опция в диалоговия прозорец за форматиране на клетки).
Ето как мога да направя това:
- Изберете клетките, които имат числата, които искате да преобразувате в дата
- Щракнете върху раздела Начало
- В групата „Числа“ кликнете върху иконата за стартиране на диалоговия прозорец (това е малка стрелка за накланяне в долния десен ъгъл на групата)
- В диалоговия прозорец „Форматиране на клетки“, който се отваря, уверете се, че е избран раздел „Номер“
- В опциите „Категория“ вляво изберете „Персонализиран“
- В полето вдясно въведете
мм/дд/гггг
- Щракнете върху OK
Горепосочените стъпки ще променят всички числа в определения формат на номера.
С персонализиран формат получавате пълен контрол и той ви позволява да показвате датата във всеки формат, който искате. Можете също да създадете формат, в който да показва датата, както и часа в същата клетка.
Например, в същия пример, ако искате да покажете дата и час, можете да използвате формата по -долу:
mm/dd/yyyy hh: mm AM/PM
По -долу е таблицата, която показва кодовете за формат на датата, които можете да използвате:
| Код за форматиране на дата | Как форматира датата |
| м | Показва месеца като число от 1-12 |
| mm | Показвайте месеците като двуцифрени числа от 01-12 |
| ммм | Показва месеца с три букви, както през януари-декември |
| Мммм | Показва пълното име на месеците през януари-декември |
| ммммм | Показва първата азбука с името на месеца, както в J-D |
| д | Показва дните като 1-31 |
| дд | Показва дните като 01-31 |
| ddd | Показва дните като неделя-събота |
| dddd | Показва дните като неделя-събота |
| yy | Показва години като 00-99 |
| yyyy | Показва години като 1900-9999 |
Преобразувайте серийни номера в дати с помощта на формулата TEXT
Горните методи работят чрез промяна на форматирането на клетките с номера.
Но в някои случаи може да не успеете да използвате тези методи.
Например по -долу имам набор от данни, където числата имат водещ апостроф - който преобразува тези числа в текст.
Ако опитате и използвате вградените опции, за да промените форматирането на клетките с този набор от данни, това няма да направи нищо. Това е така, защото Excel не ги счита за числа и тъй като датите за числа, Excel отказва желанието ви да ги форматирате.
В такъв случай можете или да преобразувате този текст в числа и след това да използвате описаните по -горе методи за форматиране, или да използвате друга техника за преобразуване на серийни номера в дати - с помощта на функцията TEXT.
Функцията TEXT приема два аргумента - номера, който трябва да бъде форматиран, и кода за форматиране.
Да предположим, че имате набор от данни, както е показано по -долу, и искате да преобразувате всички числа и колона А в дати.
Под формулата, която може да направи това:
= ТЕКСТ (A2, "MM/DD/ГГГГ")
Имайте предвид, че във формулата съм посочил форматирането на датата във формат MM/DD/YYYY. Ако трябва да използвате друго форматиране, можете да посочите кода за него като втори аргумент на формулата TEXT.
Можете също да използвате тази формула, за да покажете датата, както и часа.
Например, ако искате крайният резултат да бъде във формата - 01/01/2020 12:00 ч., Използвайте формулата по -долу:
= ТЕКСТ (A2, "mm/dd/yyyy hh: mm AM/PM")
В горната формула добавих и формата на часа, така че ако в числата има десетични части, това ще се показва като времето в часове и минути.
Ако искате само датата (а не основната формула), преобразувайте формулите в статични стойности, като използвате специална паста.
Едно голямо предимство при използването на функцията TEXT за преобразуване на серийни номера в дати е, че можете да комбинирате резултата от формулата TEXT с други формули. Например, можете да комбинирате получената дата с текста и да покажете нещо като - Крайният срок е 01-януари-2020 или Крайният срок е 01-януари-2020
Забележка: Датите и часът се съхраняват като числа, където всяко цяло число ще представлява един цял ден, а десетичната част ще представлява тази голяма част от деня. Така че 1 би означавало 1 цял ден, а 0,5 би означавало половин ден или 12 часа.
Това са два прости начина, които можете да използвате за преобразуване на серийни номера в дати в Microsoft Excel.
Надявам се, че сте намерили този урок за полезен!
