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

Sử dụng hàm DATEDIF khi mà bạn mong muốn tính sự chênh lệch giữa 2 ngày. Đầu tiên, đặt ngày bắt tay vào làm vào một ô, ngày kết thúc vào ô khác. Sau đấy, nhập bí quyết chẳng hạn như một trong số tác vụ phía dưới.

Microsoft Excel Microsoft Excel cho Android Microsoft Excel cho iOS Microsoft Excel On-line

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

Chênh lệch trong tuần

Chênh lệch tuần

Ở VD này, ngày bắt tay vào làm nằm trong ô D13, ngày kết thúc ở E13. d trả về số ngày. Tuy vậy, chú ý /7 ở cuối. Nó chia số ngày cho 7 bởi một tuần có 7 ngày. Chú ý kết quả này cũng cần được định dạng là một vài người. Nhấn CTRL+1. Sau đấy, click on Quantity > Decimal puts: 2.

Chênh lệch trong tháng

Chênh lệch tháng

VD, ngày bắt tay vào làm nằm ở ô D5, ngày kết thúc ở ô E5. Trong bí quyết này, m trả về số tháng rất đầy đủ giữa 2 ngày.

Chênh lệch của năm

Chênh lệch năm

Ở VD này, ngày bắt tay vào làm 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 của 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 dùng dịch vụ của ai đấy. 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

Hàm DATEDIF trong Excel

Trong VD này, ngày bắt tay vào làm nằm ở ô D17, ngày kết thúc ở E17. y trả về tổng số năm giữa 2 ngày.

2. Sử dụng DATEDIF cùng ym để tìm tháng

Cách dùng hàm DATEDIF

Trong ô khác, sử dụng bí quyết DATEDIF với tham số ym. Ym trả về số tháng còn lại ở năm vừa qua.

3. Sử dụng bí quyết khác để tìm ngày

Cách dùng hàm tính ngày trong Excel

Giờ chúng ta cần tìm số ngày còn lại bằng việc viết một kiểu bí quyết khác như hình ảnh minh họa trên. Bí quyết này trừ ngày trước 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 chính là cách nó thực hiện: Trước tiên, hàm DATE tạo ngày, 5/1/2016. Nó sử dụng năm và tháng trong ô E17. Kế tiếp, 1 đại diện cho ngày trước tiên của tháng. Kết quả cho hàm DATE là 5/1/2016. Sau đấy, bí quyết trừ đi ngày cuối ban đầu ở ô E17. Có nghĩa là 5/6/2016 trừ 5/1/2016 là 5 ngày.

4. Tùy chọn: Kết hợp 3 bí quyết thành một

Dùng hàm DATEDIF tất cả trong một

Bạn sẽ đặt tất cả 3 phép tính trong một ô như VD trên bằng việc sử dụng dấu &, dấu trích dẫn và textual content. Bí quyết này khá dài. Bí kíp: Nhấn ALT+ENTER để ngắt dòng trong bí quyết. Việc này giúp nó dễ đọc hơn. Ngoài những điều ấy ra, nhấn CTRL+SHIFT+U nếu như bạn không thể thấy tất cả bí quyết.

Cảnh báo: Bạn không nên sử dụng đối số “md” của DATEDIF bởi nó có thể cho kết quả tính toán không chuẩn 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 tay vào làm và ngày kết thúc. Thế nhưng, thay vì nhập ngày nhất định, bạn cũng có thể sử dụng hàm TODAY() bên trong bí quyết này. Khi sử dụng hàm TODAY(), Excel dùng ngày hiện tại của máy tính cho ngày. Nhớ rằng, việc này sẽ thay đổi khi mà bạn mở lại tệp ở lần tới.

Phép tính từ ngày hôm nay tới ngày khác trong Excel

Tính toán ngày thực hiện những công việc có hoặc không có ngày lễ

