Jak sprawić, by Excel traktował dni / daty tak, jakby rozpoczynały się o 20:00 zamiast 12 rano

Nasz 24-godzinny dzień jest od 20:00 do 19:59. To jest uważane za 1 dzień, a nie 12 rano do 23:59. W przypadku godzin po 20:00, wpisy muszą być uważane za część następnego dnia. Na przykład:

    Actual Date/Time    How Excel should treat it

    30-Sep  11:52 PM    1-Oct  +3:52
     1-Oct   6:00 AM    1-Oct +10:00 
     2-Oct   7:00 AM    2-Oct +11:00

Jak sprawić, by program Excel zobaczył 30 września o 23:52 jako 1 października, wraz z godziną 6:00, jako pierwsze kilka godzin działania, a nie koniec dnia?

Najprostszym podejściem byłoby przetłumaczenie czasu, a następnie normalne obliczenie.

Twój dzienny czas rozpoczęcia wynosi 4 godziny przed „standardową” definicją dnia. Jeśli przesuniesz czas do przodu o cztery godziny, będą one zgodne z definicją, z którą program Excel jest zaprojektowany do normalnej pracy. Pomyśl o tym w ten sposób: jeśli jest to 20 września 30 września, jest to początek 1 października dla kogoś w strefie czasowej cztery godziny na wschód. Jest to równoważne z wprowadzaniem danych w czasie rzeczywistym. Nie zmienia relacji między czasami, bardziej przypomina formatowanie ich do innej strefy czasowej.

Realizacja

Wartość przesunięcia czasowego

Kiedy masz stałą, która jest ponownie wykorzystywana, a zwłaszcza, jeśli kiedykolwiek będziesz musiał ją zmienić, dobrą praktyką jest przechowywanie wartości w jednym miejscu i utrzymywanie jej tam. Pozwala to uniknąć konieczności zmiany zakodowanej wartości w wielu lokalizacjach, a także potencjalnych literówek. Następnie odwołujesz się do tej wartości we wzorach.

W innej części arkusza kalkulacyjnego, powiedzmy X1, możesz umieścić etykietę, jak time offset:, aw Y1 ustaw wartość 4 godzin. Przyjaznym dla użytkownika sposobem jest wpisanie Y1: =TIME(4,0,0). To jest równe wartości czterech godzin, zero minut i zero sekund.

Tłumaczenie na czas

Więc jeśli A1 zawiera faktyczną datę / godzinę, formuła w B1 byłaby następująca:

=A1+$Y$1 

W Twoim przykładzie wyświetlasz datę i godzinę w osobnych kolumnach. Nie jest jasne, czy jest to tylko formatowanie zastosowane do zduplikowanych wartości daty / godziny, czy komórki zawierają tylko datę lub godzinę. Jeśli to drugie, możesz je po prostu połączyć w swojej formule. Formuła przejdzie więc do C1 jako:

=A1+B1+$Y$1

Wprowadź formułę pierwszej komórki, a następnie skopiuj ją dla całego zakresu danych. Znaki dolara zakotwiczają odnośnik Y1, dzięki czemu nie koryguje się podczas kopiowania formuły do ​​innych komórek.

Nowa kolumna będzie zawierała przetłumaczone wartości, z którymi pracujesz. (Jeśli wynik pojawi się jako liczba, wystarczy sformatować komórki jako datę / godzinę).

Jeśli używasz tylko wartości do obliczeń czasu, takich jak różnica czasu, wartości te można wykorzystać bezpośrednio, a obliczenia będą prawidłowe. Jeśli chcesz wyświetlić przetłumaczony czas jako rzeczywisty czas, po prostu wykonaj odwrotne. Wyświetlana wartość byłaby wartością przetłumaczoną minus cztery godziny. Na przykład:

=C1-$Y$1