Học Excel- Bài 19: Tính toán sự chênh lệch giữa 2 ngày

Dùng hàm DATEDIF khi bạn muốn tính sự chênh lệch giữa 2 ngày. Trước tiên, đặt ngày bắt đầu vào một ô, ngày kết thúc vào ô khác. Sau đó, nhập công thức giống như một trong số tác vụ dưới đây.

Microsoft Excel Microsoft Excel cho Android Microsoft Excel cho iOS Microsoft Excel Online

Cảnh báo: Nếu Start_date lớn hơn End_date, kết quả sẽ là l#NUM!.

Chênh lệch trong ngày

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

Chênh lệch trong tuần

Ở ví dụ này, ngày bắt đầu nằm trong ô D13, ngày kết thúc ở E13. d trả về số ngày. Thế nhưng, lưu ý /7 ở cuối. Nó chia số ngày cho 7 bởi một tuần có 7 ngày. Lưu ý kết quả này cũng cần được định dạng là một số. Nhấn CTRL+1. Sau đó, click Number > Decimal places: 2.

Chênh lệch trong tháng

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

Chênh lệch trong năm

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

Tính toán thâm niên trong năm, tháng và ngày tích lũy

Bạn cũng có thể tính thâm niên hay thời gian sử dụng dịch vụ của ai đó. Kết quả có thể là 2 năm, 4 tháng, 5 ngày.

1. Dùng DATEDIF để tìm tổng số năm

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

2. Dùng DATEDIF cùng ym để tìm tháng

Trong ô khác, dùng công thức DATEDIF với tham số ym. Ym trả về số tháng còn lại ở năm vừa qua.

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

Giờ chúng ta cần tìm số ngày còn lại bằng cách viết một kiểu công thức khác như hình minh họa trên. Công thức này trừ ngày đầu tiên của tháng cuối (5/1/2016) từ ngày kết thúc ban đầu trong ô E17 (5/6/2016). Đây là cách nó thực hiện: Đầu tiên, hàm DATE tạo ngày, 5/1/2016. Nó dùng năm và tháng trong ô E17. Tiếp theo, 1 đại diện cho ngày đầu tiên của tháng. Kết quả cho hàm DATE là 5/1/2016. Sau đó, công thức trừ đi ngày cuối ban đầu ở ô E17. Nghĩa là 5/6/2016 trừ 5/1/2016 là 5 ngày.

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

Bạn có thể đặt toàn bộ 3 phép tính trong một ô như ví dụ trên bằng cách dùng dấu &, dấu trích dẫn và text. Công thức này khá dài. Mẹo: Nhấn ALT+ENTER để ngắt dòng trong công thức. Điều này giúp nó dễ đọ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.

Cảnh báo: Bạn không nên dùng đối số “md” của DATEDIF bởi nó có thể cho kết quả tính toán không chính xác.

Phép tính ngày và thời gian khác

Tính từ ngày hôm nay tới ngày khác

Như đã thấy ở trên, hàm DATEDIF tính sự khác biệt 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ể dùng hàm TODAY() bên trong công thức này. Khi dùng hàm TODAY(), Excel sử dụng ngày hiện tại của máy tính cho ngày. Nhớ rằng, điều này sẽ thay đổi khi bạn mở lại file ở lần tới.

Tính toán ngày làm việc có hoặc không có ngày lễ

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 bỏ ngày cuối tuần và ngày lễ.

Trước khi bắt đầu: Chọn ngày lễ muốn bỏ qua bằng cách nhập danh sách ngày đó vào vùng hay bảng tính riêng. Đặt từng ngày lễ vào ô riêng. Sau đó, chọn những ô đó, chọn Formulas > Define Name. Đặt tên phạm vi MyHolidays > click OK. Sau đó tạo công thức theo những bước sau:

1. Gõ ngày bắt đầu và ngày kết thúc

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

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

Gõ công thức như ví dụ trên. 1 trong công thức này được thiết lập là ngày cuối tuần (thứ 7 & Chủ Nhật), và loại bỏ chúng khỏi kết quả tính tổng.

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

3. Nếu cần, thay đổi 1

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

Nếu đang dùng Excel 2007, bỏ qua bước này. Hàm NETWORKDAYS của Excel 2007 luôn giả định cuối tuần vào thứ 7 & Chủ Nhật.

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

Nếu đã tạo một tên phạm vi ngày lễ trong phần “Trước khi bắt đầu” ở trên, hãy nhập nó vào phần cuối cùng. Nếu không có ngày lễ, bạn có thể bỏ dấu phẩy và MyHolidays. Nếu dùng Excel 2007, ví dụ trên sẽ như sau:

=NETWORKDAYS(D53,E53,MyHolidays).

Mẹo: Nếu không muốn tham chiếu tới tên phạm vi ngày lễ, bạn 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ễ vào trong công thức. Ví dụ, nếu ngày lễ rơi vào 1/1 và 2/1 năm 2016, bạn sẽ nhập chúng như sau:

=NETWORKDAYS.INTL(D53,E53,1,{"1/1/2016","1/2/2016"}). Trong Excel 2007, nó sẽ trông như thế này =NETWORKDAYS(D53,E53,{"1/1/2016","1/2/2016"})

Tính thời gian đã trôi qua

Bạn có thể tính thời gian đã trôi qua trên Excel giống như một phép tính trừ đơn giản. Đầu tiên, nhập thời gian bắt đầu vào một ô và ngày kết thúc vào ô khác. Đảm bảo nhập thời gian đầy đủ, bao gồm giờ, phút và khoảng cách trước AM hoặc PM. Các bước thực hiện như sau:

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

Ở ví dụ này, thời gian bắt đầu ở ô D80, thời gian kết thúc ở E80.

2. Thiết lập thời gian theo định dạng giờ/phút sáng/chiều

Chọn cả hai ngày và nhấn CTRL + 1 hoặc + 1 trên Mac. Đảm bảo đã chọn Custom > h:mm AM/PM.

3. Thực hiện phép trừ thời gian

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

4. Thiết lập định dạng giờ:phút

Nhấn CTRL+1 hoặc Image of the MAC Command button icon +1 trên Mac. Chọn Custom > h:mm để kết quả không bao gồm AM và PM.

Tính thời gian đã qua giữa hai ngày và giờ

Bạn cũng chỉ cần thực hiện phép trừ tương tự như trên. 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 đầy đủ 2 ngày và giờ

Nhập đầy đủ ngày/giờ bắt đầu vào một ô. Tương tự ở ô khác, nhập ngày/giờ kết thúc. Mỗi ô cần có đủ tháng, ngày, năm, giờ, phút và khoảng cách trước AM hoặc PM.

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

Chọn cả hai ô, rồi nhấn CTRL + 1 hoặc Image of the MAC Command button icon + 1 trên Mac. Sau đó, chọn Date > 3/14/12 1:30 PM. Đây không phải ngày bạn chọn, nó chỉ là một ví dụ về cách định dạng. Lưu ý rằng ở phiên bản trước Excel 2016, định dạng này có thể khác mẫu, ví dụ: 3/14/01 1:30 PM.

3. Trừ hai ngày