Hướng dẫn Excel: Tính toán theo ngày tháng (tính toán chênh lệch, tuổi, cộng / trừ)
Trong Excel, tính toán ngày giờ được sử dụng thường xuyên, chẳng hạn như tính toán chênh lệch giữa hai ngày / giờ, cộng hoặc trừ ngày giờ, lấy tuổi dựa trên ngày sinh, v.v. Ở đây trong hướng dẫn này, nó liệt kê hầu hết các kịch bản về tính toán ngày giờ và cung cấp các phương pháp liên quan cho bạn.
Trong hướng dẫn này, tôi tạo một số ví dụ để giải thích các phương pháp, bạn có thể thay đổi các tham chiếu cần thiết khi bạn sử dụng mã hoặc công thức VBA bên dưới
1. Tính toán chênh lệch giữa hai ngày / giờ
Tính toán chênh lệch giữa hai ngày hoặc hai thời điểm có thể là vấn đề bình thường nhất của tính toán ngày giờ mà bạn gặp trong công việc Excel hàng ngày. Các ví dụ được cung cấp cho người theo dõi dưới đây có thể giúp bạn nâng cao hiệu quả khi bạn gặp phải các vấn đề tương tự.
1.11 Tính chênh lệch giữa hai ngày theo ngày / tháng / năm / tuần
Hàm DATEDIF trong Excel có thể được sử dụng để nhanh chóng tính toán sự khác biệt giữa hai ngày trong ngày, tháng, năm và tuần.
Bấm để biết thêm chi tiết về NGÀY chức năng
Số ngày chênh lệch giữa hai ngày
Để nhận được sự khác biệt về ngày giữa hai ngày trong ô A2 và B2, vui lòng sử dụng công thức như sau
=DATEDIF(A2,B2,"d")
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Số tháng chênh lệch giữa hai ngày
Để nhận được sự khác biệt về tháng giữa hai ngày trong ô A5 và B5, vui lòng sử dụng công thức như sau
=DATEDIF(A5,B5,"m")
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Chênh lệch năm giữa hai ngày
Để nhận được sự khác biệt về năm giữa hai ngày trong ô A8 và B8, vui lòng sử dụng công thức như sau
=DATEDIF(A8,B8,"y")
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Số tuần chênh lệch giữa hai ngày
Để nhận được chênh lệch tuần giữa hai ngày trong ô A11 và B11, vui lòng sử dụng công thức như sau
=DATEDIF(A11,B11,"d")/7
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Lưu ý:
1) Khi bạn sử dụng công thức trên để tính chênh lệch tuần, nó có thể trả về kết quả ở định dạng ngày tháng, bạn cần định dạng kết quả thành số chung hoặc số khi bạn cần.
2) Khi bạn sử dụng công thức trên để nhận chênh lệch tuần, nó có thể trở về số thập phân, nếu bạn muốn nhận số tuần là số nguyên, bạn có thể thêm hàm ROUNDDOWN vào trước như hình bên dưới để nhận chênh lệch số tuần:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Tính tháng bỏ qua năm và ngày giữa hai ngày
Nếu bạn chỉ muốn tính toán chênh lệch tháng bỏ qua năm và ngày giữa hai ngày như ảnh chụp màn hình bên dưới hiển thị, thì đây là công thức có thể giúp bạn.
=DATEDIF(A2,B2,"ym")
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
A2 là ngày bắt đầu và B2 là ngày kết thúc.
1.13 Tính ngày bỏ qua năm và tháng giữa hai ngày
Nếu bạn chỉ muốn tính toán chênh lệch ngày bỏ qua năm và tháng giữa hai ngày như ảnh chụp màn hình bên dưới, đây là công thức có thể giúp bạn.
=DATEDIF(A5,B5,"md")
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
A5 là ngày bắt đầu và B5 là ngày kết thúc.
1.14 Tính toán chênh lệch giữa hai ngày và năm, tháng và ngày trở lại
Nếu bạn muốn nhận sự khác biệt giữa hai ngày và trả về xx năm, xx tháng và xx ngày như ảnh chụp màn hình bên dưới hiển thị, đây cũng là một công thức được cung cấp.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
A8 là ngày bắt đầu và B8 là ngày kết thúc.
1.15 Tính toán chênh lệch giữa một ngày và hôm nay
Để tự động tính toán sự khác biệt giữa một ngày và hôm nay, chỉ cần thay đổi end_date trong các công thức trên thành TODAY (). Đây là một ví dụ để tính toán chênh lệch ngày giữa ngày trước và ngày hôm nay.
=DATEDIF(A11,TODAY(),"d")
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Chú thích: nếu bạn muốn tính toán sự khác biệt giữa ngày trong tương lai và ngày hôm nay, hãy thay đổi start_date thành hôm nay và lấy ngày trong tương lai là end_date như sau:
=DATEDIF(TODAY(),A14,"d")
Lưu ý rằng start_date phải nhỏ hơn end_date trong hàm DATEDIF, nếu không, nó sẽ trở về #NUM! giá trị lỗi.
1.16 Tính toán ngày làm việc có hoặc không có ngày nghỉ giữa hai ngày
Đôi khi, bạn có thể cần đếm số ngày làm việc có hoặc không có ngày nghỉ giữa hai ngày nhất định.
Trong phần này, bạn sẽ sử dụng hàm NETWORKDAYS.INTL:
Nhấp chuột NETWORKDAYS.INTL để biết các đối số và cách sử dụng của nó.
Đếm ngày làm việc với ngày lễ
Để đếm các ngày làm việc có ngày nghỉ giữa hai ngày trong ô A2 và B2, vui lòng sử dụng công thức như sau:
=NETWORKDAYS.INTL(A2,B2)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Đếm ngày làm việc không có ngày nghỉ
Để đếm các ngày làm việc có ngày nghỉ giữa hai ngày trong ô A2 và B2 và loại trừ các ngày lễ trong phạm vi D5: D9, vui lòng sử dụng công thức như sau:
= NETWORKDAYS.INTL (A5, B5,1, D5: D9)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Lưu ý:
Trong các công thức trên, chúng lấy Thứ Bảy và Chủ Nhật làm cuối tuần, nếu bạn có các ngày cuối tuần khác nhau, vui lòng thay đổi đối số [cuối tuần] khi bạn cần.
1.17 Tính toán các ngày cuối tuần giữa hai ngày
Nếu bạn muốn đếm số ngày cuối tuần giữa hai ngày, các hàm SUMPRODUCT hoặc SUM có thể giúp bạn.
Để đếm các ngày cuối tuần (Thứ Bảy và Chủ Nhật) giữa hai ngày trong ô A12 và B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
1.18 Tính toán ngày trong tuần cụ thể giữa hai ngày
Để đếm số của một ngày trong tuần cụ thể, chẳng hạn như Thứ Hai giữa hai ngày, sự kết hợp của các hàm INT và WEEKDAY có thể giúp bạn.
Ô A15 và B15 là hai ngày bạn muốn đếm giữa các ngày Thứ Hai, vui lòng sử dụng công thức như sau:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Thay đổi số ngày trong tuần trong hàm WEEKDAY để đếm một ngày trong tuần khác:
1 là Chủ Nhật, 2 là Thứ Hai, 3 là Thứ Ba, 4 là Thứ Tư, 5 là Thứ Năm, 6 là Thứ Sáu và 7 là Thứ Bảy)
1.19 Tính số ngày còn lại trong tháng / năm
Đôi khi, bạn có thể muốn biết những ngày còn lại trong tháng hoặc năm dựa trên ngày được cung cấp như ảnh chụp màn hình bên dưới cho thấy:
Nhận các ngày còn lại trong tháng hiện tại
Nhấp chuột KINH TẾ để biết đối số và cách sử dụng.
Để lấy các ngày còn lại của tháng hiện tại trong ô A2, vui lòng sử dụng công thức như sau:
=EOMONTH(A2,0)-A2
Ấn Bản đăng ký hạng mục thi và kéo chốt tự động điền để áp dụng công thức này cho các ô khác nếu cần.
Mẹo: kết quả có thể được hiển thị dưới dạng định dạng ngày tháng, chỉ cần thay đổi chúng thành định dạng chung hoặc định dạng số.
Nhận những ngày còn lại trong năm hiện tại
Để lấy các ngày còn lại của năm hiện tại trong ô A2, vui lòng sử dụng công thức như sau:
=DATE(YEAR(A2),12,31)-A2
Ấn Bản đăng ký hạng mục thi và kéo chốt tự động điền để áp dụng công thức này cho các ô khác nếu cần.
1.21 Tính chênh lệch giữa hai thời điểm
Để có được sự khác biệt giữa hai thời điểm, đây là hai công thức đơn giản có thể giúp bạn.
Giả sử trong ô A2 và B2 chứa start_time và end_time riêng biệt, sử dụng các công thức như sau:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Lưu ý:
- Nếu bạn sử dụng end_time-start_time, bạn có thể định dạng kết quả sang định dạng thời gian khác khi bạn cần trong hộp thoại Định dạng ô.
- Nếu bạn sử dụng TEXT (end_time-first_time, "time_format"), hãy nhập định dạng thời gian mà bạn muốn kết quả hiển thị trong công thức, chẳng hạn như TEXT (end_time-first_time, "h") trả về 16.
- Nếu end_time nhỏ hơn start_time, cả hai công thức đều trả về giá trị lỗi. Để giải quyết vấn đề này, bạn có thể thêm ABS vào trước các công thức này, chẳng hạn như ABS (B2-A2), ABS (TEXT (B2-A2, "hh: mm: ss")), sau đó định dạng kết quả theo thời gian.
1.22 Tính chênh lệch giữa hai thời điểm theo giờ / phút / giây
Nếu bạn muốn tính chênh lệch giữa hai thời điểm theo giờ, phút hoặc giây như ảnh chụp màn hình bên dưới hiển thị, hãy làm theo phần này.
Nhận chênh lệch giờ giữa hai thời điểm
Để nhận được chênh lệch giờ giữa hai thời điểm ở A5 và B5, vui lòng sử dụng công thức như sau:
=INT((B5-A5)*24)
Ấn Bản đăng ký hạng mục thi , sau đó định dạng kết quả định dạng thời gian là chung hoặc số.
Nếu bạn muốn nhận chênh lệch giờ thập phân, hãy sử dụng (end_time-start_time) * 24.
Nhận số phút chênh lệch giữa hai thời điểm
Để nhận được sự chênh lệch số phút giữa hai thời điểm ở A8 và B8, vui lòng sử dụng công thức như sau:
=INT((B8-A8)*1440)
Ấn Bản đăng ký hạng mục thi , sau đó định dạng kết quả định dạng thời gian là chung hoặc số.
Nếu bạn muốn nhận chênh lệch phút thập phân, hãy sử dụng (end_time-start_time) * 1440.
Nhận chênh lệch giây giữa hai thời điểm
Để nhận được sự chênh lệch giây giữa hai thời điểm trong A5 và B5, vui lòng sử dụng công thức như sau:
=(B11-A11)*86400)
Ấn Bản đăng ký hạng mục thi , sau đó định dạng kết quả định dạng thời gian là chung hoặc số.
1.23 Chỉ tính chênh lệch giờ giữa hai thời điểm (không quá 24 giờ)
Nếu chênh lệch giữa hai thời điểm không vượt quá 24 giờ, chức năng HOUR có thể nhanh chóng nhận được chênh lệch giờ giữa hai thời điểm này.
Nhấp chuột Giờ để biết thêm chi tiết về chức năng này.
Để nhận được chênh lệch giờ giữa các thời gian trong ô A14 và B14, vui lòng sử dụng hàm HOUR như sau:
=HOUR(B14-A14)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Start_time phải nhỏ hơn end_time, nếu không, công thức trả về #NUM! giá trị lỗi.
1.24 Chỉ tính chênh lệch số phút giữa hai thời điểm (không quá 60 phút)
Chức năng MINUTE có thể nhanh chóng nhận được sự khác biệt phút duy nhất giữa hai thời điểm này và bỏ qua giờ và giây.
Nhấp chuột PHÚT để biết thêm chi tiết về chức năng này.
Để chỉ nhận được sự khác biệt phút giữa thời gian trong ô A17 và B17, vui lòng sử dụng hàm MINUTE như sau:
=MINUTE(B17-A17)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Start_time phải nhỏ hơn end_time, nếu không, công thức trả về #NUM! giá trị lỗi.
1.25 Chỉ tính chênh lệch giây giữa hai thời điểm (không quá 60 giây)
Chức năng SECOND có thể nhanh chóng nhận được sự khác biệt giây duy nhất giữa hai thời điểm này và bỏ qua giờ và phút.
Nhấp chuột THỨ HAI để biết thêm chi tiết về chức năng này.
Để chỉ nhận được sự chênh lệch giây giữa các thời gian trong ô A20 và B20, vui lòng sử dụng hàm SECOND như sau:
=SECOND(B20-A20)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Start_time phải nhỏ hơn end_time, nếu không, công thức trả về #NUM! giá trị lỗi.
1.26 Tính chênh lệch giữa hai lần đi và về theo giờ, phút, giây
Nếu bạn muốn hiển thị sự khác biệt giữa hai thời điểm là xx giờ xx phút xx giây, vui lòng sử dụng hàm TEXT như hình dưới đây:
Nhấp chuột TEXT để nhận ra các đối số và cách sử dụng của hàm này.
Để tính toán sự khác biệt giữa thời gian trong ô A23 và B23, hãy sử dụng công thức như sau:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Lưu ý:
Công thức này cũng chỉ tính toán chênh lệch giờ không quá 24 giờ và end_time phải lớn hơn start_time, nếu không, nó trả về #VALUE! giá trị lỗi.
1.27 Tính toán chênh lệch giữa hai lịch ngày
Nếu có hai thời điểm ở định dạng mm / dd / yyyy hh: mm: ss, để tính toán sự khác biệt giữa chúng, bạn có thể sử dụng một trong các công thức dưới đây khi cần.
Nhận chênh lệch thời gian giữa hai ngày giờ và kết quả trả về ở định dạng hh: mm: ss
Lấy hai lịch ngày trong ô A2 và B2 làm ví dụ, vui lòng sử dụng công thức như sau:
=B2-A2
Ấn Bản đăng ký hạng mục thi , trả về một kết quả ở định dạng ngày giờ, sau đó định dạng kết quả này thành [h]: mm: ss trong danh mục tùy chỉnh dưới Con số tab trong Format Cells thoại.
Nhận chênh lệch giữa hai lịch ngày và ngày, giờ, phút, giây về
Lấy hai lịch ngày trong ô A5 và B5 làm ví dụ, vui lòng sử dụng công thức như sau:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Lưu ý: trong cả hai công thức, end_datetime phải lớn hơn start_datetime, nếu không, các công thức trả về giá trị lỗi.
1.28 Tính chênh lệch thời gian với mili giây
Trước tiên, bạn cần biết cách định dạng ô để hiển thị mili giây:
Chọn các ô mà bạn muốn hiển thị mili giây và phải chọn Format Cells để cho phép Format Cells hộp thoại, chọn Tuỳ chỉnh trong Phân loại danh sách trong tab Số và nhập cái này hh: mm: ss.000 vào hộp văn bản.
Sử dụng công thức:
Ở đây để tính toán sự khác biệt giữa hai thời điểm trong ô A8 và B8, hãy sử dụng công thức như sau:
=ABS(B8-A8)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
1.29 Tính giờ làm việc giữa hai ngày trừ ngày cuối tuần
Đôi khi, bạn có thể cần phải tính giờ làm việc giữa hai ngày, không bao gồm các ngày cuối tuần (Thứ Bảy và Chủ Nhật).
Ở đây, giờ làm việc được cố định là 8 giờ mỗi ngày và để tính giờ làm việc giữa hai ngày được cung cấp trong ô A16 và B16, vui lòng sử dụng công thức như sau:
=NETWORKDAYS(A16,B16) * 8
Ấn Bản đăng ký hạng mục thi và sau đó định dạng kết quả dưới dạng tổng quát hoặc số.
Để biết thêm ví dụ về cách tính giờ làm việc giữa hai ngày, vui lòng truy cập Nhận giờ làm việc giữa hai ngày trong Excel
Nếu bạn có Kutools cho Excel được cài đặt trong Excel, 90% phép tính chênh lệch ngày giờ có thể được giải quyết nhanh chóng mà không cần nhớ bất kỳ công thức nào.
1.31 Tính toán chênh lệch giữa hai ngày giờ bằng Trình trợ giúp dữ liệu & thời gian
Để tính toán sự khác biệt giữa hai ngày giờ trong Excel, chỉ cần Trình trợ giúp ngày & giờ Là đủ.
1. Chọn một ô nơi bạn đặt kết quả được tính toán và nhấp vào Kutools > Công thức trợ giúp > Trình trợ giúp ngày & giờ.
2. Trong popping Trình trợ giúp ngày & giờ , hãy làm theo cài đặt bên dưới:
- kiểm tra Sự khác biệt Tùy chọn;
- Chọn ngày bắt đầu và ngày kết thúc trong Đầu vào đối số , bạn cũng có thể nhập trực tiếp ngày giờ theo cách thủ công vào hộp nhập liệu hoặc nhấp vào biểu tượng lịch để chọn ngày;
- Chọn loại kết quả đầu ra từ danh sách thả xuống;
- Xem trước kết quả trong Kết quả phần.
3. nhấp chuột Ok. Kết quả tính toán được xuất ra và kéo chốt tự động điền qua các ô mà bạn cũng cần tính toán.
Mẹo:
Nếu bạn muốn nhận được sự khác biệt giữa hai ngày giờ và hiển thị kết quả dưới dạng ngày, giờ và phút với Kutools cho Excel, vui lòng thực hiện như bên dưới:
Chọn một ô mà bạn muốn đặt kết quả và nhấp vào Kutools > Công thức trợ giúp > Ngày giờ > Đếm ngày, giờ và phút giữa hai ngày.
Sau đó trong Trình trợ giúp công thức hộp thoại, chỉ định ngày bắt đầu và ngày kết thúc, sau đó nhấp vào Ok.
Và kết quả chênh lệch sẽ được hiển thị dưới dạng ngày, giờ và phút.
Nhấp chuột Trình trợ giúp ngày & giờ để biết thêm cách sử dụng tính năng này.
Nhấp chuột Kutools cho Excel để biết tất cả các tính năng của bổ trợ này.
Nhấp chuột Tải xuống miễn phí để nhận bản dùng thử miễn phí 30 ngày của Kutools for Excel
Nếu bạn muốn nhanh chóng đếm ngày cuối tuần, ngày làm việc hoặc một ngày trong tuần cụ thể giữa hai lịch hẹn ngày, Kutools cho Excel's Công thức trợ giúp nhóm có thể giúp bạn.
1. Chọn ô sẽ đặt kết quả được tính toán, nhấp vào Kutools > Thống kê > Số ngày không làm việc giữa hai ngày/Số ngày làm việc giữa hai ngày/Đếm số ngày cụ thể trong tuần.
2. Trong cửa sổ bật ra Trình trợ giúp công thức hộp thoại, chỉ định ngày bắt đầu và ngày kết thúc, nếu bạn áp dụng Đếm số ngày cụ thể trong tuần, bạn cũng cần chỉ định ngày trong tuần.
Để đếm các ngày trong tuần cụ thể, bạn có thể tham khảo ghi chú để sử dụng 1-7 để chỉ Chủ nhật-Thứ bảy.
3. nhấp chuột Okvà sau đó kéo chốt tự động điền qua các ô cần đếm số ngày cuối tuần / ngày làm việc / ngày trong tuần cụ thể nếu cần.
Nhấp chuột Kutools cho Excel để biết tất cả các tính năng của bổ trợ này.
Nhấp chuột Tải xuống miễn phí để nhận bản dùng thử miễn phí 30 ngày của Kutools for Excel
2. Thêm hoặc bớt ngày và giờ
Ngoại trừ việc tính toán sự khác biệt giữa hai ngày giờ, cộng hoặc trừ cũng là cách tính ngày giờ thông thường trong Excel. Ví dụ, bạn có thể muốn lấy ngày đến hạn dựa trên ngày sản xuất và số ngày bảo quản của một sản phẩm.
2.11 Cộng hoặc trừ các ngày cho một ngày
Để cộng hoặc trừ một số ngày cụ thể cho một ngày, đây là hai phương pháp khác nhau.
Giả sử thêm 21 ngày vào một ngày trong ô A2, vui lòng chọn một trong các phương pháp dưới đây để giải quyết vấn đề đó,
Phương pháp 1 ngày + ngày
Chọn một ô và nhập công thức:
=A+21
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Nếu bạn muốn trừ đi 21 ngày, juts thay đổi dấu cộng (+) thành dấu trừ (-).
Phương pháp 2 Dán đặc biệt
1. Nhập số ngày bạn muốn thêm vào một ô, giả sử trong ô C2, rồi nhấn Ctrl + C để sao chép nó.
2. Sau đó chọn ngày bạn muốn thêm 21 ngày, nhấp chuột phải để hiển thị menu ngữ cảnh và chọn Dán đặc biệt ....
3. bên trong Dán đặc biệt hộp thoại, kiểm tra Thêm tùy chọn (Nếu bạn muốn trừ ngày, hãy kiểm tra Trừ quyền mua). Nhấp chuột OK.
4. Bây giờ các ngày ban đầu thay đổi thành các số có 5 chữ số, hãy định dạng chúng thành ngày tháng.
2.12 Cộng hoặc trừ các tháng cho một ngày
Để cộng hoặc trừ các tháng cho một ngày, có thể sử dụng hàm EDATE.
Nhấp chuột CHỈNH SỬA để nghiên cứu các lập luận và cách sử dụng của nó.
Giả sử thêm 6 tháng vào ngày trong ô A2, hãy sử dụng công thức như sau:
=EDATE(A2,6)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Nếu bạn muốn trừ 6 tháng cho đến ngày, hãy đổi 6 thành -6.
2.13 Cộng hoặc trừ năm cho một ngày
Để cộng hoặc trừ n năm cho một ngày, có thể sử dụng công thức kết hợp các hàm DATE, YEAR, MONTH và DAY.
Giả sử thêm 3 năm vào ngày trong ô A2, hãy sử dụng công thức như sau:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Nếu bạn muốn trừ 3 năm cho ngày, hãy đổi 3 thành -3.
2.14 Cộng hoặc trừ các tuần cho một ngày
Để cộng hoặc trừ các tuần cho một ngày, công thức chung là
Giả sử thêm 4 tuần vào ngày trong ô A2, hãy sử dụng công thức như sau:
=A2+4*7
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Nếu bạn muốn trừ 4 tuần đến ngày, hãy đổi dấu cộng (+) thành dấu trừ (-).
2.15 Cộng hoặc trừ các ngày làm việc bao gồm hoặc không bao gồm các ngày lễ
Trong phần này, nó giới thiệu cách sử dụng hàm WORKDAY để cộng hoặc trừ ngày làm việc vào một ngày nhất định không bao gồm ngày nghỉ hoặc bao gồm cả ngày nghỉ.
Truy cập NGÀY LÀM VIỆC để biết thêm chi tiết về các đối số và cách sử dụng của nó.
Thêm ngày làm việc kể cả ngày lễ
Trong ô A2 là ngày bạn sử dụng, trong ô B2 chứa số ngày bạn muốn thêm, vui lòng sử dụng công thức như sau:
=WORKDAY(A2,B2)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Thêm ngày làm việc trừ ngày lễ
Trong ô A5 là ngày bạn sử dụng, trong ô B5 chứa số ngày mà bạn muốn thêm, trong phạm vi D5: D8 liệt kê các ngày lễ, vui lòng sử dụng công thức như sau:
=WORKDAY(A5,B5,D5:D8)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Lưu ý:
Hàm WORKDAY lấy thứ bảy và chủ nhật làm cuối tuần, nếu cuối tuần của bạn là thứ bảy và chủ nhật, bạn có thể áp dụng hàm WOKRDAY.INTL, hỗ trợ chỉ định ngày cuối tuần.
Truy cập NGÀY LÀM VIỆC.INTL để biết thêm chi tiết.
Nếu bạn muốn trừ ngày làm việc cho một ngày, chỉ cần thay đổi số ngày thành số âm trong công thức.
2.16 Cộng hoặc trừ năm, tháng, ngày cụ thể cho một ngày
Nếu bạn muốn thêm một năm, ngày trong tháng cụ thể vào một ngày, công thức kết hợp hàm DATE, YEAR, MONTH và DAYS có thể giúp bạn.
Để thêm 1 năm 2 tháng và 30 ngày vào một ngày trong A11, vui lòng sử dụng công thức như sau:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Nếu bạn muốn trừ, hãy đổi tất cả các dấu cộng (+) thành dấu trừ (-).
2.21 Thêm hoặc trừ giờ / phút / giây cho ngày giờ
Ở đây cung cấp một số công thức để cộng hoặc trừ giờ, phút hoặc giây cho ngày giờ.
Thêm hoặc trừ giờ cho một ngày giờ
Giả sử thêm 3 giờ vào ngày giờ (cũng có thể là thời gian) trong ô A2, vui lòng sử dụng công thức như sau:
=A2+3/24
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Thêm hoặc trừ giờ cho một ngày giờ
Giả sử thêm 15 phút vào ngày giờ (cũng có thể là thời gian) trong ô A5, vui lòng sử dụng công thức như sau:
=A2+15/1440
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Thêm hoặc trừ giờ cho một ngày giờ
Giả sử thêm 20 giây vào ngày giờ (cũng có thể là thời gian) trong ô A8, vui lòng sử dụng công thức như sau:
=A2+20/86400
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Giả sử có một bảng Excel ghi lại thời gian làm việc của tất cả nhân viên trong một tuần, để tính tổng thời gian làm việc để tính các khoản thanh toán, bạn có thể sử dụng SUM (phạm vi) để nhận được kết quả. Nhưng nói chung, kết quả tổng hợp sẽ được hiển thị với thời gian không quá 24 giờ như ảnh chụp màn hình bên dưới, làm thế nào bạn có thể nhận được kết quả chính xác?
Trên thực tế, bạn chỉ cần định dạng kết quả là [hh]: mm: ss.
Nhấp chuột phải vào ô kết quả, chọn Format Cells trong menu ngữ cảnh và trong cửa sổ bật lên Format Cells hộp thoại, chọn Tuỳ chỉnh từ vỉ và gõ [hh]: mm: ss vào hộp văn bản ở phần bên phải, nhấp vào OK.
Kết quả tổng hợp sẽ được hiển thị chính xác.
2.23 Thêm giờ làm việc vào một ngày không bao gồm cuối tuần và ngày lễ
Ở đây cung cấp một công thức dài để lấy ngày kết thúc dựa trên việc thêm một số giờ làm việc cụ thể vào ngày bắt đầu và loại trừ các ngày cuối tuần (thứ Bảy và Chủ nhật) và ngày lễ.
Trong bảng Excel, A11 chứa ngày bắt đầu và B11 chứa giờ làm việc, trong ô E11 và E13 là thời gian bắt đầu và kết thúc làm việc và ô E15 chứa ngày nghỉ sẽ bị loại trừ.
Vui lòng sử dụng công thức như sau:
=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))
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Nếu bạn đã cài đặt Kutools cho Excel, chỉ có một công cụ - Ngày & giờ Helper có thể giải quyết hầu hết các phép tính về cộng và trừ ngày giờ.
1. Nhấp vào ô mà bạn muốn xuất kết quả và áp dụng công cụ này bằng cách nhấp vào Kutools > Công thức trợ giúp > Trình trợ giúp ngày và giờ.
2. bên trong Trình trợ giúp ngày & giờ hộp thoại, kiểm tra Thêm tùy chọn hoặc Trừ tùy chọn như bạn cần, sau đó chọn ô hoặc nhập trực tiếp ngày giờ bạn muốn sử dụng Đầu vào đối số , sau đó chỉ định năm, tháng, tuần, ngày, giờ, phút và giây mà bạn muốn cộng hoặc trừ, sau đó bấm Ok. Xem ảnh chụp màn hình:
Bạn có thể xem trước kết quả được tính toán trong Kết quả phần.
Bây giờ kết quả được xuất ra, hãy kéo ô điều khiển tự động qua các ô khác để nhận kết quả.
Nhấp chuột Trình trợ giúp ngày & giờ để biết thêm cách sử dụng tính năng này.
Nhấp chuột Kutools cho Excel để biết tất cả các tính năng của bổ trợ này.
Nhấp chuột Tải xuống phí để nhận bản dùng thử miễn phí 30 ngày của Kutools for Excel
2.41 Kiểm tra hoặc đánh dấu nếu ngày hết hạn
Nếu có danh sách các ngày hết hạn của sản phẩm, bạn có thể muốn kiểm tra và đánh dấu các ngày đã hết hạn dựa trên ngày hôm nay như ảnh chụp màn hình bên dưới.
Trên thực tế, Định dạng có điều kiện có thể nhanh chóng xử lý công việc này.
1. Chọn ngày bạn muốn kiểm tra, sau đó nhấp vào Trang Chủ > Định dạng có điều kiện > Quy tắc mới.
2. bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng trong Chọn một loại quy tắc phần và loại = B2 vào hộp nhập liệu (B2 là ngày đầu tiên bạn muốn kiểm tra) và nhấp vào Định dạng bật lên Format Cells , sau đó chọn một định dạng khác để tồn tại trước các ngày hết hạn khi bạn cần. Nhấp chuột OK > OK.
2.42 Trả về cuối tháng hiện tại / ngày đầu tiên của tháng tiếp theo / a>
Ngày hết hạn của một số sản phẩm là vào cuối tháng sản xuất hoặc ngày đầu tiên của tháng sản xuất tiếp theo, để nhanh chóng liệt kê các ngày hết hạn dựa trên ngày sản xuất, vui lòng theo dõi phần này.
Nhận cuối tháng hiện tại
Đây là ngày sản xuất trong ô B13, vui lòng sử dụng công thức như sau:
=EOMONTH(B13,0)
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
Nhận ngày đầu tiên của tháng tiếp theo
Đây là ngày sản xuất trong ô B18, vui lòng sử dụng công thức như sau:
=EOMONTH(B18,0)+1
Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.
3. Tính tuổi
Trong phần này, nó liệt kê các phương pháp giải quyết cách tính tuổi dựa trên một ngày hoặc một số chuỗi nhất định.
3.11 Tính tuổi dựa trên ngày sinh đã cho
Nhận tuổi ở dạng số thập phân dựa trên ngày sinh
Nhấp chuột NĂMFRAC để biết chi tiết về các đối số của nó và cách sử dụng.
Ví dụ: để lấy tuổi dựa trên danh sách ngày sinh trong cột B2: B9, vui lòng sử dụng công thức như sau:
=YEARFRAC(B2,TODAY())
Ấn Bản đăng ký hạng mục thi , sau đó kéo tay cầm tự động điền xuống cho đến khi tất cả các độ tuổi được tính toán.
Mẹo:
1) Bạn có thể chỉ định vị trí thập phân khi bạn cần trong Format Cells thoại.
2) Nếu bạn muốn tính tuổi vào một ngày cụ thể dựa trên ngày sinh nhất định, hãy thay đổi TODAY () thành ngày cụ thể kèm theo dấu ngoặc kép như = YEARFRAC (B2, "1/1/2021")
3) Nếu bạn muốn lấy tuổi của năm tiếp theo dựa trên ngày sinh, chỉ cần thêm 1 vào công thức như = YEARFRAC (B2, TODAY ()) + 1.
Nhận số nguyên dựa trên ngày sinh
Nhấp chuột NGÀY để biết chi tiết về các đối số của nó và cách sử dụng.
Sử dụng ví dụ trên, để lấy tuổi dựa trên ngày sinh trong danh sách ở B2: B9, vui lòng sử dụng công thức như sau:
=DATEDIF(B2,TODAY(),"y")
Ấn Bản đăng ký hạng mục thi , sau đó kéo chốt tự động điền xuống cho đến khi tất cả các độ tuổi được tính toán.
Mẹo:
1) Nếu bạn muốn tính tuổi vào một ngày cụ thể dựa trên ngày sinh nhất định, hãy thay đổi TODAY () thành ngày cụ thể kèm theo dấu ngoặc kép như = DATEDIF (B2, "1/1/2021", "y") .
2) Nếu bạn muốn lấy tuổi của năm tiếp theo dựa trên ngày sinh, chỉ cần thêm 1 vào công thức như = DATEDIF (B2, TODAY (), "y") + 1.
3.12 Tính tuổi theo định dạng năm, tháng và ngày theo ngày sinh nhất định
Nếu bạn muốn tính tuổi dựa trên ngày sinh nhất định và hiển thị kết quả dưới dạng xx năm, xx tháng, xx ngày như ảnh chụp màn hình bên dưới cho thấy, đây là một công thức dài có thể giúp bạn.
Để tính tuổi theo năm, tháng và ngày dựa trên ngày sinh trong ô B12, vui lòng sử dụng công thức như sau:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Ấn Bản đăng ký hạng mục thi để lấy độ tuổi, sau đó kéo ô điều khiển tự động điền xuống các ô khác.
Mẹo:
Nếu bạn muốn tính tuổi trong một ngày cụ thể dựa trên ngày sinh nhất định, hãy thay đổi TODAY () thành ngày cụ thể kèm theo dấu ngoặc kép như = = DATEDIF (B12, "1/1/2021", "Y") & "Năm" & DATEDIF (B12, "1/1/2021", "YM") & "Tháng" & DATEDIF (B12, "1/1/2021", "MD") & "Ngày".
3.13 Tính tuổi theo ngày sinh trước 1/1/1900
Trong Excel, không thể nhập ngày trước ngày 1/1/1900 dưới dạng ngày giờ hoặc được tính toán chính xác. Nhưng nếu bạn muốn tính tuổi của một người nổi tiếng dựa trên ngày sinh (trước 1/11900) và ngày mất thì chỉ có mã VBA mới có thể giúp bạn.
1. nhấn Khác + F11 phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ và nhấp chuột Chèn tab và chọn Mô-đun để tạo một mô-đun mới.
2. Sau đó sao chép và dán đoạn mã dưới đây vào mô-đun mới.
VBA: Tính tuổi trước ngày 1/1/1900
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. Lưu mã và quay lại trang tính và chọn một ô để đặt tuổi đã tính, gõ = AgeFunc (ngày sinh, ngày mất), trong trường hợp này, = AgeFunc (B22, C22), nhấn phím Enter để lấy tuổi. Và sử dụng chốt tự động điền để áp dụng công thức này cho các ô khác nếu cần.
Nếu bạn có Kutools cho Excel được cài đặt trong Excel, bạn có thể áp dụng Trình trợ giúp ngày & giờ công cụ để tính tuổi.
1. Chọn một ô mà bạn muốn đặt tuổi được tính toán và nhấp vào Kutools > Công thức trợ giúp > Trình trợ giúp ngày & giờ.
2. bên trong Trình trợ giúp ngày & giờ hộp thoại,
- 1) Kiểm tra Độ tuổi Tùy chọn;
- 2) Chọn ô ngày sinh hoặc nhập trực tiếp ngày sinh hoặc nhấp vào biểu tượng lịch để chọn ngày sinh;
- 3) Chọn Hôm nay tùy chọn nếu bạn muốn tính tuổi hiện tại, hãy chọn Ngày chỉ định tùy chọn và nhập ngày nếu bạn muốn tính tuổi trong quá khứ hoặc tương lai;
- 4) Chỉ định loại đầu ra từ danh sách thả xuống;
- 5) Xem trước kết quả đầu ra. Nhấp chuột Ok.
Nhấp chuột Trình trợ giúp ngày & giờ để biết thêm cách sử dụng tính năng này.
Nhấp chuột Kutools cho Excel để biết tất cả các tính năng của bổ trợ này.
Nhấp chuột Tải xuống miễn phí để nhận bản dùng thử miễn phí 30 ngày của Kutools for Excel
Nếu có một danh sách các số ID sử dụng 6 chữ số đầu tiên để ghi ngày sinh chẳng hạn như 920315330 có nghĩa là ngày sinh là 03/15/1992, làm thế nào bạn có thể nhanh chóng lấy ngày sinh vào cột khác?
Bây giờ chúng ta hãy lấy danh sách các số ID bắt đầu trong ô C2 làm ví dụ và sử dụng công thức như sau:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Ấn Bản đăng ký hạng mục thi Chìa khóa. Sau đó, kéo tay cầm tự động điền xuống để nhận các kết quả khác.
Lưu ý:
Trong công thức, bạn có thể thay đổi tham chiếu theo nhu cầu của mình. Ví dụ: nếu số ID hiển thị là 13219920420392, sinh nhật là 04/20/1992, bạn có thể thay đổi công thức thành = MID (C2,8,2) & "/" & MID (C2,10,2) & "/ "& MID (C2,4,4) để có kết quả chính xác.
Nếu có một danh sách các số ID sử dụng 6 chữ số đầu tiên để ghi ngày sinh, chẳng hạn như 920315330 có nghĩa là ngày sinh là 03/15/1992, làm thế nào bạn có thể nhanh chóng tính tuổi dựa trên từng số ID trong Excel?
Bây giờ chúng ta hãy lấy danh sách các số ID bắt đầu trong ô C2 làm ví dụ và sử dụng công thức như sau:
=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")
Ấn Bản đăng ký hạng mục thi Chìa khóa. Sau đó, kéo tay cầm tự động điền xuống để nhận các kết quả khác.
Lưu ý:
Trong công thức này, nếu năm nhỏ hơn năm hiện tại, thì năm đó sẽ được coi là bắt đầu bằng 20, chẳng hạn như 200203943 sẽ được coi là năm 2020; nếu năm lớn hơn năm hiện tại, năm sẽ được coi là bắt đầu bằng 19, chẳng hạn như 920420392 sẽ được coi là năm 1992.
Các hướng dẫn Excel khác:
Kết hợp nhiều Workbook / Worksheets thành một
Hướng dẫn này, liệt kê hầu hết tất cả các tình huống kết hợp mà bạn có thể gặp phải và cung cấp các giải pháp chuyên nghiệp tương đối cho bạn.
Tách ô văn bản, số và ngày (Tách thành nhiều cột)
Hướng dẫn này được chia thành ba phần: chia ô văn bản, chia ô số và chia ô ngày tháng. Mỗi phần cung cấp các ví dụ khác nhau để giúp bạn biết cách xử lý công việc tách khi gặp cùng một vấn đề ..
Kết hợp nội dung của nhiều ô mà không làm mất dữ liệu trong Excel
Hướng dẫn này thu hẹp việc trích xuất đến một vị trí cụ thể trong một ô và thu thập các phương pháp khác nhau để giúp trích xuất văn bản hoặc số từ một ô theo vị trí cụ thể trong Excel.
So sánh hai cột cho các kết quả phù hợp và khác biệt trong Excel
Ở đây, bài viết này đề cập đến hầu hết các tình huống có thể xảy ra của việc so sánh hai cột mà bạn có thể gặp và hy vọng nó có thể giúp ích cho bạn.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn
- Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
- Hợp nhất các ô / hàng / cột và Lưu giữ dữ liệu; Nội dung phân chia ô; Kết hợp các hàng trùng lặp và Tổng / Trung bình... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
- Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
- Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
- Yêu thích và Chèn công thức nhanh chóng, Dãy, Biểu đồ và Hình ảnh; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
- Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
- Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
- Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
- Nhóm bảng tổng hợp theo số tuần, ngày trong tuần và hơn thế nữa ... Hiển thị các ô đã mở khóa, đã khóa bởi các màu sắc khác nhau; Đánh dấu các ô có công thức / tênhữu ích. Cảm ơn !
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
