Датата и часът в Excel се третират малко по -различно в Excel, отколкото в другия софтуер за електронни таблици. Ако не знаете как работят датата и часът в Excel, може да срещнете ненужни грешки.
Така че в тази статия ще научим всичко за датата и часа на Excel. Ще научим какво представляват датите в Excel, как да добавяме време в Excel, как да форматираме дата и час в Excel, какви са функциите за дата и час в Excel, как да правим изчисления на дата и час (добавяне, изваждане, умножение и т.н. с дати и часове).
Какво е дата и час в Excel?
Много от вас може би вече знаят, че датите и часът на Excel не са нищо друго освен серийни номера. Датата е цяло число, а времето е дробно число. Датите в Excel имат различно регионално форматиране. Например в моята система той е mm/dd/YYYY (ще използваме този формат в цялата статия). Възможно е да използвате същия формат за дата или да използвате формат за дата dd/mm/YYYY.
Форматиране на дата на клетката
Има няколко опции за форматиране на дата в Excel. Изберете клетка, която може да съдържа дата и натиснете CTRL+1. Това ще отвори диалоговия прозорец Форматиране на клетки. Тук можете да видите две опции за форматиране като Дата и Час. В тези категории има няколко формати за дата, които отговарят на вашите изисквания.
Дати:
Датите в Excel са серийни номера на кобила, започващи от 1 януари 1900 г. Един ден в Excel е равен на 1. Следователно 1-януари-1900 е 1, 2-януари-1900 е 2, а 1-януари-2000 е 36526.
Забавен факт: 1900 г. не беше високосна година, но Excel приема 29 февруари 1900 г. за валидна дата. Беше отчаяна грешка да се състезаваш с Lotus 1-2-3 в онези дни.Пряк път за въвеждане на статична днешна дата в Excel е CTRL+; (Точка и запетая).
За да добавите или извадите ден от дата, просто трябва да извадите или добавите този брой дни към тази дата.
Време:
Excel по подразбиране следва hh: mm формат за време (формат от 0 до 23). Часовете и минутите са разделени с двоеточие без интервали между тях. Можете да го промените в hh: mm AM/PM формат. AM/PM трябва да има 1 интервал от стойността на времето. За да включите секунди, можете да добавите: ss към hh: mm (hh: mm: ss). Всеки друг формат на час е невалиден.
Времето винаги е свързано с дата. Датата идва преди стойността на времето, разделена с интервал от време. Ако не споменавате дата преди часа, по подразбиране тя приема първата дата на excel (която е 1/1/1900). Времето в Excel е дробно число. Той е показан от дясната страна на десетичния знак.
Часа:
Тъй като 1 ден е равно на 1 в Excel и 1 ден се състои от 24 часа, 1 час е равен на 1/24 в Excel. Какво означава това? Това означава, че ако искате да добавите или извадите 1 час към време, трябва да добавите или извадите 1/24. Вижте изображението по -долу.
можете да кажете, че 1 час е равен на 0,041666667 (1/24).
Изчислете часовете между времето в Excel
Минути:
От обяснението на часа в excel сигурно сте предположили, че 1 Minute in excel е равно на 1/(24x60) (или 1/1440 или0.000694444).
Ако искате да добавите минута към Excel време, добавете 1/(24x60).Вижте изображението по -долу. Понякога имате нужда да изчислявате минути между дати и време В Excel можете да го прочетете тук.
Секунди:
Да, секунда в Excel е равна на1/(24x60x60).За да добавяте или изваждате секунди от даден момент, просто трябва да направите същите неща, които направихме за минути и часове.
Дата и час в една клетка
Датите и часовете са свързани помежду си. Датата винаги е свързана с валидна дата, а часът винаги е свързан с валидна дата на Excel. Дори и да не можете да видите някой от тях.
Ако въведете само време в клетка, датата на тази клетка ще бъде 1-януари-1900, дори ако не можете да я видите. Ако форматирате тази клетка като формат дата-час, можете да видите свързаната дата. По същия начин, ако не споменавате часа с датата, по подразбиране е приложен 12:00 ч. Вижте изображението по -долу.
На горното изображение имаме време само в B3 и дата само в B4. Когато форматираме тези клетки като mm/dd/yy hh: mm, получаваме и двете, час и дата в двете клетки.
Така че, докато правите изчисления на дата и час в Excel, дръжте това под контрол.
Без отрицателно време
Както ви казах, датата и часът в excel започват от 1 януари 1900 г. в 12:00 ч. По всяко време преди това не е валидна дата в Excel. Ако извадите стойност от дата, която води до 1-януари 1900 12:00, дори една секунда, Excel ще доведе до грешка ######. Говорих за това тук и в Преобразуване на дата и час от GMT в CST. Това се случва, когато се опитваме да извадим нещо, което води до преди 1 януари 1900 г. 12:00. Опитайте сами. Напишете 12:00 часа и извадете 13 часа от него. вижте какво получавате.
Изчисления с дати и час в Excel
Добавяне на дни към дата:
Добавянето на дни към дата в Excel е лесно. За да добавите ден към датата, просто добавете 1 към него. Вижте изображението по -долу.
Не трябва да добавяте две дати, за да получите бъдещата дата, тъй като тя ще обобщи серийните номера на тези дни и може да получите дата далеч в бъдеще.
Изваждане на дни от дата:
Ако искате да получите обратна дата от дата няколко дни преди, просто извадете този брой дни от датата и ще получите обратна дата. Например, ако искам да знам коя дата е била преди 56 дни от ДНЕС, тогава бих написал тази формула в клетката.
| = ДНЕС ()-56 |
Това ще ни върне датата 56 преди текущата дата.
Забележка: Не забравяйте, че не можете да имате дата преди 1/януари/1900 г. в Excel. Ако получите грешка ######, това може да е причината.
Дни между две дати:
За да изчислим дните между две дати, просто трябва да извадим началната дата от крайната. Вече направих статия по тази тема. Отидете и проверете тук. Можете също да използвате функцията Excel ДНИ за изчисляване на дни между начална и крайна дата.
Добавяне на времена:
Имаше много запитвания как да се добави време за превъзходство, тъй като много хора получават объркващи резултати, когато го правят. Има два вида добавяне във времето. Един добавя време към друг. В този случай и двата часа са форматирани като hh: mm формат на времето. В този случай можете просто да добавите тези времена.
Вторият случай е, когато нямате допълнително време във формат за време. Трябва само да добавите часове, минути и секунди. В този случай трябва да преобразувате тези числа в еквивалентите им по време. Обърнете внимание на тези точки, за да добавите часове, минути и секунди към дата/час.
- За да добавите N часа към X време използвайте формула = X+(N/24) (As 1 = 24 часа)
- За да добавите N минути към X време, използвайте формулата = X+(N/(24*60))
- За да добавите N секунда към X време, използвайте формулата = X+(N/(24*60*60))
Изваждане на времената
Това е същото като добавяне на време, просто се уверете, че няма да получите отрицателна стойност на времето, когато изваждате, защото няма такова нещо като отрицателно число в Excel.
Забележка: Когато добавяте или изваждате време в Excel, което надвишава 24 часа разлика, Excel ще се върне към следващата или предишната дата. Например, ако извадите 2 часа от 29 януари 2019 г. в 1:00 ч., То той ще се върне към 28 януари 2019 г. 23:00 ч. Ако извадите 2 часа от 1:00 ч. (Няма посочената дата), Excel ще върне грешка ######. Казах причината в началото на статията.Добавяне на месеци към дата:Не можете просто да добавите кратни по 30, за да добавите месеци към датата, тъй като различните месеци имат различен брой дни. Трябва да бъдете внимателни, докато добавяте месеци към Date. За да добавим месеци към дата в Excel, използваме функцията EDATE на Excel. Тук имам отделна статия за добавяне на месеци към дата в различни сценарии.
Добавяне на години към датата:
Точно както добавянето на месеци към датата, не е лесно да се добавят години към датата. Трябва да използваме функцията YEAR, MONTH, DAY, за да добавим години към датата. Можете да прочетете за добавяне на години към датата тук.
Ако искате да изчислите години между датите, можете да използвате това.
Функции за обработка на дата и час на Excel:
Тъй като датата и часът са специални в Excel, Excel предоставя специални функции за работа с тях. Тук ще спомена някои от тях.
- TODAY (): Тази функция връща днешната дата динамично.
- DAY (): Връща ден от месеца (връща номер от 1 до 31).
- ДНИ (): Използва се за броене на дните между две дати.
- МЕСЕЦ (): Използва се за получаване на месеца на датата (връща номера от 1 до 12).
- YEAR (): Връща година от датата.
- WEEKNUM (): Връща седмичния номер на дата за една година.
- WEEKDAY (): Връща номера на деня в седмица (1 до 7) от предоставената дата.
- РАБОТЕН ДЕН (): Използва се за изчисляване на работни дни.
- TIMEVALUE (): Използва се за извличане на стойност на времето (сериен номер) от текстово форматирана дата и час.
- DATEVALUE (): Използва се за извличане на стойност на датата (сериен номер) от текстово форматирана дата и час.
Това са някои от най -полезните функции за данни и време в Excel. Има още много функции за дата и час. Можете да ги проверите тук.
Изчисления на дата и час
Ако обясня всички тях тук, тази статия ще стане твърде дълга. Разделих тези техники за изчисляване на времето в Excel в отделни статии. Тук ги споменавам. Можете да кликнете върху тях, за да прочетете.
- Изчислете дни, месеци и години
- Изчислете възрастта от датата на раждане
- Умножаване на стойности и числа на времето.
- Вземете име на месец от Дата в Excel
- Вземете име на ден от Дата в Excel
- Как да получите една четвърт от годината от датата
- Как да добавите работни дни в Excel
- Вмъкнете дата и час с VBA
Така че да, момчета, това е всичко за датата и часа в Excel, за които трябва да знаете. Надявам се тази статия да ви е била полезна. Ако имате въпроси или предложения, запишете ги в секцията за коментари по -долу.