По подразбиране датата и часът се записват като числа/десетични знаци в Excel. Но често го виждате в различни формати поради начина, по който са форматирани клетките.
Например в примера по -долу стойността в колони А и В е една и съща, но те се показват различно поради формата.
И едно от нещата, от които много хора се нуждаят в Excel, е да го направят конвертирате времето в десетична (като часове, минути или секунди).
Например, вместо да има дата и час като 01-01-2020 6:30 сутринта, може да искате да получите:
- Брой часове като 6
- Брой минути като 30
За щастие Excel има някои страхотни формули, можете лесно да конвертирате времето в десетични стойности.
Как да конвертирате времето в десетични числа в Excel
В този урок ще ви покажа някои примери за преобразуване на времето в десетична с помощта на формули (т.е. преобразуване на времето в часове, минути и секунди).
Има няколко начина да конвертирате времето в десетично в Excel:
- Използването на аритметична операция е най -лесният начин за преобразуване на времето в десетични числа. Например:
- За да преобразувате времето в часове, умножете стойността на времето с 24
- За да преобразувате времето в минути, умножете стойността на времето с 24*60
- За да преобразувате времето в секунди, умножете стойността на времето с 24*60*60
- Можете също да използвате вградени формули на Excel, като например ПРЕВЪРНЕТЕ или ЧАС/МИНУТА/ВТОРИ
В следващите раздели ще ви покажа примери за това как да конвертирате времето на Excel в десетични стойности с помощта на тези методи.
Така че нека започнем!
Преобразувайте времето в часове в Excel
В този раздел обхващам три различни начина за преобразуване на времето в часове в Excel.
Използване на просто умножение за получаване на стойността на часа
Да предположим, че имате време в клетка като 18:00 ч. (В клетка А2) и искате да го преобразувате в часове.
Можете просто да го умножите по 24 и това ще ви даде колко часа са изминали до това време.
= A2*24
Това работи поради факта, че Excel съхранява дати и часове като числа. Цял ден (24 часа) се съхранява като 1 в Excel. Това означава, че всеки час се съхранява като 1/24.
Така че, докато виждате стойността 18:00 часа, в задната част, тя се съхранява като стойност 0,75 (което показва, че 18 часа от 24 -те вече са изтекли). Така че, когато го умножите с 24, това ви дава броя на изминалите часове.
В случай, че имате стойност като 6:32 PM, използването на горната формула ще ви даде резултата като 18.53 (където частта минути/секунди е показана в десетични знаци, а пълните/пълните часове като цели числа). В този пример числовата стойност на 32 минути в Excel ще бъде 0,53 часа.
Забележка: В случай, че получите стойността на часа, която съдържа десетичните знаци (например 18,53), уверете се, че сте я форматирали, за да показва и десетичните знаци. В случай, че не сте го направили, може да видите резултата като 19, тъй като Excel ви показва закръглената цяло числова стойност. Това обаче няма да промени стойността в клетката, която ще продължи да бъде 18,53В случай, че искате само пълната/пълна стойност на часа и игнорирате частта за минути, използвайте формулата по -долу, използвайки INT.
= INT (A2*24)
INT ви дава само целочислената част и не е нужно да се притеснявате за допълнителни минути.
Използване на Excel функции (HOUR, MINUTE и SECOND)
Ако не ви харесва методът за ръчно умножение, друг наистина лесен начин да преобразувате времето в часове е чрез използване на вградените функции, свързани с времето (HOUR, MINUTE и SECOND)
Да предположим, че имате времето, както е показано по -долу, и искате да знаете колко часа са изминали през деня.
По -долу е формулата за преобразуване на времето в часове
= ЧАС (A2)+МИНУТА (A2)/60+ВТОРИ (A2)/(60*60)
Функцията HOUR просто връща броя на часовете, които са изминали за даденото време, както и функциите MINUTE и SECOND.
Но тъй като имаме нужда от крайната стойност в часове, трябва да разделим минутната стойност на 60 (за да я преобразуваме в часове) и втората стойност на 60*60 (отново, за да я преобразуваме в часове).
Използване на функцията Convert
Друг наистина лесен (вероятно най -лесният от трите метода) е да използвате функцията CONVERT.
Тази функция приема числова стойност (която би била времето в този случай) и може да я преобразува в часове (или минути/секунди).
По -долу е формулата, която ще преобразува времето в часове:
= CONVERT (A2, "day", "hr")
Вторият аргумент е да укажете функцията на съществуващия формат на данни, а третият е форматът, в който искате да конвертирате.
Забележка: Понякога резултатите могат да изглеждат различно от очакваното. Това може да се дължи на формата на клетката, който може да показва резултата във формат за дата вместо десетичен формат. Можете лесно да промените това, като отидете в раздела Начало и зададете формата от падащото меню за формат (той е в групата Числа)На практика е по -вероятно да съберете дата и час заедно (вместо само времето, както съм показал в горните примери). Все още можете да използвате методите, показани по -горе, но първо трябва да разделите стойността на датата и часа и след това да използвате методите, показани по -горе, само за частта от времето. По -късно в този урок, аз също обхващам два метода за разделяне на дата и час в Excel с помощта на формули.
Можете също да използвате същите описани по -горе методи, за да конвертирате времето в минути и секунди.
Нека бързо разгледаме и тези примери.
Преобразувайте времето в минути в Excel
В този раздел разглеждам три различни начина за преобразуване на времето в минути в Excel.
Използване на просто умножение за получаване на минутната стойност
На ден има 1440 минути (24*60).
Така че, за да преобразувате времето в минути, можете просто да умножите стойността на времето с 1440.
Да предположим, че имате време в клетка като 18:00 ч. (В клетка А2) и искате да го преобразувате в минути.
По -долу е формулата за това:
= A2*1440
Това ще ви даде общия брой минути, които са изтекли на този ден за дадения час.
Забележка: Не забравяйте, че в случай че имате и секунди във времето, те ще бъдат преобразувани в минути и ще бъдат показани като десетична част. За да бъдат те видими в резултата, трябва да форматирате получените клетки, за да показват резултати до 2/3 знака след десетичната запетая.В случай, че искате само минутната стойност и игнорирате частта от секунди, използвайте формулата по -долу, използвайки INT.
= INT (A2*1440)
Използване на Excel функции (HOUR, MINUTE и SECOND)
Точно както използвахме тези функции, за да получим стойността на часа, можете също да ги използвате, за да преобразувате времето в минути.
По -долу е формулата, която ще преобразува времето в минути с помощта на тези функции:
= ЧАС (A2)*60+МИНУТА (A2)+ВТОРИ (A2)/60
Тъй като целта тук е да получим всички части за минути (часове, минути и секунди), умножаваме 60 с часове, за да получим стойността на минутите в него и разделяме секундите на 60, за да получим минутната стойност.
Използване на функцията Convert
Отново, определено най -лесният начин да конвертирате времето в минути е като използвате формулата за преобразуване.
Формулата по -долу взема стойността на времето и я преобразува в минути;
= CONVERT (A2, "ден", "mn")
Вторият аргумент казва на функцията, че стойността в клетка A2 е във формат на ден, а третият аргумент („mn“) й казва да я преобразува в минути.
Преобразувайте времето в секунди в Excel
В този раздел разглеждам три различни начина за преобразуване на времето в секунди в Excel.
Използване на просто умножение за получаване на стойността на секундите
За един ден има 86400 секунди (24*60*60).
Така че, за да преобразувате времето в секунди, можете просто да умножите стойността на времето с 86400.
Да предположим, че имате време в клетка като 18:32:30 ч. (В клетка А2) и искате да го преобразувате в секунди.
По -долу е формулата за това:
= A2*86400
Това ще ви даде общия брой секунди, които са изтекли на този ден за дадения час.
Използване на Excel функции (HOUR, MINUTE и SECOND)
По -долу е формулата, която ще преобразува времето в минути с помощта на тези функции:
= ЧАС (A2)*60*60+МИНУТА (A2)*60+ВТОРИ (A2)
Тъй като целта тук е да получим всички части (часове, минути и секунди) за секунди, умножаваме 60*60 с часове, за да получим стойността на секундите в нея и умножаваме минути по 60, за да получим стойността на секундите.
Разделете датата и часа и след това преобразувайте времето в десетично
Досега сме виждали примери, в които имаме само време и искахме да преобразуваме това време в часове, минути или секунди.
Но какво, ако имате датата, както и часа?
В този случай не можете да използвате горните методи директно. Първо ще трябва да разделите датата и часа и след това да преобразувате часа в десетични.
Тъй като датата и часът се съхраняват като числа в Excel, разделянето им става по -лесно.
Целият ден се съхранява като цяло число, а часовата част се съхранява като десетична стойност. Така че, ако искате да разделите датата и часа, просто трябва да разделите целочислената част и десетичната част.
Нека да разгледаме няколко наистина лесни начина за разделяне на датата и часа в Excel.
Разделете датата и часа, като използвате формулата INT
Да предположим, че имате набора от данни, както е показано по -долу, и искате да разделите датата и часа.
За да направите това, въведете следната формула в клетка В2:
= A2-INT (A2)
Горната формула премахва целочислената част и ви дава десетичната част на датата.
Сега можете да използвате някой от трите метода, обхванати по -горе (използвайки аритметично умножение, използвайки формулата HOUR/MINUTE/SECOND или използвайки формулата CONVERT), за да преобразувате времето в десетична.
Разделете датата и часа, като използвате формулата MOD
Друг начин да направите това може да бъде като използвате функцията MOD.
Да предположим, че имате набора от данни, както е показано по -долу, и искате да разделите датата и часа.
За да направите това, въведете следната формула в клетка В2:
= MOD (A2,1)
Това ще ви даде десетичната част веднага и след това можете да използвате някой от трите метода, обхванати по -горе, за да преобразувате времето в десетични числа (часове, минути или секунди).
