Samouczek Excela: Obliczanie daty i godziny (oblicz różnicę, wiek, dodaj / odejmij)
W programie Excel obliczanie daty i godziny jest często używane, na przykład obliczanie różnicy między dwiema datami / godzinami, dodawanie lub odejmowanie daty i godziny, pobieranie wieku na podstawie daty urodzenia i tak dalej. W tym samouczku zawiera listę prawie scenariuszy dotyczących obliczania daty i godziny oraz zapewnia powiązane metody.
W tym samouczku tworzę kilka przykładów, aby wyjaśnić metody, możesz zmienić referencje według potrzeb, gdy używasz poniższego kodu lub formuł VBA
1. Oblicz różnicę między dwiema datami/godzinami
Obliczanie różnicy między dwiema datami lub dwoma czasami może być najbardziej normalnym problemem obliczania daty i godziny, który spotykasz w codziennej pracy w programie Excel. Poniższe przykłady mogą pomóc w zwiększeniu wydajności, gdy napotkasz te same problemy.
1.11 Oblicz różnicę między dwiema datami w dniach/miesiącach/latach/tygodniach
Funkcja Excel DATEDIF może służyć do szybkiego obliczenia różnicy między dwiema datami w dniach, miesiącach, latach i tygodniach.
Kliknij, aby uzyskać więcej informacji na temat JEŻELI DATA funkcjonować
Różnica dni między dwiema datami
Aby uzyskać różnicę dni między dwiema datami w komórce A2 i B2, użyj następującej formuły
=DATEDIF(A2,B2,"d")
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Różnica miesięcy między dwiema datami
Aby uzyskać różnicę miesięcy między dwiema datami w komórce A5 i B5, użyj następującej formuły
=DATEDIF(A5,B5,"m")
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Różnica lat między dwiema datami
Aby uzyskać różnicę lat między dwiema datami w komórce A8 i B8, użyj następującej formuły
=DATEDIF(A8,B8,"y")
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Różnica tygodni między dwiema datami
Aby uzyskać różnicę tygodni między dwiema datami w komórce A11 i B11, użyj następującej formuły
=DATEDIF(A11,B11,"d")/7
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Uwaga:
1) Gdy używasz powyższej formuły, aby uzyskać różnicę tygodni, może ona zwrócić wynik w formacie daty, musisz sformatować wynik do ogólnego lub liczby zgodnie z potrzebami.
2) Gdy używasz powyższej formuły, aby uzyskać różnicę tygodni, może ona powrócić do liczby dziesiętnej, jeśli chcesz uzyskać całkowity numer tygodnia, możesz dodać funkcję ROUNDDOWN przed, jak pokazano poniżej, aby uzyskać całkowitą różnicę tygodni:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Oblicz miesiące ignoruj lata i dni między dwiema datami
Jeśli chcesz tylko obliczyć różnicę miesięcy, ignorując lata i dni między dwiema datami, jak pokazuje poniższy zrzut ekranu, oto formuła, która może ci pomóc.
=DATEDIF(A2,B2,"ym")
Naciśnij przycisk Wchodzę aby uzyskać wynik.
A2 to data rozpoczęcia, a B2 to data zakończenia.
1.13 Oblicz dni ignoruj lata i miesiące między dwiema datami
Jeśli chcesz tylko obliczyć różnicę dni, ignorując lata i miesiące między dwiema datami, jak pokazano na poniższym zrzucie ekranu, oto formuła, która może ci pomóc.
=DATEDIF(A5,B5,"md")
Naciśnij przycisk Wchodzę aby uzyskać wynik.
A5 to data rozpoczęcia, a B5 to data zakończenia.
1.14 Oblicz różnicę między dwiema datami i latami, miesiącami i dniami zwrotu
Jeśli chcesz uzyskać różnicę między dwiema datami i zwrotem xx lat, xx miesięcy i xx dni, jak pokazuje poniższy zrzut ekranu, oto również podana formuła.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Naciśnij przycisk Wchodzę aby uzyskać wynik.
A8 to data rozpoczęcia, a B8 to data zakończenia.
1.15 Oblicz różnicę między datą a dniem dzisiejszym
Aby automatycznie obliczyć różnicę między datą a dniem dzisiejszym, po prostu zmień datę końcową w powyższych formułach na DZIŚ(). Tutaj należy obliczyć różnicę dni między datą przeszłą a dzisiejszą jako instancją.
=DATEDIF(A11,TODAY(),"d")
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Note: jeśli chcesz obliczyć różnicę między przyszłą datą a dzisiejszą, zmień datę_początkową na dzisiejszą i weź przyszłą datę jako datę_końcową w ten sposób:
=DATEDIF(TODAY(),A14,"d")
Zauważ, że data_początkowa musi być mniejsza niż data_końcowa w funkcji DATEDIF, w przeciwnym razie zwróci #NUM! wartość błędu.
1.16 Oblicz dni robocze z lub bez urlopu między dwiema datami
Czasami może być konieczne policzenie liczby dni roboczych ze świętami lub bez dni wolnych między dwiema podanymi datami.
W tej części wykorzystasz funkcję DNI.ROBOCZE.INTL:
Kliknij DNI.ROBOCZE.NIESTAND poznać jego argumenty i zastosowanie.
Policz dni robocze ze świętami
Aby policzyć dni robocze ze świętami między dwiema datami w komórce A2 i B2, użyj następującej formuły:
=NETWORKDAYS.INTL(A2,B2)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Policz dni robocze bez świąt
Aby policzyć dni robocze ze świętami między dwiema datami w komórce A2 i B2 oraz z wyłączeniem świąt w zakresie D5:D9, użyj następującej formuły:
=DNI.ROBOCZE.NIESTAND(A5;B5,1;5;D9:DXNUMX)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Uwaga:
W powyższych formułach sobota i niedziela są traktowane jako weekend, jeśli masz różne dni weekendu, zmień argument [weekend] zgodnie z potrzebami.
1.17 Oblicz weekendy między dwiema datami
Jeśli chcesz policzyć liczbę weekendów między dwiema datami, funkcje SUMPRODUCT lub SUM mogą wyświadczyć Ci przysługę.
Aby policzyć weekendy (sobota i niedziela) między dwiema datami w komórce A12 i B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Naciśnij przycisk Wchodzę aby uzyskać wynik.
1.18 Oblicz konkretny dzień tygodnia między dwiema datami
Aby policzyć numer konkretnego dnia tygodnia, takiego jak poniedziałek, między dwiema datami, może pomóc kombinacja funkcji INT i WEEKDAY.
Komórki A15 i B15 to dwie daty, od których chcesz policzyć poniedziałek, użyj formuły w następujący sposób:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Zmień numer dnia tygodnia w funkcji WEEKDAY, aby zliczyć inny dzień tygodnia:
1 to niedziela, 2 to poniedziałek, 3 to wtorek, 4 to środa, 5 to czwartek, 6 to piątek, a 7 to sobota)
1.19 Oblicz pozostałe dni w miesiącu/roku
Czasami możesz chcieć poznać pozostałe dni w miesiącu lub roku na podstawie podanej daty, jak pokazuje poniższy zrzut ekranu:
Uzyskaj pozostałe dni w bieżącym miesiącu
Kliknij MIESIĄC poznać argument i użycie.
Aby pobrać pozostałe dni bieżącego miesiąca w komórce A2, użyj następującej formuły:
=EOMONTH(A2,0)-A2
Naciśnij przycisk Wchodzę i przeciągnij uchwyt automatycznego wypełniania, aby w razie potrzeby zastosować tę formułę do innych komórek.
Wskazówka: wyniki mogą być wyświetlane w formacie daty, wystarczy zmienić je w formacie ogólnym lub liczbowym.
Uzyskaj pozostałe dni w bieżącym roku
Aby uzyskać pozostałe dni bieżącego roku w komórce A2, użyj następującej formuły:
=DATE(YEAR(A2),12,31)-A2
Naciśnij przycisk Wchodzę i przeciągnij uchwyt automatycznego wypełniania, aby w razie potrzeby zastosować tę formułę do innych komórek.
1.21 Oblicz różnicę między dwoma czasami
Aby uzyskać różnicę między dwoma czasami, oto dwie proste formuły, które mogą ci pomóc.
Przypuśćmy, że komórki A2 i B2 zawierają oddzielnie czas_początkowy i czas_końcowy, używając następujących formuł:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Uwaga:
- Jeśli używasz end_time-start_time, możesz sformatować wynik do innego formatu czasu zgodnie z potrzebami w oknie dialogowym Formatowanie komórek.
- Jeśli używasz TEKST(czas_końca-pierwszy_godzina;"format_godziny"), wprowadź format godziny, w którym chcesz uzyskać wynik przedstawiony w formule, na przykład TEKST(czas_końca-pierwszy_godzina;"h") zwraca 16.
- Jeśli end_time jest mniejszy niż start_time, obie formuły zwracają wartości błędów. Aby rozwiązać ten problem, możesz dodać ABS na początku tych formuł, na przykład ABS(B2-A2), ABS(TEKST(B2-A2,"hh:mm:ss")), a następnie sformatować wynik jako czas.
1.22 Oblicz różnicę między dwoma czasami w godzinach/minutach/sekundach
Jeśli chcesz obliczyć różnicę między dwoma czasami w godzinach, minutach lub sekundach, jak pokazuje poniższy zrzut ekranu, postępuj zgodnie z tą częścią.
Uzyskaj różnicę godzin między dwoma razy
Aby uzyskać różnicę godzin między dwoma czasami w A5 i B5, użyj następującego wzoru:
=INT((B5-A5)*24)
Naciśnij przycisk Wchodzę klucz, a następnie sformatuj wynik formatu czasu jako ogólny lub liczbowy.
Jeśli chcesz uzyskać różnicę godzin dziesiętnych, użyj (end_time-start_time)*24.
Uzyskaj minutową różnicę między dwoma czasami
Aby uzyskać różnicę minut między dwoma czasami w A8 i B8, użyj następującego wzoru:
=INT((B8-A8)*1440)
Naciśnij przycisk Wchodzę klucz, a następnie sformatuj wynik formatu czasu jako ogólny lub liczbowy.
Jeśli chcesz uzyskać różnicę w minutach dziesiętnych, użyj (end_time-start_time)*1440.
Uzyskaj różnicę sekund między dwoma czasami
Aby uzyskać różnicę sekund między dwoma czasami w A5 i B5, użyj następującego wzoru:
=(B11-A11)*86400)
Naciśnij przycisk Wchodzę klucz, a następnie sformatuj wynik formatu czasu jako ogólny lub liczbowy.
1.23 Oblicz różnicę godzin tylko między dwoma czasami (nie przekraczaj 24 godzin)
Jeśli różnica między dwoma czasami nie przekracza 24 godzin, funkcja GODZINA może szybko uzyskać różnicę godzin między tymi dwoma czasami.
Kliknij GODZINA aby uzyskać więcej informacji na temat tej funkcji.
Aby uzyskać różnicę godzin między godzinami w komórce A14 i B14, użyj funkcji GODZINA w następujący sposób:
=HOUR(B14-A14)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Start_time musi być mniejszy niż end_time, w przeciwnym razie formuła zwróci #NUM! wartość błędu.
1.24 Oblicz różnicę minut tylko między dwoma czasami (nie przekraczaj 60 minut)
Funkcja MINUTY może szybko uzyskać jedyną różnicę minut między tymi dwoma czasami i zignorować godziny i sekundy.
Kliknij MINUTE aby uzyskać więcej informacji na temat tej funkcji.
Aby uzyskać tylko różnicę minut między godzinami w komórce A17 i B17, użyj funkcji MINUTA w następujący sposób:
=MINUTE(B17-A17)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Start_time musi być mniejszy niż end_time, w przeciwnym razie formuła zwróci #NUM! wartość błędu.
1.25 Oblicz różnicę sekund tylko między dwoma czasami (nie przekraczaj 60 sekund)
Funkcja SECOND może szybko uzyskać różnicę w sekundach między tymi dwoma czasami i zignorować godziny i minuty.
Kliknij DRUGIE aby uzyskać więcej informacji na temat tej funkcji.
Aby uzyskać tylko sekundy różnicy między czasami w komórce A20 i B20, użyj funkcji SECOND w następujący sposób:
=SECOND(B20-A20)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Start_time musi być mniejszy niż end_time, w przeciwnym razie formuła zwróci #NUM! wartość błędu.
1.26 Oblicz różnicę między dwoma czasami i godzinami, minutami, sekundami powrotu
Jeśli chcesz pokazać różnicę między dwoma czasami jako xx godzin xx minut xx sekund, użyj funkcji TEKST, jak pokazano poniżej:
Kliknij TEKST uświadomić sobie argumenty i zastosowanie tej funkcji.
Aby obliczyć różnicę między czasami w komórce A23 i B23, użyj następującej formuły:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Uwaga:
Ta formuła oblicza również tylko różnicę godzin nieprzekraczającą 24 godzin, a end_time musi być większy niż start_time, w przeciwnym razie zwraca #VALUE! wartość błędu.
1.27 Oblicz różnicę między dwiema datami i godzinami
Jeśli są dwa razy w formacie mm/dd/rrrr gg:mm:ss, aby obliczyć różnicę między nimi, możesz użyć jednej z poniższych formuł, jeśli potrzebujesz.
Uzyskaj różnicę czasu między dwiema datami i zwróć wynik w formacie gg:mm:ss
Weź dwie daty i godziny w komórce A2 i B2 jako instancję, użyj następującej formuły:
=B2-A2
Naciśnij przycisk Wchodzę klucz, zwracając wynik w formacie daty i godziny, a następnie sformatuj ten wynik jako [h]: mm: ss w kategorii niestandardowej pod Numer w zakładce Formatowanie komórek dialog.
Uzyskaj różnicę między dwoma datami i dniami powrotu, godzinami, minutami, sekundami
Weź dwie daty i godziny w komórce A5 i B5 jako instancję, użyj następującej formuły:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Uwaga: w obu formułach data_końcowa musi być większa niż data_początkowa, w przeciwnym razie formuły zwracają wartości błędów.
1.28 Oblicz różnicę czasu w milisekundach
Po pierwsze, musisz wiedzieć, jak sformatować komórkę, aby wyświetlała milisekundy:
Wybierz komórki, które chcesz wyświetlić w milisekundach i zaznacz je w prawo Formatowanie komórek włączyć Formatowanie komórek dialog, wybierz Zamówienia Indywidualne Kategoria Lista w zakładce Numer i wpisz to hh: mm: ss.000 w polu tekstowym.
Użyj wzoru:
Tutaj, aby obliczyć różnicę między dwoma czasami w komórce A8 i B8, użyj formuły jako:
=ABS(B8-A8)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
1.29 Oblicz godziny pracy między dwiema datami z wyłączeniem weekendów
Czasami może być konieczne policzenie godzin pracy między dwiema datami, z wyłączeniem weekendów (sobota i niedziela).
Tutaj godziny pracy są ustalone na 8 godzin każdego dnia, a aby obliczyć godziny pracy między dwiema datami podanymi w komórkach A16 i B16, użyj następującego wzoru:
=NETWORKDAYS(A16,B16) * 8
Naciśnij przycisk Wchodzę klucz, a następnie sformatuj wynik jako ogólny lub liczbowy.
Aby uzyskać więcej przykładów obliczania godzin pracy między dwiema datami, odwiedź Uzyskaj godziny pracy między dwiema datami w programie Excel
Jeśli Kutools dla programu Excel zainstalowany w programie Excel, 90 procent obliczeń różnicy daty i godziny można szybko rozwiązać bez zapamiętywania żadnych formuł.
1.31 Oblicz różnicę między dwiema datami za pomocą Data & Time Helper
Aby obliczyć różnicę między dwiema datami w programie Excel, wystarczy Pomocnik daty i godziny wystarczy.
1. Wybierz komórkę, w której umieszczasz obliczony wynik i kliknij Kutools > Pomocnik formuły > Pomocnik daty i godziny.
2. W poppingu Pomocnik daty i godziny w oknie dialogowym postępuj zgodnie z poniższymi ustawieniami:
- Kontrola Różnica opcja;
- Wybierz datę rozpoczęcia i datę zakończenia w Wprowadzanie argumentów sekcji, możesz również bezpośrednio wprowadzić datę i godzinę ręcznie w polu wprowadzania lub kliknąć ikonę kalendarza, aby wybrać datę;
- Wybierz typ wyniku wyjściowego z listy rozwijanej;
- Wyświetl podgląd wyniku w Wynik
3. kliknij Ok. Obliczony wynik jest wyprowadzany i przeciągnij uchwyt autouzupełniania nad komórkami, które również musisz obliczyć.
Wskazówka:
Jeśli chcesz uzyskać różnicę między dwiema datami i pokazać wynik jako dni, godziny i minuty za pomocą Kutools for Excel, wykonaj następujące czynności:
Wybierz komórkę, w której chcesz umieścić wynik i kliknij Kutools > Pomocnik formuły > Data i czas > Policz dni, godziny i minuty między dwiema datami.
Następnie w Pomocnik formuł w oknie dialogowym, określ datę rozpoczęcia i datę zakończenia, a następnie kliknij Ok.
Wynik różnicy zostanie pokazany w dniach, godzinach i minutach.
Kliknij Pomocnik daty i godziny aby dowiedzieć się więcej o korzystaniu z tej funkcji.
Kliknij Kutools dla programu Excel poznać wszystkie funkcje tego dodatku.
Kliknij Darmowe pobieranie aby uzyskać 30-dniową bezpłatną wersję próbną Kutools dla Excel
Jeśli chcesz szybko policzyć weekend, dni robocze lub określony dzień tygodnia między dwiema datami, Kutools dla programu Excel Pomocnik formuły grupa może Ci pomóc.
1. Wybierz komórkę, w której zostanie umieszczony obliczony wynik, kliknij Kutools > Statystyczny > Liczba dni wolnych od pracy między dwiema datami/Liczba dni roboczych między dwiema datami/Policz numer konkretnego dnia tygodnia.
2. W wyskakującym okienku Pomocnik formuł w oknie dialogowym określ datę rozpoczęcia i datę zakończenia, jeśli aplikujesz Policz numer konkretnego dnia tygodnia, musisz również określić dzień tygodnia.
Aby policzyć konkretny dzień tygodnia, możesz odwołać się do notatki, aby użyć 1-7, aby wskazać niedzielę-sobota.
3. kliknij Ok, a następnie przeciągnij uchwyt autouzupełniania nad komórkami, które w razie potrzeby muszą policzyć liczbę weekendów/dni roboczych/określonych dni tygodnia.
Kliknij Kutools dla programu Excel poznać wszystkie funkcje tego dodatku.
Kliknij Darmowe pobieranie aby uzyskać 30-dniową bezpłatną wersję próbną Kutools dla Excel
2. Dodaj lub odejmij datę i godzinę
Oprócz obliczania różnicy między dwiema datami, dodawanie lub odejmowanie jest również normalnym obliczaniem daty i godziny w programie Excel. Na przykład możesz chcieć uzyskać termin płatności na podstawie daty produkcji i liczby dni przechowywania produktu.
2.11 Dodaj lub odejmij dni do daty
Aby dodać lub odjąć określoną liczbę dni do daty, oto dwie różne metody.
Zakładając dodanie 21 dni do daty w komórce A2, wybierz jedną z poniższych metod, aby to rozwiązać,
Metoda 1 data + dni
Wybierz komórkę i wpisz formułę:
=A+21
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Jeśli chcesz odjąć 21 dni, zmień znak plus (+) na znak minus (-).
Metoda 2 wklej specjalnie
1. Wpisz liczbę dni, które chcesz dodać w komórce, zakładając w komórce C2, a następnie naciśnij Ctrl + C skopiować.
2. Następnie wybierz daty, które chcesz dodać 21 dni, kliknij prawym przyciskiem myszy, aby wyświetlić menu kontekstowe i wybierz Wklej specjalnie....
3. w wklej specjalnie okno dialogowe, sprawdź Dodaj opcja (Jeśli chcesz odjąć dni, zaznacz Odejmować opcja). Kliknij OK.
4. Teraz oryginalne daty zmienią się na 5-cyfrowe liczby, sformatuj je jako daty.
2.12 Dodaj lub odejmij miesiące do daty
Aby dodać lub odjąć miesiące do daty, można użyć funkcji EDATE.
Kliknij EDATA przestudiować jego argumenty i użycie.
Załóżmy, że dodamy 6 miesięcy do daty w komórce A2, użyj formuły w następujący sposób:
=EDATE(A2,6)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Jeśli chcesz odjąć 6 miesięcy od daty, zmień 6 na -6.
2.13 Dodaj lub odejmij lata do daty
Aby dodać lub odjąć n lat do daty, można użyć formuły łączącej funkcje DATA, ROK, MIESIĄC i DZIEŃ.
Przypuśćmy, że do daty w komórce A3 dodamy 2 lata, użyj następującej formuły:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Jeśli chcesz odjąć 3 lata od daty, zmień 3 na -3.
2.14 Dodaj lub odejmij tygodnie do daty
Aby dodać lub odjąć tygodnie do daty, ogólna formuła to
Przypuśćmy, że do daty w komórce A4 dodamy 2 tygodnie, użyj następującej formuły:
=A2+4*7
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Jeśli chcesz odjąć 4 tygodnie od daty, zmień znak plus (+) na znak minus (-).
2.15 Dodaj lub odejmij dni robocze, w tym lub bez świąt
W tej sekcji opisano, jak używać funkcji WORKDAY do dodawania lub odejmowania dni roboczych do podanej daty z wyłączeniem świąt lub z uwzględnieniem świąt.
Odwiedzić DZIEŃ ROBOCZY aby poznać więcej szczegółów na temat jego argumentów i użycia.
Dodaj dni robocze, w tym święta
W komórce A2 znajduje się data, której używasz, w komórce B2 zawiera liczbę dni, które chcesz dodać, użyj formuły w następujący sposób:
=WORKDAY(A2,B2)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Dodaj dni robocze z wyłączeniem świąt
W komórce A5 jest używana data, w komórce B5 zawiera liczbę dni, które chcesz dodać, w zakresie D5: D8 wymienia się święta, użyj formuły w następujący sposób:
=WORKDAY(A5,B5,D5:D8)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Uwaga:
Funkcja DZIEŃ.ROBOCZY przyjmuje sobotę i niedzielę jako weekendy, jeśli weekendy są w sobotę i niedzielę, możesz zastosować funkcję DZIEŃ.ROBOCZY.INTL, która obsługuje określanie weekendów.
Odwiedzić DZIEŃ.ROBOCZY.INTL by uzyskać więcej szczegółów.
Jeśli chcesz odjąć dni robocze od daty, po prostu zmień liczbę dni na ujemną w formule.
2.16 Dodaj lub odejmij określony rok, miesiąc, dni do daty
Jeśli chcesz dodać określony rok, miesiąc dni do daty, formuła łącząca funkcję DATA, ROK, MIESIĄC i DNI może wyświadczyć Ci przysługę.
Aby dodać 1 rok, 2 miesiące i 30 dni do daty w A11, użyj następującej formuły:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Jeśli chcesz odjąć, zmień wszystkie znaki plus (+) na znaki minus (-).
2.21 Dodaj lub odejmij godziny/minuty/sekundy do daty i godziny
Poniżej przedstawiono niektóre formuły dodawania lub odejmowania godzin, minut lub sekund do daty i godziny.
Dodaj lub odejmij godziny do daty i godziny
Zakładając, że dodamy 3 godziny do daty i godziny (również może to być godzina) w komórce A2, użyj następującej formuły:
=A2+3/24
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Dodaj lub odejmij godziny do daty i godziny
Zakładając, że dodasz 15 minut do daty i godziny (również może być godziną) w komórce A5, użyj następującej formuły:
=A2+15/1440
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Dodaj lub odejmij godziny do daty i godziny
Zakładając, że dodasz 20 sekund do daty i godziny (również może to być godzina) w komórce A8, użyj następującej formuły:
=A2+20/86400
Naciśnij przycisk Wchodzę aby uzyskać wynik.
2.22 Suma razy w ciągu 24 godzin
Przypuśćmy, że istnieje tabela Excela rejestrująca czas pracy wszystkich sztabów w ciągu tygodnia, aby zsumować całkowity czas pracy do obliczania płatności, możesz użyć SUMA(zakres) aby uzyskać wynik. Ale ogólnie rzecz biorąc, zsumowany wynik zostanie pokazany jako czas nieprzekraczający 24 godzin, jak pokazuje poniższy zrzut ekranu, jak uzyskać poprawny wynik?
Właściwie wystarczy sformatować wynik jako [gg]:mm:ss.
Kliknij prawym przyciskiem myszy komórkę wyników, wybierz Formatowanie komórek w menu kontekstowym oraz w popping Formatowanie komórek dialog, wybierz Zamówienia Indywidualne z listy i wpisz [gg]:mm:ss w polu tekstowym w prawej sekcji, kliknij OK.
Zsumowany wynik zostanie wyświetlony poprawnie.
2.23 Dodaj godziny pracy do daty z wyłączeniem weekendów i świąt
Tutaj przedstawiono długą formułę uzyskiwania daty końcowej na podstawie dodania określonej liczby godzin pracy do daty początkowej, z wyłączeniem weekendów (sobota i niedziela) oraz świąt.
W tabeli programu Excel A11 zawiera datę i godzinę rozpoczęcia, a B11 zawiera godziny pracy, w komórkach E11 i E13 są godziny rozpoczęcia i zakończenia pracy, a komórka E15 zawiera urlop, który zostanie wykluczony.
Użyj poniższego wzoru:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Jeśli masz zainstalowane Kutools for Excel, tylko jedno narzędzie – Pomoc dotycząca daty i godzinyr potrafi rozwiązać większość obliczeń dotyczących dodawania i odejmowania daty i godziny.
1. Kliknij komórkę, do której chcesz wyprowadzić wynik, i zastosuj to narzędzie, klikając Kutools > Pomocnik formuły > Pomocnik daty i godziny.
2. w Pomocnik daty i godziny okno dialogowe, sprawdź Dodaj opcja lub Odejmować opcję według potrzeb, a następnie wybierz komórkę lub bezpośrednio wpisz datę i godzinę, której chcesz użyć Wprowadzanie argumentów sekcji, a następnie określ lata, miesiące, tygodnie, dni, godziny, minuty i sekundy, które chcesz dodać lub odjąć, a następnie kliknij Ok. Zobacz zrzut ekranu:
Możesz wyświetlić podgląd obliczonego wyniku w Wynik 
Teraz wynik jest wyprowadzany, przeciągnij automatyczny uchwyt na inne komórki, aby uzyskać wyniki.
Kliknij Pomocnik daty i godziny aby dowiedzieć się więcej o korzystaniu z tej funkcji.
Kliknij Kutools dla programu Excel poznać wszystkie funkcje tego dodatku.
Kliknij Pobieranie opłat aby uzyskać 30-dniową bezpłatną wersję próbną Kutools dla Excel
2.41 Zaznacz lub zaznacz, czy data wygasła
Jeśli istnieje lista wygasłych dat produktów, możesz sprawdzić i podświetlić daty, które wygasły na podstawie dzisiejszego dnia, jak pokazuje poniższy zrzut ekranu.
Właściwie to Formatowanie warunkowe szybko poradzi sobie z tą pracą.
1. Wybierz daty, które chcesz sprawdzić, a następnie kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.
2. w Nowa reguła formatowania dialog, wybierz Użyj formuły, aby określić, które komórki należy sformatować Wybierz typ reguły sekcję i typ =B2 w polu wprowadzania (B2 to pierwsza data, którą chcesz sprawdzić) i kliknij utworzony wyskoczyć Formatowanie komórek w oknie dialogowym, a następnie wybierz inne formatowanie, aby przetrzymać wygasłe daty, zgodnie z potrzebami. Kliknij OK > OK.
2.42 Zwróć koniec bieżącego miesiąca/pierwszy dzień następnego miesiąca/a>
Daty wygaśnięcia niektórych produktów przypadają na koniec miesiąca produkcji lub pierwszego dnia następnego miesiąca produkcji, aby szybko wypisać wygasłe daty na podstawie daty produkcji, postępuj zgodnie z tą częścią.
Pobierz koniec bieżącego miesiąca
Oto data produkcji w komórce B13, użyj następującej formuły:
=EOMONTH(B13,0)
Naciśnij przycisk Wchodzę aby uzyskać wynik.
Zdobądź pierwszy dzień następnego miesiąca
Oto data produkcji w komórce B18, użyj następującej formuły:
=EOMONTH(B18,0)+1
Naciśnij przycisk Wchodzę aby uzyskać wynik.
3. Oblicz wiek
W tej sekcji przedstawia metody rozwiązywania problemu obliczania wieku na podstawie podanej daty lub numeru seryjnego.
3.11 Oblicz wiek na podstawie podanej daty urodzenia
Uzyskaj wiek w liczbie dziesiętnej na podstawie daty urodzenia
Kliknij CZĘŚĆ ROKU aby uzyskać szczegółowe informacje na temat jego argumentów i użycia.
Na przykład, aby uzyskać wiek na podstawie listy dat urodzenia w kolumnie B2:B9, użyj następującego wzoru:
=YEARFRAC(B2,TODAY())
Naciśnij przycisk Wchodzę , a następnie przeciągnij uchwyt autouzupełniania w dół, aż zostaną obliczone wszystkie przedziały wiekowe.
Wskazówka:
1) Możesz określić miejsce dziesiętne zgodnie z potrzebami w Formatowanie komórek dialog.
2) Jeśli chcesz obliczyć wiek w określonej dacie na podstawie podanej daty urodzenia, zmień DZIŚ() na konkretną datę umieszczoną w cudzysłowach podwójnych, np. =YEARFRAC(B2,"1/1/2021")
3) Jeśli chcesz uzyskać wiek na przyszły rok na podstawie daty urodzenia, po prostu dodaj 1 do formuły, na przykład =YEARFRAC(B2,TODAY())+1.
Uzyskaj wiek w liczbie całkowitej na podstawie daty urodzenia
Kliknij JEŻELI DATA aby uzyskać szczegółowe informacje na temat jego argumentów i użycia.
Korzystając z powyższego przykładu, aby uzyskać wiek na podstawie dat urodzenia z listy w B2:B9, użyj wzoru w następujący sposób:
=DATEDIF(B2,TODAY(),"y")
Naciśnij przycisk Wchodzę , a następnie przeciągnij uchwyt autouzupełniania w dół, aż zostaną obliczone wszystkie przedziały wiekowe.
Wskazówka:
1) Jeśli chcesz obliczyć wiek w określonej dacie na podstawie podanej daty urodzenia, zmień DZIŚ() na konkretną datę umieszczoną w cudzysłowach podwójnych, np. =DAT.JEŻELI(B2,"1/1/2021","y") .
2) Jeśli chcesz uzyskać wiek na przyszły rok na podstawie daty urodzenia, po prostu dodaj 1 w formule, na przykład =DAT.DEF(B2,DZIŚ(),"y")+1.
3.12 Oblicz wiek w formacie lat, miesięcy i dni według daty urodzin
Jeśli chcesz obliczyć wiek na podstawie podanej daty urodzenia i pokazać wynik jako xx lat, xx miesięcy, xx dni, jak pokazuje poniższy zrzut ekranu, oto długa formuła, która może ci pomóc.
Aby uzyskać wiek w latach, miesiącach i dniach na podstawie daty urodzenia w komórce B12, użyj następującego wzoru:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Naciśnij przycisk Wchodzę aby uzyskać wiek, a następnie przeciągnij uchwyt autouzupełniania w dół do innych komórek.
Wskazówka:
Jeśli chcesz obliczyć wiek w określonej dacie na podstawie podanej daty urodzenia, zmień DZIŚ() na konkretną datę umieszczoną w cudzysłowach, np. = =DAT.JEŻELI(B12,"1/1/2021","Y")& " Lata, "&DATEDIF(B12,"1/1/2021","YM")&"Miesiące,"&DATADIF(B12;"1/1/2021","MD")&"Dni".
3.13 Oblicz wiek według daty urodzenia przed 1
W programie Excel daty sprzed 1 stycznia 1 nie można wprowadzić jako daty i godziny ani poprawnie obliczyć. Ale jeśli chcesz obliczyć wiek sławnej osoby na podstawie podanej daty urodzenia (przed 1900/1) i daty śmierci, tylko kod VBA może ci pomóc.
1. naciśnij inny + F11 klucze do włączenia Microsoft Visual Basic for Applications okno i kliknij wstawka kartę i wybierz Moduł stworzyć nowy moduł.
2. Następnie skopiuj i wklej poniższy kod do nowego modułu.
VBA: Oblicz wiek przed 1
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Zapisz kod i wróć do arkusza i wybierz komórkę, w której chcesz umieścić obliczony wiek, wpisz =AgeFunc(data urodzenia,data śmierci), w tym przypadku =FunkcjaWiek(B22;C22), naciśnij klawisz Enter, aby uzyskać wiek. W razie potrzeby użyj uchwytu automatycznego wypełniania, aby zastosować tę formułę do innych komórek.
Jeśli Kutools dla programu Excel zainstalowany w programie Excel, możesz zastosować Pomocnik daty i godziny narzędzie do obliczania wieku.
1. Wybierz komórkę, w której chcesz umieścić obliczony wiek i kliknij Kutools > Pomocnik formuły > Pomocnik daty i godziny.
2. w Pomocnik daty i godziny dialog,
- 1) Sprawdź Wiek opcja;
- 2) Wybierz komórkę daty urodzenia lub bezpośrednio wprowadź datę urodzenia lub kliknij ikonę kalendarza, aby wybrać datę urodzenia;
- 3) Wybierz Dziś opcja jeśli chcesz obliczyć aktualny wiek, wybierz Określona data opcję i wprowadź datę, jeśli chcesz obliczyć wiek w przeszłości lub w przyszłości;
- 4) Określ typ wyjścia z listy rozwijanej;
- 5) Wyświetl podgląd wyniku wyjściowego. Kliknij Ok.
Kliknij Pomocnik daty i godziny aby dowiedzieć się więcej o korzystaniu z tej funkcji.
Kliknij Kutools dla programu Excel poznać wszystkie funkcje tego dodatku.
Kliknij Darmowe pobieranie aby uzyskać 30-dniową bezpłatną wersję próbną Kutools dla Excel
3.31 Uzyskaj urodziny z numeru identyfikacyjnego
Jeśli istnieje lista numerów identyfikacyjnych, które używają pierwszych 6 cyfr do zapisania daty urodzenia, np. 920315330, oznacza to, że data urodzenia to 03, jak szybko przenieść datę urodzenia do innej kolumny?
Teraz weźmy listę numerów identyfikacyjnych zaczynających się w komórce C2 jako instancję i użyjmy następującej formuły:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Naciśnij przycisk Wchodzę klucz. Następnie przeciągnij uchwyt autouzupełniania w dół, aby uzyskać inne wyniki.
Uwaga:
W formule możesz zmienić odniesienie do swoich potrzeb. Na przykład, jeśli numer ID pokazany jako 13219920420392, data urodzenia to 04, możesz zmienić formułę na =MID(C20)&"/"&MID(C1992)&"/ "&MID(C2,8,2), aby uzyskać poprawny wynik.
3.32 Oblicz wiek na podstawie numeru identyfikacyjnego
Jeśli istnieje lista numerów identyfikacyjnych, które używają pierwszych 6 cyfr do zarejestrowania daty urodzenia, na przykład 920315330, oznacza to, że data urodzenia to 03, jak szybko obliczyć wiek na podstawie każdego numeru identyfikacyjnego w programie Excel?
Teraz weźmy listę numerów identyfikacyjnych zaczynających się w komórce C2 jako instancję i użyjmy następującej formuły:
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")
Naciśnij przycisk Wchodzę klucz. Następnie przeciągnij uchwyt autouzupełniania w dół, aby uzyskać inne wyniki.
Uwaga:
W tej formule, jeśli rok jest krótszy niż rok bieżący, rok zostanie uznany za rozpoczynający się od 20, np. 200203943 zostanie uznany za rok 2020; jeśli rok jest większy niż rok bieżący, rok zostanie uznany za rozpoczynający się od 19, na przykład 920420392 zostanie uznany za rok 1992.
Więcej samouczków Excela:
Połącz wiele skoroszytów/arkuszy roboczych w jeden
Ten samouczek, zawierający listę prawie wszystkich scenariuszy łączenia, z którymi możesz się zmierzyć, i zapewniających względne profesjonalne rozwiązania.
Podziel komórki tekstowe, liczbowe i dat (oddziel na wiele kolumn)
Ten samouczek jest podzielony na trzy części: podzielone komórki tekstowe, podzielone komórki liczbowe i podzielone komórki daty. Każda część zawiera różne przykłady, które pomogą Ci dowiedzieć się, jak poradzić sobie z zadaniem podziału, gdy napotkasz ten sam problem.
Połącz zawartość wielu komórek bez utraty danych w programie Excel
Ten samouczek zawęża wyodrębnianie do określonej pozycji w komórce i zbiera różne metody, aby pomóc wyodrębnić tekst lub liczby z komórki według określonej pozycji w programie Excel.
Porównaj dwie kolumny pod kątem dopasowań i różnic w programie Excel
W tym artykule opisano większość możliwych scenariuszy porównywania dwóch kolumn, które możesz napotkać, i mam nadzieję, że może ci to pomóc.
Najlepsze narzędzia biurowe
Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%
- Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
- Scal komórki / wiersze / kolumny i przechowywanie danych; Podziel zawartość komórek; Połącz zduplikowane wiersze i sumę / średnią... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
- Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
- Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
- Ulubione i szybkie wstawianie formuł, Zakresy, wykresy i obrazy; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
- Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
- Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
- Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
- Grupowanie tabel przestawnych według numer tygodnia, dzień tygodnia i więcej ... Pokaż odblokowane, zablokowane komórki w różnych kolorach; Podświetl komórki, które mają formułę / nazwę...
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
