Bendroji formulė
=IF(end>start, end-start, 1-start+end)
Santrauka
Norėdami apskaičiuoti valandų skaičių tarp dviejų kartų, galite naudoti formulę, kuri atima pradžios laiką nuo pabaigos laiko. Jei pradžios ir pabaigos laikas apima vidurnaktį, turėsite pakoreguoti formulę, kaip paaiškinta toliau. Parodytame pavyzdyje formulė D5 yra:
=IF(C5>B5,C5-B5,1-B5+C5)
Paaiškinimas
Norėdami apskaičiuoti valandas tarp kartų, kai abu laikai yra tą pačią kalendorinę dieną, galite tiesiog atimti pradžios laiką iš pabaigos laiko:
=end-start
Norėdami rodyti valandas, pritaikykite skaičių formatavimą.
Tačiau kai laikas peržengia dienos ribą (vidurnaktį), viskas gali būti keblu. Perskaitykite toliau, kad pamatytumėte kelis būdus, kaip suvaldyti šį iššūkį.
Kaip „Excel“ stebi laiką
Programoje „Excel“ viena diena lygi 1, o tai reiškia 24 valandas. Tai reiškia, kad laikas ir valandos yra dalinės 1 vertės, kaip parodyta toliau pateiktoje lentelėje:
| Valandos | Laikas | Trupmena | Vertė |
|---|---|---|---|
| 3 | 3:00 val | 3/24 | 0,125 |
| 6 | 6:00 RYTO | 6/24 | 0,25 |
| 4 | 4:00 val | 4/24 | 0,167 |
| 8 | 08:00 | 8/24 | 0,333 |
| 12 | 12:00 | 12/24 | 0.5 |
| 18 | 18:00 val | 18/24 | 0,75 |
| 21 | 21:00 | 21/24 | 0,875 |
Paprastas trukmės skaičiavimas
Kai pradžios ir pabaigos laikas yra tą pačią dieną, trukmę valandomis apskaičiuoti yra nesudėtinga. Pvz., Kai pradžios laikas yra 9:00 ir pabaigos laikas - 17:00, galite tiesiog naudoti šią formulę:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Kai laikas kerta vidurnaktį
Apskaičiuoti praėjusį laiką yra kebliau, jei laikai peržengia dienos ribą (vidurnaktį). Pvz., Jei pradžios laikas yra 22:00 vieną dieną, o pabaigos laikas yra 5:00 AM kitą dieną, pabaigos laikas iš tikrųjų yra mažesnis nei pradžios laikas ir aukščiau pateikta formulė grąžins neigiamą vertę, o „Excel“ pynė rodo maišos simbolių eilutę (########).
Norėdami išspręsti šią problemą, galite naudoti šią formulę laikams, perkopiantiems dienos ribą:
=1-start+end
Atimdami pradžios laiką nuo 1, gausite pirmosios dienos laiką, kurį galite tiesiog pridėti prie 2 dienos laiko, kuris yra toks pats kaip pabaigos laikas .
Ši formulė neveiks kelis kartus tą pačią dieną, todėl galime apibendrinti ir sujungti abi formules IF sakinyje:
=IF(end>start, end-start, 1-start+end)
Dabar, kai abu laikai yra tą pačią dieną, pabaiga yra didesnė nei pradžios laikas , todėl naudojama paprasta formulė. Bet kai dienos ribos laikosi, naudojama antroji formulė.
MOD funkcijos alternatyva
Naudodami MOD funkciją su dalikliu 1, mes galime supaprastinti aukščiau pateiktą formulę:
=MOD(end-start,1)
Čia MOD funkcija rūpinasi neigiama problema, naudodama MOD funkciją, norėdama „apversti“ neigiamas reikšmes iki reikiamos teigiamos vertės. Ši formulės versija bus taikoma abiem atvejais, todėl galime pašalinti sąlyginį IF teiginį.
Pastaba: nei viena iš aukščiau pateiktų formulių netaikys ilgesnės nei 24 valandų trukmės. Jei jums to reikia, žiūrėkite datos ir laiko parinktį žemiau.
Norėdami sužinoti daugiau apie modulą, čia yra gera nuoroda į Khano akademiją.
Formatavimo trukmės
Pagal numatytuosius nustatymus „Excel“ gali rodyti laiką, net ir laiką, nurodantį trukmę, naudodama AM / PM. Pvz., Jei apskaičiuotas laikas yra 6 valandos, „Excel“ gali tai rodyti kaip 6:00. Norėdami pašalinti AM / PM, pritaikykite pasirinktinį skaičių formatą, pvz., H: mm.
Tais atvejais, kai apskaičiuotas laikas viršija 24 valandas, galite naudoti pasirinktinį formatą, pvz., (H): mm. Kvadratinių skliaustų sintaksėje (h) „Excel“ nurodoma rodyti ilgesnes nei 24 valandų trukmės valandas. Jei nenaudosite skliaustų, „Excel“ tiesiog „apsivers“, kai trukmė pasieks 24 valandas (pvz., Laikrodis).
Supaprastinkite problemą su data + laiku
Paprasčiausiai galite apskaičiuoti praėjusio laiko problemą dirbdami su reikšmėmis, kuriose yra ir data, ir laikas. Norėdami kartu įvesti datą ir laiką, naudokite vieną tarpą tarp laiko ir datos: 2016-09-01 10:00
Tada galite naudoti pagrindinę formulę apskaičiuoti praėjusį laiką:
=end-start
Žemiau pateiktame pavyzdyje pradžios ir pabaigos vertėse yra datos ir laikas:
Formulė yra:
=C5-B5
Suformatuotas pagal pasirinktinį skaičių formatą (h): mm, kad būtų rodomos praėjusios valandos.