Sử dụng hàm NETWORKDAYS.INTL khi mà bạn mong muốn tính toàn số ngày thực hiện những công 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 lúc bắt đầu: Chọn ngày lễ mong muốn bỏ qua bằng việc nhập danh sách ngày đấy vào vùng hay bảng tính riêng. Đặt từng ngày lễ vào ô riêng. Sau đấy, chọn những ô đấy, chọn Formulation > Outline Identify. Đặt tên phạm vi MyHolidays > click on OK. Sau đấy tạo bí quyết theo những bước sau:

1. Gõ ngày bắt tay vào làm và ngày kết thúc

Nhập ngày vào Excel

Trong VD này, ngày bắt tay vào làm nằm ở ô D53, ngày kết thúc ở ô E53.

2. Trong ô khác, nhập bí quyết như sau:

Nhập công thức Excel

Gõ bí quyết như VD trên. 1 trong bí quyết này được cài đặt 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.

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

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

Thay đổi giá trị ngày

Nếu như 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. VD, 2 cài đặt Chủ Nhật & thứ Hai làm ngày cuối tuần.

Nếu như đang sử 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ễ

Gõ tên phạm vi ngày lễ trong Excel

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

=NETWORKDAYS(D53,E53,MyHolidays).

Bí kíp: Nếu như không mong muốn tham chiếu tới tên phạm vi ngày lễ, bạn sẽ nhập một phạm vi thay thế, như D35:E:39. Hoặc bạn sẽ nhập từng ngày lễ vào trong bí quyết. VD, nếu như ngày lễ rơi vào 1/1 và 2/1 năm 2016, bạn có thể 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 sẽ tính thời gian đã trôi qua trên Excel chẳng hạn như một phép tính trừ dễ dàng. Trước tiên, nhập thời gian bắt tay vào làm vào một ô và ngày kết thúc vào ô khác. Bảo đảm nhập thời gian rất đầy đủ, gồm có 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 tay vào làm và kết thúc

Nhập dữ liệu ngày vào Excel

Ở VD này, thời gian bắt tay vào làm ở ô D80, thời gian kết thúc ở E80.

2. Cài đặt thời gian theo định dạng giờ/phút sáng/chiều

Thiết lập định dạng trong Excel

Chọn cả hai ngày và nhấn CTRL + 1 hoặc Image of the MAC Command button icon + 1 trên Mac. Bảo đảm đã chọn Customized > h:mm AM/PM.

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

Thực hiện phép trừ trong Excel

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

4. Cài đặt định dạng giờ:phút

Thiết lập định dạng giờ và phút

Nhấn CTRL+1 hoặc Image of the MAC Command button icon +1 trên Mac. Chọn Customized > h:mm để kết quả không gồm có 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ừ giống như là trên. Thế nhưng, bạn phải áp dụng định dạng cho từng ô để bảo đảm Excel trả về kết quả ước muốn.

1. Điền đầy đủ 2 ngày và giờ

Nhập ngày và giờ vào bảng tính Excel

Điền đầy đủ ngày/giờ bắt tay vào làm vào một ô. Tương tự ở ô khác, nhập ngày/giờ kết thúc. Mỗi ô nê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

Thiết lập định dạng ngày trong Excel

Chọn cả hai ô, rồi nhấn CTRL + 1 hoặc Image of the MAC Command button icon+ 1 trên Mac. Sau đấy, 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 VD về cách định dạng. Chú ý rằng ở phiên bản trước Excel 2016, định dạng này có thể khác mẫu, VD: 3/14/01 1:30 PM.

3. Trừ hai ngày

Trừ hai ngày và giờ trong Excel

Ở ô khác, trừ ngày/thời gian xuất phát từ ngày/thời gian kết thúc. Kết quả có thể là số số thập phân. Bạn có thể sửa lỗi này ở bước kế tiếp.

4. Cài đặt định dạng [h]:mm

Thiết lập định dạng ngày và giờ

Nhấn CTRL+1 hoặc Image of the MAC Command button icon +1 trên Mac. Chọn Customized. Trong Sort, nhập [h]:mm.