Tính chênh lệch giữa hai ngày

Sử dụng hàm DATEDIF khi bạn muốn tính chênh lệch giữa hai ngày. Trước tiên, đặt ngày bắt đầu vào một ô và ngày kết thúc ở ô khác. Sau đó, nhập công thức như một trong các công thức sau.

Cảnh báo: Nếu Ngày_bắt_đầu lớn hơn Ngày_kết_thúc, kết quả sẽ là #NUM!.

Chênh lệch về số ngày

=DATEDIF(D9,E9,"d") với kết quả là 856

Trong ví dụ này, ngày bắt đầu nằm trong ô D9 và ngày kết thúc nằm trong ô E9. Công thức nằm trong ô F9. “d” trả về số ngày đầy đủ giữa hai ngày.

Chênh lệch về số tuần

=(DATEDIF(D13,E13,"d")/7) với kết quả là: 122,29

Trong ví dụ này, ngày bắt đầu nằm trong ô D13 và ngày kết thúc nằm trong ô E13. “d” trả về số ngày. Nhưng hãy lưu ý /7 ở cuối. Ta chia số ngày cho 7 vì có 7 ngày trong một tuần. Lưu ý rằng kết quả này cũng cần được định dạng dưới dạng số. Nhấn CTRL + 1. Sau đó bấm vào Số > Vị trí thập phân: 2.

Chênh lệch về số tháng

=DATEDIF(D5,E5,"m") với kết quả là: 28

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

Chênh lệch về số năm

=DATEDIF(D2,E2,"y") với kết quả là: 2

Trong ví dụ này, ngày bắt đầu nằm trong ô D2 và ngày kết thúc nằm trong ô E2. “d” trả về số năm đầy đủ giữa hai ngày.

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

Bạn cũng có thể tính tuổi hoặc thời gian phục vụ của một người. Kết quả có thể là “2 năm, 4 tháng, 5 ngày”.

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

=DATEDIF(D17,E17,"y") với kết quả là: 2

Trong ví dụ này, ngày bắt đầu nằm trong ô D17 và ngày kết thúc nằm trong ô E17. Trong công thức này, “y” trả về số năm đầy đủ giữa hai ngày.

2. Sử dụng lại DATEDIF với “ym” để tìm số tháng.

=DATEDIF(D17,E17,"ym") với kết quả là: 4

Trong một ô khác, sử dụng công thức DATEDIF với tham số “ym”. “ym” trả về số tháng còn lại sau cả năm trước.

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

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

Cảnh báo: Bạn không nên sử dụng đối số "md" của DATEDIF vì đối số này có thể tính ra kết quả không chính xác.

4. Tùy chọn: Kết hợp ba công thức thành một.

=DATEDIF(D17,E17,"y")&" năm, "&DATEDIF(D17,E17,"ym")&" tháng, "&DATEDIF(D17,E17,"md")&" ngày" với kết quả là: 2 năm, 4 tháng, 5 ngà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 này dài hơn khi nhập, nhưng ít nhất là công thức tất cả trong một. Mẹo: Nhấn ALT+ENTER để đặt ngắt dòng trong công thức của bạn. Điều này giúp bạn dễ đọc hơn. Ngoài ra, nhấn CTRL+SHIFT+U nếu bạn không thể thấy 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 minh họa với tất cả các 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.Tải xuống ví dụ tính ngà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 chênh lệ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() bên trong công thức. Khi bạn sử dụng hàm TODAY(), Excel sẽ sử dụng ngày hiện tại của máy tính cho ngày đó. Lưu ý rằng ngày này sẽ thay đổi khi bạn mở lại tệp vào một ngày trong tương lai.

=DATEDIF(TODAY(),D28,"y") với kết quả là: 984

Xin lưu ý rằng vào thời điểm đăng bài viết này, ngày này là ngày 6 tháng 10 năm 2016.

Sử dụng hà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ể không bao gồm ngày cuối tuần và ngày nghỉ lễ.

Trước khi bạn bắt đầu: Quyết định xem bạn có muốn loại trừ ngày nghỉ lễ không. Nếu muốn, hãy nhập danh sách ngày nghỉ lễ vào một khu vực hoặc trang tính riêng biệt. Đặt mỗi ngày nghỉ lễ vào ô riêng. Sau đó, chọn những ô đó, chọn Công thức > Xác định tên. Đặt tên phạm vi là Ngày nghỉ lễ của tôi rồi nhấp vào OK. Sau đó, tạo công thức bằng cách sử dụ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 nằm trong ô E53 là 31/12/2016

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

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

=NETWORKDAYS.INTL(D53,E53,1) với kết quả là: 261

Nhập công thức như ví dụ trên đây. Số 1 trong công thức xác định Thứ Bảy và Chủ Nhật là ngày cuối tuần và loại trừ chúng khỏi tổng.

3. Nếu cần, hãy thay đổi 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 là ngày cuối tuần của bạn, hãy thay đổi 1 thành số khác từ danh sách IntelliSense. Ví dụ, 2 xác định Chủ Nhật và Thứ Hai là ngày cuối tuần.

4. Nhập tên phạm vi ngày nghỉ lễ.

=NETWORKDAYS.INTL(D53,E53,1,MyHolidays) với kết quả là: 252

Nếu bạn đã tạo tên phạm vi ngày nghỉ lễ trong mục “Trước khi bạn bắt” đầu ở trên, hãy nhập tên đó vào cuối như thế này. Nếu bạn không có ngày nghỉ lễ, bạn có thể không bao gồm dấu phẩy và Ngày nghỉ lễ của tôi.

Bạn có thể tính toán thời gian đã trôi qua bằng cách lấy một khoảng thời gian khác trừ đi một khoảng thời gian. Trước tiên, đặt thời gian bắt đầu vào một ô và thời gian kết thúc ở ô khác. Đảm bảo nhập toàn bộ thời gian, bao gồm giờ, phút và một dấu cách 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 trong ô D80 và thời gian kết thúc nằm trong ô E80. Đảm bảo nhập giờ, phút và một dấu cách 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 và nhấn CTRL + 1 (hoặc Hình ảnh biểu tượng nút Command trên máy MAC + 1 trên máy Mac). Đảm bảo chọn Tùy chỉnh > h:mm SA/CH nếu chưa đặt.

3. Trừ hai khoảng thời gian này.

Trong một ô khác, lấy ô thời gian kết thúc trừ ô 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 trên máy MAC + 1 trên máy Mac). Chọn Tùy chỉnh > h:mm để kết quả không bao gồm SA và CH.

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

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

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

Trong một ô, nhập ngày/giờ bắt đầu đầy đủ. Trong ô khác, nhập ngày/giờ kết thúc đầy đủ. Mỗi ô phải có một tháng, ngày, năm, giờ, phút và một dấu cách trước SA hoặc CH.

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

Hộp thoại định dạng ô, Lệnh ngày, loại 14/3/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 trên máy MAC + 1 trên máy Mac). Sau đó, chọnNgày > 14/3/2012 1:30 CH. Đây không phải là ngày bạn sẽ đặt, đây chỉ là ví dụ về hình thức của định dạng. Lưu ý rằng trong các phiên bản trước Excel 2016, định dạng này có thể có một ngày mẫu khác như 14/3/2001 1:30 CH.

3. Trừ hai khoảng thời gian này.

Trong ô khác, lấy ngày/giờ kết thúc trừ ngày/giờ bắt đầu. Kết quả có thể sẽ có dạng số và thập phân. Bạn sẽ sửa thông tin này trong bước tiếp theo.

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

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

Chủ đề liên quan

Hàm DATEDIF Hàm NETWORKDAYS.INTL NETWORKDAYS Xem thêm hàm ngày giờ khác Tính chênh lệch giữa hai thời gian

Chênh lệch về số ngày

Chênh lệch về số tuần

Chênh lệch về số tháng

Chênh lệch về số năm