Tính khoảng cách giữa hai ngày

Tính khoảng cách giữa hai ngày

Sử dụng hàm DATEDIF khi bạn muốn tính toán khoảng cách giữa hai ngày. Trước tiên, hãy đặt ngày bắt đầu vào một ô và ngày kết thúc vào một ô khác. Sau đó, nhập công thức tương tự như một trong các công thức sau.

Khoảng cách tính bằng ngày

=DATEDIF(D9,E9,"d") với kết quả là 856

Trong ví dụ này, ngày bắt đầu nằm ở ô D9 và ngày kết thúc nằm ở ô E9. Công thức nằm ở ô F9. "d" trả về số ngày đầy đủ giữa hai ngày.

Khoảng cách tính bằng tuần

=(DATEDIF(D13,E13,"d")/7) và kết quả: 122,29

Trong ví dụ này, ngày bắt đầu nằm ở ô D13 và ngày kết thúc nằm ở ô E13. "d" trả về số ngày. Nhưng hãy lưu ý /7 ở cuối. Tham số này sẽ chia số ngày cho 7 vì một tuần có 7 ngày. Lưu ý, kết quả này cũng cần được định dạng dưới dạng số. Nhấn CTRL + 1. Sau đó, bấm Số > Phần thập phân: 2.

Khoảng cách tính bằng tháng

=DATEDIF(D5,E5,"m") và kết quả: 28

Trong ví dụ này, ngày bắt đầu nằm ở ô D5 và ngày kết thúc nằm ở ô E5. Trong công thức, "m" trả về số tháng đầy đủ giữa hai ngày.

Khoảng cách tính bằng năm

=DATEDIF(D2,E2,"y") và kết quả: 2

Trong ví dụ này, ngày bắt đầu nằm ở ô D2 và ngày kết thúc nằm ở ô E2. "y" trả về số năm đầy đủ giữa hai ngày.

Tính toán tuổi theo số năm, tháng và ngày tích lũy

Bạn cũng có thể tính toán tuổi hoặc thời gian làm việc của một người. Kết quả có thể ở dạng tương tự như "2 năm, 4 tháng, 5 ngày".

1. Sử dụng hàm DATEDIF để tìm số năm tổng cộng.

=DATEDIF(D17,E17,"y") và kết quả: 2

Trong ví dụ này, ngày bắt đầu nằm ở ô D17 và ngày kết thúc nằm ở ô E17. Trong công thức, “y” trả về số năm đầy đủ giữa hai ngày.

2. Sử dụng hàm DATEDIF một lần nữa với "ym" để tìm số tháng.

=DATEDIF(D17,E17,"ym") và kết quả: 4

Trong một ô khác, sử dụng công thức DATEDIF với tham số “ym”. Tham số "ym" trả về số tháng còn lại sau năm đầy đủ cuối cùng.

3. Sử dụng một công thức khác để tìm số ngày.

=DATEDIF(D17,E17,"md") và kết quả: 5

Bây giờ, chúng ta cần tìm số ngày còn lại. Chúng ta sẽ tìm bằng cách viết một loại công thức khác, được hiển thị ở trên. Công thức này lấy ngày kết thúc đầu tiên trong ô E17 (06/05/2016) trừ đi ngày đầu tiên của tháng kết thúc (01/05/2016). Đây là cách tính của công thức này: Trước tiên, hàm DATE sẽ tạo ngày, 01/05/2016. Hàm này tạo ngày theo năm trong ô E17 và tháng trong ô E17. Khi đó 1 sẽ đại diện cho ngày đầu tiên của tháng đó. Kết quả cho hàm DATE là 01/05/2016. Sau đó, chúng ta lấy ngày kết thúc đầu tiên trong ô E17 là 06/05/2016 trừ đi kết quả trên. 06/05/2016 trừ 01/05/2016 bằng 5 ngày.

Ghi chú: Chúng tôi khuyên bạn không nên sử dụng tham đối "md" trong DATEDIF vì tham đối này có thể tính toán kết quả không chính xác.

4. Tùy chọn: Kết hợp ba công thức làm một.

=DATEDIF(D17,E17,"y")&" năm, "&DATEDIF(D17,E17,"ym")&" tháng, "&DATEDIF(D17,E17,"md")&" ngày" và kết quả: 2 năm, 4 tháng, 5 ngày

Bạn có thể đặt cả ba phép tính vào một ô như ví dụ này. Sử dụng dấu và, dấu ngoặc kép và văn bản. Công thức này phải nhập nhiều hơn nhưng bạn chỉ cần nhập một lần. Mẹo: Nhấn ALT + ENTER để đặt ngắt dòng vào công thức của bạn. Việc này sẽ giúp công thức trở nên dễ đọc hơn. Ngoài ra, nhấn CTRL+SHIFT+U nếu bạn không thể xem toàn bộ công thức.

