Mục lục
×
Sử dụng phổ biến của hàm
Sử dụng phổ biến của hàm

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

Dùng hàm DATEDIF khi bạn muốn tính toán chênh lệ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 như một trong các loại sau.

Cảnh bá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 theo 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 trong ô D9 và ngày kết thúc nằm ở ô E9. Công thức ở ô F9. "d" trả về số ngày trọn vẹn giữa hai ngày.

Chênh lệch theo 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 trong ô D13 và ngày kết thúc nằm trong ô E13. "d" trả về số ngày. Nhưng hãy lưu ý tới /7 ở cuối. Làm như vậy sẽ 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 đó bấm Số> Vị trí thập phân: 2.

Chênh lệch theo tháng

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

Trong ví dụ này, ngày bắt đầu nằm trong ô 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.

Chênh lệch trong năm

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

Trong ví dụ này, ngày bắt đầu nằm trong ô 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 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 người khác. Kết quả có thể có loại như "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à kết quả: 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, "y" trả về số năm đầy đủ giữa hai ngày.

2. Dùng hàm DATEDIF lại với "ym" để tìm tháng.

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

Trong một ô khác, hãy dùng công thức DATEDIF với tham số "ym". "ym" trả về số tháng còn lại, qua cả năm trước.

3. Dùng công thức khác để tìm ngày.

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

Bây giờ chúng ta cần tìm số ngày còn lại. Chúng ta sẽ làm điều này bằng cách viết một loại công thức khác, như minh họa ở 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.

Cảnh báo: Chúng tôi khuyên bạn không nên sử dụng tham đối "md" 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 thành 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 trong một ô như ví dụ này. Sử dụng dấu và, dấu ngoặc kép và văn bản. Đó là công thức dài hơn để nhập nhưng ít nhất thì tất cả đều có trong một kiểu. Mẹo: Nhấn ALT+ENTER để đặt ngắt dòng trong công thức của bạn. Điều này giúp dễ đọc hơn. Ngoài ra, hãy nhấn CTRL+SHIFT+U nếu bạn không nhìn 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 mẫu 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 của riêng mình.

Tải xuống ví dụ 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 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 các ngày cụ thể, bạn cũng có thể dùng hàm TODAY() bên trong công thức. Khi bạn dùng hàm TODAY(), bạn Excel dùng ngày hiện tại của máy tính cho ngày đó. Hãy nhớ rằng điều này sẽ thay đổi khi tệp được mở lại vào một ngày trong tương lai.

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

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

Dùng NETWORKDAYS. Hàm 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ể cho phép không bao gồm các ngày cuối tuần và ngày lễ.

Trước khi bạn bắt đầu: Quyết định xem bạn có muốn loại trừ ngày lễ không. Nếu bạn làm như vậy, hãy nhập danh sách ngày lễ vào một khu vực 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 ô đó, chọn Công > Xác định Tên. Đặt tên cho dải ô MyHolidays, rồi bấm vào OK. Sau đó, tạo công thức theo 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 ô 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 như ví dụ trên đây. 1 trong công thức thiết lập ngày Thứ bảy và Chủ nhật là ngày cuối tuần và loại trừ các ngày này khỏi tổng số.

Lưu ý: Excel 2007 không có NETWORKDAYS. INTL (Hàm INTL). Tuy nhiên, hàm này có hàm NETWORKDAYS. Ví dụ trên đây sẽ như thế này trong Excel 2007: =NETWORKDAYS(D53,E53). Bạn không xác định 1 vì hàm NETWORKDAYS giả định rằng 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 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 số 1 thành một số khác từ danh sách IntelliSense. Ví dụ, 2 thiết lập các ngày Chủ nhật và Thứ hai là ngày cuối tuần.

Nếu bạn đang sử dụng Excel 2007, hãy bỏ qua bước này. Excel 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 phạm vi ngày lễ.

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

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

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

Bạn có thể tính toán thời gian đã qua bằng cách trừ một khoảng thời gian ra khỏi một khoảng thời gian 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. Đảm bảo nhập thời gian đầy đủ, bao gồm giờ, phút và khoảng trống trước CH 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 từ 7:15 SÁNG, 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à khoảng trống trước GIỜ SÁNG hoặc CH.

2. Đặt định dạng h:mm AM/PM.

Hộp thoại Định dạng ô, lệnh Tùy chỉnh, kiểu h:mm AM/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 > tùy chỉnh h:mm AM/CH, nếu tùy chọn đó chưa được đặt.

3. Trừ hai lần.

=E80-D80 và kết quả: 9:15 SÁNG

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

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

Hộp thoại Định dạng Ô, lệnh Tùy chỉnh, kiểu 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 Tùy > h:mm để kết quả không bao gồm AM và CH.

Để tính toán thời gian giữa hai ngày và thời gian, bạn chỉ cần trừ một từ một ngày và thời gian còn lại. 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ả bạn muốn.

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

Ngày bắt đầu từ 01/01/2016 1:00 CH; Ngày kết thúc vào ngày 2/1/16 2:00 CH

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

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

Hộp thoại Định dạng Ô, lệnh Ngày tháng, kiểu 1:30 CH ngày 14/03/12

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 đó, chọn > ngày 14/03/12 1:30 CH. Đây không phải là ngày bạn đặt mà chỉ là một mẫu đị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ó ngày mẫu khác như14/03/01 1:30 CH.

3. Trừ hai.

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

Trong một ô khác, trừ ngày/giờ bắt đầu từ ngày/giờ kết thúc. Kết quả có thể trông giống như một số và thập phân. Bạn sẽ khắc phục điều đó 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, [h]:mm kiểu

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 Tùy chỉnh. Trong hộp Loại, nhập [h]:mm.

Chủ đề liên quan

DateDIF (Hàm DATEDIF)
NETWORKDAYS. Hàm INTL
NETWORKDAYS Các hàmngày và giờ Khác
Tínhtoán
chênh lệch giữa hai thời điểm

Bạn cần thêm trợ giúp?

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 Microsoft Office nội bộ

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

Bạn hài lòng đến đâu với chất lượng dịch thuật?
Điều gì ảnh hưởng đến trải nghiệm của bạn?

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

×