Công thức tính thâm niên

Chia sẻ bởi:hrspring.tides

★★★★★

‹ Công thức ngày tháng theo lịch Tổng hợp các cách tra cứu ngược... ›

Quảng cáo

Cách dùng hàm DATEDIF trong Excel để tính năm, tháng, ngày và quy đổi ra số tuần

Hàm DATEDIF trong Excel giúp tính khoảng cách giữa hai mốc thời gian theo năm, tháng, ngày. Ngoài ra, có thể kết hợp với hàm INT để quy đổi tổng số ngày thành số tuần, kể cả trường hợp muốn làm tròn khi ngày lẻ lớn hơn 4 ngày.

Nội dung chính:

Cách dùng hàm DATEDIF để tính năm, tháng, ngày
Cách tránh lỗi kết quả âm khi tự trừ YEAR, MONTH, DAY
Công thức tính tổng số tuần giữa hai ngày
Cách làm tròn: ngày lẻ từ 5 ngày trở lên tính thành 1 tuần

1. Hàm DATEDIF trong Excel dùng để làm gì?

Hàm DATEDIF được dùng để tính khoảng cách giữa hai ngày theo nhiều đơn vị khác nhau như năm, tháng, ngày, tháng lẻ hoặc ngày lẻ.

Công thức	Kết quả trả về
`=DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, "y")`	Số năm tròn
`=DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, "m")`	Tổng số tháng
`=DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, "ym")`	Số tháng lẻ sau khi đã tính đủ năm
`=DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, "d")`	Tổng số ngày
`=DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, "md")`	Số ngày lẻ sau khi đã tính đủ tháng

2. Công thức tính số năm, tháng, ngày giữa hai mốc thời gian

Giả sử ngày bắt đầu nằm ở ô B4, ngày kết thúc là ngày hiện tại, có thể dùng công thức:

=DATEDIF(B4,TODAY(),"y")&" năm "&DATEDIF(B4,TODAY(),"ym")&" tháng "&DATEDIF(B4,TODAY(),"md")&" ngày"

Công thức này giúp trả về kết quả dạng:

Ví dụ: 5 năm 3 tháng 6 ngày

3. Vì sao không nên dùng YEAR, MONTH, DAY để trừ trực tiếp?

Nhiều người thường dùng công thức:

=YEAR(TODAY())-YEAR(B4)&" năm "&MONTH(TODAY())-MONTH(B4)&" tháng "&DAY(TODAY())-DAY(B4)&" ngày"

Tuy nhiên, cách này dễ trả về kết quả âm ở phần tháng hoặc ngày nếu ngày/tháng hiện tại nhỏ hơn ngày/tháng bắt đầu.

Khuyến nghị: Nên dùng DATEDIF để Excel tự xử lý đúng phần năm, tháng và ngày lẻ.

4. Cách tính tổng số tuần giữa hai ngày

Nếu muốn tính tổng số tuần giữa ngày bắt đầu ở ô B4 và ngày hiện tại, có thể dùng:

=INT((TODAY()-B4)/7)

Công thức này lấy tổng số ngày giữa hai mốc thời gian rồi chia cho 7 để ra số tuần tròn.

5. Cách tính số tuần nếu ngày lẻ lớn hơn 4 thì tính thêm 1 tuần

Nếu quy ước rằng số ngày lẻ từ 5 ngày trở lên sẽ được tính thành 1 tuần, công thức tối ưu là:

=INT((TODAY()-B4+2)/7)

Hoặc dùng với hàm DATEDIF:

=INT((DATEDIF(B4,TODAY(),"d")+2)/7)

Giải thích:

Một tuần có 7 ngày. Nếu ngày lẻ từ 5 đến 6 ngày được tính thêm 1 tuần, ta cộng thêm 2 vào tổng số ngày trước khi chia cho 7.

6. Ví dụ minh họa

Tình huống	Kết quả tính tuần
14 ngày	2 tuần
18 ngày = 2 tuần 4 ngày	2 tuần
19 ngày = 2 tuần 5 ngày	3 tuần
20 ngày = 2 tuần 6 ngày	3 tuần

7. Công thức hoàn chỉnh nên dùng

Nếu chỉ cần trả về số tuần:

=INT((TODAY()-B4+2)/7)

Nếu muốn hiển thị kèm chữ “tuần”:

=INT((TODAY()-B4+2)/7)&" tuần"

8. Kết luận

Để tính khoảng cách thời gian trong Excel, nên dùng hàm DATEDIF thay vì tự trừ năm, tháng, ngày bằng các hàm YEAR, MONTH, DAY. Nếu cần quy đổi ra số tuần và muốn làm tròn khi ngày lẻ từ 5 ngày trở lên, công thức ngắn gọn và chính xác là:

=INT((TODAY()-B4+2)/7)