Tải xuống ví dụ của chúng tôi

Bạn có thể tải xuống sổ làm việc mẫu chứa toàn bộ ví dụ trong bài viết này. Bạn có thể làm theo hoặc tạo công thức riêng.

Tải xuống các ví dụ về phép tính ngày

Các phép tính ngày và giờ khác

Như bạn thấy ở trên, hàm DATEDIF tính toán khoảng cách giữa ngày bắt đầu và ngày kết thúc. Tuy nhiên, thay vì nhập ngày cụ thể, bạn cũng có thể sử dụng hàm TODAY() trong công thức. Khi bạn sử dụng hàm TODAY(), Excel sẽ lấy ngày hiện tại trên máy tính của bạn. Lưu ý, điều này sẽ thay đổi khi bạn mở lại tệp vào một ngày trong tương lai.

=DATEDIF(TODAY(),D28,"y") và kết quả: 984

Lưu ý, thời điểm viết bài viết này là vào 06/10/2016.

Sử dụng hàm NETWORKDAYS.INTL khi bạn muốn tính toán số ngày làm việc giữa hai ngày. Bạn cũng có thể loại trừ ngày cuối tuần và ngày lễ.

Trước khi bắt đầu: Hãy quyết định xem bạn có muốn loại trừ các ngày lễ hay không. Nếu muốn, hãy nhập danh sách các ngày lễ trong một vùng hoặc trang tính riêng biệt. Đặt từng ngày lễ vào một ô riêng. Sau đó, chọn các ô đó, rồi chọn Công thức > Xác định Tên. Đặt tên cho dải ô là Ngày lễ Của tôi và bấm OK. Sau đó, tạo công thức bằng các bước dưới đây.

1. Nhập ngày bắt đầu và ngày kết thúc.

Ngày bắt đầu trong ô D53 là 01/01/2016, ngày kết thúc trong ô E53 là 31/12/2016

Trong ví dụ này, ngày bắt đầu nằm ở ô D53 và ngày kết thúc nằm ở ô E53.

2. Trong một ô khác, hãy nhập công thức như sau:

=NETWORKDAYS.INTL(D53,E53,1) và kết quả: 261

Nhập công thức tương tự như ví dụ trên. Tham số 1 trong công thức thiết lập Thứ Bảy và Chủ Nhật làm ngày cuối tuần và loại trừ các ngày này ra khỏi tổng.

Lưu ý: Excel 2007 không có hàm NETWORKDAYS.INTL. Tuy nhiên, phiên bản này có hàm NETWORKDAYS. Trong Excel 2007, ví dụ trên sẽ có dạng như sau: =NETWORKDAYS(D53,E53). Bạn không chỉ định tham số 1 vì hàm NETWORKDAYS giả định ngày cuối tuần là vào Thứ Bảy và Chủ Nhật.

3. Nếu cần, hãy thay đổi tham số 1.

Danh sách Intellisense hiển thị 2 - Chủ Nhật, Thứ Hai; 3 - Thứ Hai, Thứ Ba, v.v..

Nếu Thứ Bảy và Chủ Nhật không phải ngày cuối tuần của bạn, hãy đổi tham số 1 thành một số khác từ danh sách IntelliSense. Ví dụ: tham số 2 thiết lập Chủ Nhật và Thứ Hai làm ngày cuối tuần.

Nếu bạn đang sử dụng Excel 2007, hãy bỏ qua bước này. Hàm NETWORKDAYS của Excel 2007 luôn giả định ngày cuối tuần là vào Thứ Bảy và Chủ Nhật.

4. Nhập tên dải ô ngày lễ.

=NETWORKDAYS.INTL(D53,E53,1,MyHolidays) và kết quả: 252

Nếu bạn đã tạo tên dải ô ngày lễ trong mục “Trước khi bắt đầu” ở trên, hãy nhập tên đó ở phần cuối như sau. Nếu bạn không có ngày lễ, bạn có thể bỏ qua phần Ngày lễ của Tôi. Nếu bạn đang sử dụng Excel 2007 thì thay vào đó, ví dụ trên sẽ có dạng như sau: =NETWORKDAYS(D53,E53,MyHolidays).

