Chia sẻ công thức Excel về các tình huống tính thâm niên

 Trong thực tế quản trị nhân sự thì bài toán tính thâm nhiên của nhiều doanh nghiệp sẽ có đặc thù khác nhau tùy theo quy định của doanh nghiệp. Kết quả thời gian thâm niên công tác phục vụ cho tính phụ cấp, tính thưởng,...

Tình huống 1: Tính thâm niên chính xác số năm, số tháng và số ngày

(Tình huống này thường áp dụng để hiển thị chi tiết thâm niên công tác)

Công thức tính số năm:

=DATEDIF(A2,B2+1,"y")

Công thức tính số tháng lẻ:

=DATEDIF(A2,B2+1,"ym")

Công thức tính số ngày lẻ:

=DATEDIF(A2,B2+1,"md")

Nếu ghép trong cùng 1 công thức để ra kết quả theo cấu trúc "x năm y tháng z ngày" thì công thức là:

=DATEDIF(A2,B2+1,"y") & " năm " & DATEDIF(A2,B2+1,"ym") & " tháng " & DATEDIF(A2,B2+1,"md") & " ngày"

Tuy nhiên, sẽ có những trường hợp số năm, số tháng lẻ hoặc số ngày lẻ là 0 mà cần bỏ phần đó đi thì công thức là:

=IF(DATEDIF(A2,B2+1,"y")=0,"",DATEDIF(A2,B2+1,"y") & " năm ") & IF(DATEDIF(A2,B2+1,"ym")=0,"",DATEDIF(A2,B2+1,"ym") & " tháng ") & IF(DATEDIF(A2,B2+1,"md")=0,"",DATEDIF(A2,B2+1,"md") & " ngày")

Tình huống 2: Tính thâm niên  theo số năm, số tháng và làm tròn số ngày còn lại ra tháng

(Tình huống này thường áp dụng để hiển thị chi tiết thâm niên công tác)

Giả lập: Nếu số ngày lẻ còn lại >=15 ngày thì sẽ được cộng thêm 1 tháng và dưới 15 ngày thì sẽ không được làm tròn.

Công thức tính số năm là:

=((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")=12)+DATEDIF(A2,B2+1,"y")

Công thức tính số tháng lẻ là:

=IF((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")<12,(DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym"),0)

Nếu ghép trong cùng 1 công thức để ra kết quả theo cấu trúc: "x năm y tháng" thì công thức là:

=((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")=12)+DATEDIF(A2,B2+1,"y") & " năm " & IF((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")<12,(DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym"),0) & " tháng"

Tuy nhiên sẽ có những trường hợp số năm hoặc số tháng là 0 mà cần bỏ phần đó đi thì công thức là:

=IF(((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")=12)+DATEDIF(A2,B2+1,"y")=0,"",((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")=12)+DATEDIF(A2,B2+1,"y") & " năm ") & IF(IF((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")<12,(DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym"),0)=0,"",IF((DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym")<12,(DATEDIF(A2,B2+1,"md")>=15)+DATEDIF(A2,B2+1,"ym"),0) & " tháng")

Tình huống 3: Tính thâm niên ra số tháng (ví dụ: 15 tháng, 38 tháng, 52 tháng,...)

(Tình huống này thường áp dụng tính phụ cấp, thưởng thâm niên theo tháng. Ví dụ: từ 24 đến 36 tháng thì được hưởng....)

Công thức tính số tháng  (tính chính xác và không làm tròn phần ngày lẻ):

=DATEDIF(A2,B2+1,"m")

Công thức tính số tháng có làm tròn phần ngày lẻ. Giả sử số ngày lẻ >=15 ngày thì được làm tròn lên 1 tháng

=DATEDIF(A2,B2+1,"m")+(DATEDIF(A2,B2+1,"md")>=15)

Công thức tính số tháng không làm tròn nhưng có phần thập phân:

=YEARFRAC(A2,B2,1)*12

Tình huống số 4: Tính thâm niên ra số năm

Công thức tính ra số năm (tính chính xác và không làm tròn phần tháng lẻ)

=DATEDIF(A2,B2+1,"y")

Công thức tính ra số năm có làm tròn phần tháng (Ví dụ: Số tháng lẻ còn lại >=7 thì làm tròn lên 1 năm)

=DATEDIF(A2,B2+1,"y")+(DATEDIF(A2,B2+1,"ym")>=7)

Công thức tính ra số năm có phần thập phân (Ví dụ: 1.2 năm)

=YEARFRAC(A2,B2,1)

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334