Mam arkusz kalkulacyjny Google, który wygląda mniej więcej tak:
Date | Start time | End time | Minutes
------------+-------------+-------------+-----------
1/11/2012 | 11:39 | 12:41 | ?!
------------+-------------+-------------+-----------
| | |
W tej chwili, jeśli ręcznie podaję liczbę minut między dwoma porami dnia. Czy istnieje prosty sposób, aby obliczyć różnicę czasu i poprosić o to arkusz kalkulacyjny?
Odpowiedzi:
Google dodał nowy format liczb o nazwie Czas trwania. Ustaw pola początkowe i końcowe Format -> Number -> Timeoraz pole obliczeniowe naFormat -> Number -> Duration
Gdy to zrobisz, możesz odjąć pola, aby uzyskać różnicę, jak zauważył Stefano Palazzo w swojej odpowiedzi.
Tak, jeśli twoje pola czasu są poprawnie sformatowane (kliknij Format → Liczba → Czas ), możesz po prostu dodawać i odejmować czasy:
=C2-B2
lub
21:58:00 - 20:44:00 = 1:14:00
To da ci deltę czasu jako HH:MM:SS. A jeśli chcesz obliczyć liczbę minut, można użyć Hour(), Minute()i Second()działa na tym polu:
=(Hour(D2) * 60) + Minute(D2) + (Second(D2) / 60)
Oczywiście, jeśli występują sekundy przestępne, zmiany strefy czasowej lub wydarzenie trwa dłużej niż 24 godziny, nadal będziesz musiał ręcznie dostosować wyniki.
Jeśli jedno wydarzenie rozciąga się po północy, powiedzmy od 23:50 do 00:10, pojawi się to jako czas ujemny!
Aby odnieść się do tych zdarzeń „poprawnie”, możesz albo wpisać „24:10”, albo podzielić wydarzenie na dwa.
Mimo że wprowadzanie danych jest nieco trudniejsze, najbardziej niezawodnym sposobem jest oznaczenie pola beinning i end jako „Date Time”, a pola delta jako „Hours”, które będą wyglądały następująco:
Beginning End Delta
8/1/2013 0:00:00 8/2/2013 12:30:00 36:30:00
Jeśli dodasz następującą formułę D2, minuty są obliczane automatycznie:
=ARRAYFORMULA(IF(ISBLANK(B2:B)=FALSE,((C2:C-B2:B)*24*60),""))
Różnica między czasami według formatu dziesiętnego wyrażana jest w dniach. Dlatego pomnożenie razy 24 razy 60 da minuty
Jest jeden warunek: kolumna D musi być sformatowana jako „normalna”.
Zobacz przykładowy plik, który utworzyłem: Delta Time
Aby uzyskać bardziej niezawodne rozwiązanie, używamy niestandardowej funkcji.
1. Dodanie funkcji niestandardowej
Za pomocą edytora skryptów (postępuj zgodnie z instrukcjami w https://developers.google.com/apps-script/execution_custom_functions ) - pisze:
function toEpoch (indate) {
return indate.getTime();
}
2. Dodaj formułę
Następnie w komórce pisze:
=(toEpoch(C2)-toEpoch(B2)) / 60*1000
Co zamienia różnicę w milisekundach Epoki na minuty.
function toEpoch (data) { return new Date(data) / 1000; }
Zrobiłem dużo eksperymentów. Jest to najprostszy sposób obliczenia delty czasowej w Arkuszach kalkulacyjnych Google. Sformatuj komórkę zawierającą formułę w następujący sposób:
Format> Liczba> Więcej formatów> Więcej formatów daty i godziny , usuń „drugi” i :. Następnie sformatuj czas koniec i początek komórki czasu, jak ten: h:mm am/pm.
Użyj wzoru =abs(end time - start time). To daje wartość bezwzględną, więc nie będzie ujemnych wartości czasu.
O wiele prościej: spójrz na to B2: 23:00 C2: 1:37 D2: = C2-B2 + (B2> C2)
Dlaczego to działa, czas jest ułamkiem dnia, porównanie B2> C2 zwraca True (1) lub False (0), jeśli dodany jest true 1 dzień (24 godziny). http://www.excelforum.com/excel-general/471757-calcul--time-difference-over-midnight.html
Jeśli chcesz, aby delta czasu była mierzona w dniach, użyj
=DAYS(end_date, start_date)
Możesz także spróbować TIMEVALUE().
W powyższym przypadku rozwiązaniem byłoby:
(TIMEVALUE(End Time) - TIMEVALUE(Start Time))*24*60 da ci różnicę czasu w MINUTACH.
Używam tej formuły:
=HOUR(C2-B2)*60+MINUTE(C2-B2)
następnie
Format -> Number -> More Formats -> Custom number format -> #,##0
Do mojej osobistej aplikacji:
StartTime (columnA)| EndTime(Column B)| Date (Column C)
To działało dla mnie:
=if(B4-A4<0,(B4+C4+1)-(A4+C4),B4-A4);sformatuj komórkę jako Format \ Czas \ Czas trwania.
Dla prawdziwych warunków: formuła inspirowana komentarzami Stefano Palazzo.
Zgadzam się, że delta czasu jest wyrażona w dniach, więc aby powrócić do powiedzenia minut, należy pomnożyć różnicę przez 1440, czyli 24 x 60.
Miałem dzisiaj ten problem i pomyślałem, że wspomnę o innym prostym sposobie uzyskania aktualnego czasu w arkuszu kalkulacyjnym Dokumentów Google, który należy wprowadzić CONTROL:do komórki. Możesz użyć tego zarówno dla komórek Czasu początkowego, jak i Końcowego.
Jeśli musisz wprowadzić bieżącą datę w komórce, zostanie to osiągnięte przez CONTROL;