Mẹo: Nếu bạn không muốn tham chiếu đến tên dải ô ngày lễ, bạn cũng có thể nhập một dải ô thay thế, như D35:E:39. Hoặc bạn có thể nhập từng ngày lễ vào công thức. Ví dụ: nếu ngày lễ của bạn là 01/01/2016 và 02/01/2016, bạn sẽ nhập như sau: =NETWORKDAYS.INTL(D53,E53,1,{"1/1/2016","1/2/2016"}). Trong Excel 2007, công thức sẽ có dạng như sau: =NETWORKDAYS(D53,E53,{"1/1/2016","1/2/2016"})

Bạn có thể tính toán thời gian đã qua bằng cách lấy một mốc thời gian trừ một mốc khác. Trước tiên, hãy đặt thời gian bắt đầu vào một ô và thời gian kết thúc vào một ô khác. Đừng quên nhập đầy đủ thời gian, bao gồm giờ, phút và một khoảng trắng trước SA hoặc CH. Đây là cách thực hiện:

1. Nhập thời gian bắt đầu và thời gian kết thúc.

Ngày/giờ bắt đầu 7:15 SA, Ngày/giờ kết thúc 4:30 CH

Trong ví dụ này, thời gian bắt đầu nằm ở ô D80 và thời gian kết thúc nằm ở ô E80. Đừng quên nhập giờ, phút và một khoảng trắng trước SA hoặc CH.

2. Đặt định dạng h:mm SA/CH.

Hộp thoại định dạng ô, lệnh Tùy chỉnh, loại h:mm SA/CH

Chọn cả hai ngày, rồi nhấn CTRL + 1 (hoặc Hình ảnh biểu tượng nút Command trong MAC + 1 trên máy Mac). Đừng quên chọn Tùy chỉnh > h:mm SA/CH nếu bạn chưa đặt phần này.

3. Lấy hai thời gian trừ cho nhau.

=E80-D80 và kết quả: 9:15 SA

Trong một ô khác, lấy ô thời gian kết thúc trừ đi ô thời gian bắt đầu.

4. Đặt định dạng h:mm.

Hộp thoại Định dạng Ô, lệnh Tùy chỉnh, loại h:mm

Nhấn CTRL + 1 (hoặc Hình ảnh biểu tượng nút Command trong MAC + 1 trên máy Mac). Chọn Tùy chỉnh > h: mm để kết quả loại trừ SA và CH.

Để tính thời gian giữa hai ngày và thời gian, bạn có thể chỉ cần lấy thời gian này trừ đi thời gian kia. Tuy nhiên, bạn phải áp dụng định dạng cho từng ô nhằm đảm bảo Excel trả về kết quả bạn muốn.

1. Nhập đầy đủ hai ngày và giờ.

Ngày bắt đầu 01/01/2016 1:00 CH; Ngày kết thúc 02/01/2016 2:00 CH

Trong một ô, nhập ngày/giờ bắt đầu đầy đủ. Và trong một ô khác, hãy nhập ngày/giờ kết thúc đầy đủ. Mỗi ô cần có tháng, ngày, năm, giờ, phút và một khoảng trắng trước SA hoặc CH.

2. Đặt định dạng 14/03/2012 1:30 CH.

Hộp thoại Định dạng Ô, lệnh Ngày, loại 14/03/2012 1:30 CH

Chọn cả hai ô, rồi nhấn CTRL + 1 (hoặc Hình ảnh biểu tượng nút Command trong MAC + 1 trên máy Mac). Sau đó chọn Ngày > 14/03/2012 1:30 CH.. Đây không phải là ngày bạn sẽ đặt mà chỉ là định dạng mẫu. Lưu ý, trong các phiên bản trước phiên bản Excel 2016, định dạng này có thể có ngày mẫu khác, như 14/03/01 1:30 CH.

3. Lấy hai ngày/giờ trừ cho nhau.

=E84-D84 và kết quả là 1,041666667

Trong một ô khác, lấy ngày/giờ kết thúc trừ đi ngày/giờ bắt đầu. Kết quả có thể sẽ ở dạng tương tự số và số thập phân. Bạn sẽ khắc phục điều này trong bước tiếp theo.

4. Đặt định dạng [h]:mm.

Hộp thoại Định dạng Ô, lệnh Tùy chỉnh, loại [h]:mm

Nhấn CTRL + 1 (hoặc Hình ảnh biểu tượng nút Command trong MAC + 1 trên máy Mac). Chọn Tùy chỉnh. Trong hộp Loại , nhập [h]: mm.

Xem Thêm

Hàm DATEDIF

Hàm NETWORKDAYS.INTL

NETWORKDAYS

Các hàm ngày và giờ khác

Tính toán khoảng cách giữa hai thời điểm

Phát triển các kỹ năng của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×