Период от време във формула на Excel
Има моменти, когато трябва да извършим различни операции като събиране или изваждане на стойности на датата с Excel. Като задаваме диапазони от дати в Excel, можем да извършваме изчисления на тези дати. За задаване на диапазони от дати в Excel можем първо да форматираме клетките, които имат начална и крайна дата като „Дата“ и след това да използваме операторите: „+“ или „-“, за да определим крайната дата или продължителността на диапазона.
Примери за период от време в Excel
Пример # 1 - Основни периоди от време
Нека видим как добавянето на число към дата създава период от време.
Да предположим, че имаме начална дата в клетка А2.
Сега, ако добавим число, да кажем 5 към него, можем да изградим период от време.
Сега, когато изберем клетка A3 и напишем '= B2 + 1'.
Копирайте тази клетка: B2 и я поставете в клетка B3; относителната препратка към клетката ще се промени, както следва:
Така че можем да видим, че множество периоди от време могат да бъдат изградени по този начин.
Пример # 2 - Създаване на последователност от дати
С Excel можем лесно да създадем няколко последователности. Сега знаем, че датите са някои числа в Excel. Така че можем да използваме същия метод за създаване на периоди от време. Така че, за да създадем диапазони от дати, които имат същия диапазон или интервал, но датите се променят, докато слизаме, можем да следваме стъпките по-долу:
- Въведете начална и крайна дата в минимум два реда.
- Изберете двата диапазона и го плъзнете надолу отдолу до реда, където изискваме диапазони от дати:
Така че можем да видим, че използвайки диапазоните от дати в първите два реда като шаблон, Excel автоматично създава диапазони от дати за следващите редове.
Пример # 3
Да предположим, че имаме две дати в две клетки и ние искаме да ги покажем като обединени като период от време в една клетка. За целта може да се използва формула, базирана на функцията „ТЕКСТ“. Общият синтаксис за тази формула е както следва:
= ТЕКСТ (дата1, ”формат”) & ”-” & ТЕКСТ (дата2, ”формат”)Случай 1:
Тази функция получава две числови стойности като числови и обединява тези две дати под формата на период от време според персонализиран формат на датата („mmm d“ в този случай):
Период от време = ТЕКСТ (A2, ”mmm d”) & “-” & TEXT (B2, ”mmm d”)
Така че можем да видим на горната екранна снимка, че сме приложили формулата в клетка C2. Функцията TEXT получава датите, съхранени в клетки A2 и B2, операторът амперсанд '&' се използва за обединяване на двете дати като период от време в персонализиран формат, посочен като „mmm d“ в този случай, в една клетка и двете дати се обединяват с тире '-' в резултантния диапазон от дати, който се определя в клетка C2.
Случай 2:
Да кажем, в този пример искаме да комбинираме двете дати като период от време в една клетка с различен формат, да речем „d mmm yy“. Така че формулата за период от време, в този случай, ще бъде следната:
Период от време = ТЕКСТ (A3, "d mmm yy") & "-" & TEXT (B3, "d mmm yy")
Така че можем да видим на горната екранна снимка, че функцията TEXT получава датите, съхранени в клетки A3 и B3, операторът амперсанд '&' се използва за обединяване на двете дати като период от време в персонализиран формат, посочен като „d mmm yy ”В този случай в една клетка. Двете дати се обединяват с тире '-' в резултантния период от време, определен в клетка C3.
Пример # 4
Сега нека видим какво ще се случи в случай, че липсва началната или крайната дата. Нека кажем, че крайната дата липсва, както следва:
Формулата, базирана на функцията TEXT, която използвахме по-горе, няма да работи правилно, в случай че крайната дата липсва, тъй като тирето във формулата така или иначе би било добавено към началната дата, т.е. заедно с началната дата, ние също вижте тире, показано в периода от време. За разлика от това бихме искали да видим началната дата само като период от време, в случай че крайната дата липсва.
Така че в този случай можем да получим формулата, като обгърнем конкатенацията и втората функция TEXT в клауза IF, както следва:
Период от време = TEXT (A2, ”mmm d”) & IF (B2 “”, “-” & TEXT (B2, ”mmm d”), “”)
Така че можем да видим, че горната формула създава пълен период от време, използвайки и двете дати, когато и двете са налице. Той обаче показва само началната дата в посочения формат, ако крайната дата липсва. Това се прави с помощта на клауза IF.
Сега, в случай че и двете дати липсват, бихме могли да използваме вложен оператор IF в Excel (т.е. един IF в друг оператор IF), както следва:
Период от време = IF (A4 ””, TEXT (A4, ”mmm d”) & IF (B4 “”, “-” & TEXT (B4, ”mmm d”), “”), ””)
Така че можем да видим, че горната формула връща празен низ, ако началната дата липсва.
В случай че липсват и двете дати, тогава се връща и празен низ.
Неща за запомняне
- Можем дори да създадем списък с последователни дати, като използваме командата „Fill Handle“. За да направите това, можем да изберем клетката с начална дата и след това да я плъзнем до диапазона от клетки, където искаме да попълним. Кликнете върху раздела „Начало“ -> „Редактиране“ -> „Попълване на серията“ и след това изберете единица за дата, която желаем да използваме.
- Ако искаме да изчислим продължителността или броя на дните между две дати, можем просто да извадим двете дати с помощта на оператора '-' и ще получим желания резултат.
Забележка: Форматът на клетките: A2 и B2 е „Дата“, докато този на клетка C2 е „Общ“, тъй като изчислява броя на дните.
