Nếu bạn đang sử dụng Excel để theo dõi thông tin về các dự án, bạn có thể muốn biết thời hạn của một dự án nhất định trong năm, tháng và ngày. Nếu bạn có ngày bắt đầu và ngày kết thúc cho mỗi dự án, bạn có thể sử dụng hàm trang tính DATEDIF để trả về thông tin theo cách mong muốn.

Ví dụ, giả sử rằng bạn có ngày bắt đầu trong ô E7 và ngày kết thúc trong ô F7. Bạn có thể tính toán sự khác biệt giữa hai ngày bằng cách sử dụng DATEDIF rất đơn giản này:

=DATEDIF(E7,F7,"d")

Hàm này trả về số ngày giữa hai ngày, miễn là ngày trong E7 nhỏ hơn hoặc bằng ngày trong F7. Đối số thứ ba, “d”, khiến DATEDIF trả về kết quả sau nhiều ngày. Bạn cũng có thể chỉ định tháng (“m”) và năm (‘y “). Tuy nhiên, đối với mục đích của ví dụ này, có một số đối số khác đặc biệt hữu ích: tháng không bao gồm năm (” ym “), ngày không bao gồm năm ( “yd”), và ngày không bao gồm tháng và năm (“md”).

Sử dụng các đối số khác nhau này, bạn có thể tạo ra một công thức sẽ trả về một câu trả lời cho biết số ngày trôi qua là năm, tháng và ngày.

(Do độ dài của các công thức trong mẹo này, tôi đã chia chúng thành các dòng riêng biệt để dễ đọc hơn một chút. Tuy nhiên, đây là một công thức duy nhất và nên được nhập như chẳng hạn vào Excel.)

=DATEDIF(E7,F7,"y") & " years, " & DATEDIF(E7,F7,"ym")

& " months, " & DATEDIF(E7,F7,"md") & " days "

Lưu ý rằng công thức này sẽ luôn trả về đơn vị số nhiều, như năm, tháng và ngày. Đối với những người muốn đúng ngữ pháp và cung cấp các đơn vị số ít khi nó được gọi, công thức sau sẽ thực hiện thủ thuật:

=IF(DATEDIF(E7,F7,"y")=1,DATEDIF(E7,F7,"y")&" year, ", DATEDIF(E7,F7,"y")&" years, ")&IF(DATEDIF(E7,F7,"ym")=1, DATEDIF(E7,F7,"ym") &" month, ",DATEDIF(E7,F7,"ym")

&" months, ")&IF(DATEDIF(E7,F7,"md")=1,DATEDIF(E7,F7,"md")

&" day",DATEDIF(E7,F7,"md")&" days")

Từ này ks trong mọi trường hợp ngoại trừ khi năm, tháng hoặc ngày bằng 0. Để loại bỏ đơn vị thích hợp khi nó bằng 0, yêu cầu một công thức thậm chí còn dài hơn:

=IF(DATEDIF(E7,F7,"y")=0,"",IF(DATEDIF(E7,F7,"y")=1, DATEDIF(E7,F7,"y")&" year, ",DATEDIF(E7,F7,"y")&" years, "))

&IF(DATEDIF(E7,F7,"ym")=0,"",IF(DATEDIF(E7,F7,"ym")=1, DATEDIF(E7,F7,"ym")&" month, ",DATEDIF(E7,F7,"ym")&" months, "))

&IF(DATEDIF(E7,F7,"md")=0,"",IF(DATEDIF(E7,F7,"md")=0, DATEDIF(E7,F7,"md")&" day ",DATEDIF(E7,F7,"md")&" days"))

Công thức này khá dài, nhưng nó sẽ chỉ trả về những đơn vị có giá trị. Do đó, thay vì trả về “0 năm, 2 tháng, 1 ngày”, nó sẽ trả về “2 tháng, 1 ngày.”

Thậm chí đây không phải là một công thức hoàn hảo, vì nó vẫn sẽ hiển thị dấu phẩy giữa các mục nhập trong một số trường hợp mà chúng không được bảo đảm. Megaformula sau đây sẽ sửa số nhiều và dấu phẩy và loại bỏ các mục nhập không.

=IF(DATEDIF(E7,F7,"y")=0,"",IF(DATEDIF(E7,F7,"y")=1, DATEDIF(E7,F7,"y")&"year",DATEDIF(E7,F7,"y")&"years"))

&IF(AND(DATEDIF(E7,F7,"y")<>0,DATEDIF(E7,F7,"ym")<>0),", ","")

&IF(DATEDIF(E7,F7,"ym")=0,"",IF(DATEDIF(E7,F7,"ym")=1, DATEDIF(E7,F7,"ym")&" month",DATEDIF(E7,F7,"ym")&" months"))

&IF(AND(OR(DATEDIF(E7,F7,"y")<>0,DATEDIF(E7,F7,"ym")<>0), DATEDIF(E7,F7,"md")<>0),", ","")&IF(DATEDIF(E7,F7,"md")=0,"", IF(DATEDIF(E7,F7,"md")=1,DATEDIF(E7,F7,"md")&" day", DATEDIF(E7,F7,"md")&" days"))

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (11360) áp dụng cho Microsoft Excel 2007, 2010 và 2013. Bạn có thể tìm phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: