Sử dụng hàm DATEDIF khi bạn muốn tính chênh lệch giữa hai ngày. Đầu tiên, đặt ngày bắt đầu vào ô và ngày kết thúc vào ô khác. Sau đó nhập một công thức như một trong những điều sau đây.
Cảnh báo:tôi củangày bắt đầulớn hơn so vớiNgày cuối, kết quả sẽ là#NUM!.
Chênh lệch ngày
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 ở F9. Các“d”trả về số ngày đầy đủ giữa hai ngày.
Sự khác biệt trong tuần
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 chú ý/7cuối cùng. Cái đó 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 đó nhấp vàoCon số>Số thập phân: 2.
Chênh lệch tháng
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,“tôi”trả về số tháng đầy đủ giữa hai ngày.
Sự khác biệt trong năm
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. Các“y”trả về số năm đầy đủ giữa hai ngày.
Tính tuổi theo năm, tháng, 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 ai đó. Kết quả có thể giống như “2 năm, 4 tháng, 5 ngày”.
1. Sử dụng DATEDIF để tìm tổng số năm.
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. Sử dụng lại DATEDIF với “ym” để tìm tháng.
Trong một ô khác, hãy sử dụng công thức DATEDIF với“ừm”tham số. “ym” trả về số tháng còn lại trong cả năm vừa qua.
3. Sử dụng một công thức khác để tìm ngày.
Bây giờ chúng ta cần tìm số ngày còn lại. Chúng tôi sẽ làm điều này 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 trừ ngày đầu tiên của tháng kết thúc (5/1/2016) khỏi ngày kết thúc ban đầu trong ô E17 (5/6/2016). Đây là cách nó thực hiện việc này: Đầu tiên, hàm DATE tạo ngày, 1/5/2016. Nó tạo nó bằng cách sử 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ả cho hàm DATE là 1/5/2016. Sau đó, chúng tôi trừ ngày kết thúc ban đầu trong ô E17, là ngày 6/5/2016. 6/5/2016 trừ 1/5/2016 là 5 ngày.
Cảnh báo:Chúng tôi khuyên bạn không nên sử dụng đối số DATEDIF "md" vì đối số 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 trong một.
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à một công thức dài hơn để nhập, nhưng ít nhất đó là tất cả trong một.Mẹo:Nhấn ALT+ENTER để ngắt dòng trong công thức của bạn. Điều này làm cho nó dễ đọc hơn. Ngoài ra, hãy nhấn CTRL+SHIFT+U nếu bạn không thể nhìn thấy toàn bộ công thức.
Tải xuống các ví dụ của chúng tôi
Bạn có thể tải xuống một sổ làm việc ví dụ 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 bạn.
Tải xuống các ví dụ tính ngày
Tính toán ngày 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ụngHÔM NAY()hàm 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 trên máy tính của bạn 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.
Xin lưu ý rằng tại thời điểm viết bài này, ngày là ngày 6 tháng 10 năm 2016.
Sử dụng hàm NETWORKDAYS.INTL khi bạn muốn tính số ngày làm việc giữa hai ngày. Bạn cũng có thể yêu cầu nó loại trừ các ngày cuối tuần và ngày lễ.
Trước khi bắt đầu:Quyết định xem bạn có muốn loại trừ ngày lễ hay không. Nếu bạn làm như vậy, hãy nhập danh sách các ngày nghỉ trong một khu vực hoặc trang tính riêng biệt. Đặt mỗi ngày lễ trong ô riêng của mình. Sau đó chọn các ô đó, chọncông thức>xác định tên. Đặt tên cho phạm viKỳ nghỉ của tôi, và bấmĐƯỢC RỒI. Sau đó, tạo công thức bằng cách sử dụng các bước bên dưới.
1. Nhập ngày bắt đầu và ngày kết thúc.
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, hãy nhập công thức như sau:
Nhập công thức như ví dụ trên. Số 1 trong công thức thiết lập Thứ Bảy và Chủ Nhật là ngày cuối tuần và loại trừ chúng khỏi tổng số.
Lưu ý: Excel 2007 không có hàm NETWORKDAYS.INTL. Tuy nhiên, nó có NETWORKDAYS. Ví dụ trên sẽ như thế này trong Excel 2007:=NETWORKDAYS(D53,E53). Bạn không chỉ định 1 vì NETWORKDAYS cho rằng cuối tuần là Thứ Bảy và Chủ Nhật.
3. Nếu cần, hãy thay đổi 1.
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 số khác từ danh sách IntelliSense. Ví dụ: 2 thiết lập 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. Hàm NETWORKDAYS của Excel 2007 luôn giả sử cuối tuần là Thứ Bảy và Chủ Nhật.
4. Nhập tên phạm vi kỳ nghỉ.
Nếu bạn đã tạo tên phạm vi ngày lễ trong phần “Trước khi bạn bắt đầu” ở trên, hãy nhập tên đó ở cuối như thế này. Nếu bạn không có ngày nghỉ, 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à thế này:=NETWORKDAYS(D53,E53,MyHolidays).
Mẹo:Nếu không muốn tham chiếu tên dãy ngày lễ, bạn cũng có thể nhập một dãy để thay thế, chẳng hạn 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 và 2 tháng 1 năm 2016, bạn sẽ nhập chúng như sau:=NETWORKDAYS.INTL(D53,E53,1,{"1/1/2016","2/1/2016"}). Trong Excel 2007, nó sẽ trông như thế này:=NETWORKDAYS(D53,E53,{"1/1/2016","2/1/2016"})
Bạn có thể tính toán thời gian đã trôi qua bằng cách trừ đi thời gian này với thời gian khác. Đầu tiên đặt thời gian bắt đầu trong một ô và thời gian kết thúc trong một ô khác. Đảm bảo nhập toàn thời gian, bao gồm giờ, phút,và một khoảng trắng trước AM hoặc PM. Đây là cách:
1. Nhập thời gian bắt đầu và thời gian kết thúc.
Trong ví dụ này, thời gian bắt đầu ở ô D80 và thời gian kết thúc ở E80. Đảm bảo nhập giờ, phút,và một khoảng trắng trước AM hoặc PM.
2. Đặt định dạng h:mm AM/PM.
Chọn cả hai ngày và nhấn CTRL + 1 (hoặc
+ 1 trên Mac). Đảm bảo chọnPhong tục>h:mm AM/PM, nếu nó chưa được đặt.
3. Trừ hai lần.
Trong một ô khác, hãy trừ ô thời gian bắt đầu khỏi ô thời gian kết thúc.
4. Đặt định dạng h:mm.
Nhấn CTRL + 1 (hoặc+ 1 trên Mac). ChọnPhong tục>giờ: phútđể kết quả loại trừ AM và PM.
Để tính toán thời gian giữa hai ngày tháng và thời gian, bạn có thể chỉ cần trừ cái này cho cái kia. Tuy nhiên, bạn phải áp dụng định dạng cho từng ô để đảm bảo rằng Excel trả về kết quả mà bạn muốn.
1. Nhập đầy đủ hai ngày và giờ.
Trong một ô, hãy 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 ô nên có một tháng, ngày, năm, giờ, phút,và một khoảng trắng trước AM hoặc PM.
2. Đặt định dạng 14/3/12 1:30 chiều.
Chọn cả hai ô, rồi nhấn CTRL + 1 (hoặc+ 1 trên Mac). Sau đó chọnNgày>14/3/12 13:30. Đây không phải là ngày bạn sẽ đặt, đây chỉ là ví dụ về cách định dạng sẽ trông như thế nào. 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ư 3/14/011GIỜ 30 PHÚT TỐI.
3. Trừ hai cái.
Trong một ô khác, trừ ngày/giờ bắt đầu khỏi ngày/giờ kết thúc. Kết quả có thể sẽ giống như một số và số 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.
Nhấn CTRL + 1 (hoặc+ 1 trên Mac). Lựa chọnPhong tục. bên trongKiểuloại hộp[h]:mm.
chủ đề liên quan
Hàm DATEDIF
Hàm NETWORKDAYS.INTL
NETWORKDAYS
Thêm chức năng ngày giờ
Tính chênh lệch giữa hai lần