Công thức tính số tháng trong excel : Trong một số nơi có quy định tính thâm niên làm việc cụ thể ra bao nhiêu năm, bao nhiêu tháng và bao nhiêu ngày tính từ thời điểm ngày vào làm đến 1 mốc cụ thể. Vậy công thức nào giúp chúng ta làm được việc này? Hãy cùng Học Excel Online tìm hiểu cách tính thời gian làm việc giữa hai thời điểm cụ thể theo ngày tháng năm trong Excel nhé. Hãy tham khảo với Mobitoolnhé.
Yêu cầu tính thâm niên làm việc theo cụ thể ngày tháng năm
Ví dụ trong 1 đơn vị có thông tin như sau:
Cách tính số tháng
Tương ứng với mỗi nhân viên đều có Ngày vào làm cụ thể. Tính tới ngày 31/12/2018 [ngày làm mốc] thì mỗi người đã có thâm niên là bao nhiêu năm, tháng, ngày?
Nguyên tắc tính khoảng thời gian thâm niên
Để tính khoảng thời gian giữa 2 thời điểm, chúng ta áp dụng theo các nguyên tắc sau:
- Hàm sử dụng: DATEDIF
- Hệ số quy đổi: 1 năm = 12 tháng
- Thời điểm xét thâm niên: Ngày làm mốc [trong ô B2] luôn lấy ngày cuối tháng hoặc ngày cuối năm.
- Công thức tính số tháng trong excel
Khi xét với ngày cuối tháng hoặc ngày cuối năm làm mốc, chúng ta đã có thể đơn giản hóa logic tính toán rất nhiều cho đơn vị Ngày. Và thực tế công việc tính thâm niên làm việc cũng thường xét trên các thời điểm đó chứ không phải là 1 ngày bất kỳ.
Công thức tính thâm niên làm việc theo số năm
Áp dụng hàm DATEDIF để tính số năm giữa 2 khoảng thời gian hàm tính số tháng trong 1 khoảng thời gian
=DATEDIF[Ngày bắt đầu, Ngày kết thúc, “y”]
Trong đó:
- Ngày bắt đầu: tính từ ô B4
- Ngày kết thúc: tại ô B2
- Lưu ý trường hợp ngày vào làm sau ngày xét làm mốc sẽ ra kết quả lỗi cho hàm DATEDIF [Ngày bắt đầu > Ngày kết thúc].
Dùng hàm IF để biện luận trước cho trường hợp này. Nếu Ngày bắt đầu > Ngày kết thúc thì trả về kết quả = 0
Công thức như sau:
C4=IF[B4>$B$2,0,DATEDIF[$B4,$B$2,”y”]]
Tính số tháng trong excel
Công thức tính thâm niên làm việc theo số tháng
Áp dụng hàm DATEDIF để tính số tháng giữa 2 khoảng thời gian:
=DATEDIF[Ngày bắt đầu, Ngày kết thúc, “m”]
Trong đó:
- Ngày bắt đầu: tính từ ô B4
- Ngày kết thúc: tại ô B2
Các trường hợp cần lưu ý:
- Biện luận bằng hàm IF cho trường hợp Ngày bắt đầu > Ngày kết thúc thì trả về kết quả là 0
- Kết quả hàm DATEDIF trả về tổng số tháng. Để tính ra số tháng còn lại trong năm thì sẽ trừ đi Số năm * 12 [số năm đã tính ở trên được quy đổi về tháng]
Công thức như sau:
D4=IF[B4>$B$2,0,DATEDIF[$B4,$B$2,”m”]-C4*12]
Công thức tính tháng
Hoặc để đơn giản hơn, các bạn có thể sử dụng công thức DATEDIF với tham số cuối là “ym” để tính số tháng còn lại khi đã biết số năm:
=DATEDIF[Ngày bắt đầu, Ngày kết thúc, “ym”]
Áp dụng vào cách tính cụ thể ta có:
D4=IF[B4>$B$2,0,DATEDIF[$B4,$B$2,”ym”]]
Kết quả của 2 cách viết là như nhau. Cách viết “ym” ngắn hơn và logic đơn giản hơn.
Công thức tính thâm niên làm việc theo số ngày
Trường hợp này tính số ngày chính là số ngày của Ngày vào làm.
Chúng ta sử dụng hàm DAY để tách phần số ngày này.
Lưu ý biện luận thêm trường hợp Ngày vào làm > Ngày mốc thì phải trả về kết quả là 0 ngày.
Công thức cụ thể là:
E4=IF[B4>B2,0,DAY[B4]]
Cách tính số tháng trong excel
Các bạn có thể tải về file mẫu trong bài viết tại địa chỉ: mobitool.net/2H5kSTC
Video hướng dẫn
Tính thứ, ngày, tháng, năm trong excel
Bạn có thể tải file thực hành tại đây.
Để lấy được ngày từ một ngày cố định, các bạn có thể sử dụng hàm DAY có sẵn trong excel.
Nếu như các bạn muốn biết một ngày bất kì nào đó là thứ mấy thì các bạn có thể sử dụng hàm TEXT. Ở ví dụ bên dưới mình nhập vào hàm TEXT hai giá trị, giá trị đầu tiên là ngày cố định còn giá trị thứ hai chính là kiểu định dạng mà ta mong muốn. Khi các bạn nhập “ddd” thì hàm sẽ trả về thứ ở dạng viết tắt.
Tương tự như với cách tính ngày, để tính tháng các bạn có thể sử dụng hàm MONTH có sẵn trong excel.
Trong một số trường hợp, nếu các bạn muốn lấy tháng nhưng ở dạng chữ viết thì có thể sử dụng hàm TEXT. Khi các bạn nhập giá trị thứ 2 là “mmm” thì hàm sẽ trả về tháng ở dạng viết tắt.
Để tính năm, các bạn đơn giản chỉ cần sử dụng hàm YEAR có sẵn.
Tính ngày cuối tháng, đầu tháng sau.
Khi các bạn đã có một ngày cố định và muốn biết cuối tháng đó là ngày bao nhiêu thì hãy sử dụng hàm EOMONTH. Hàm này nhận vào hai tham số, đầu tiên là ngày cố định và thứ hai là số tháng muốn cộng thêm. Trong trường hợp ta chỉ lấy ngày cuối của tháng hiện tại nên giá trị của tham số này sẽ là 0.
Khi các bạn đã biết cách tính ngày cuối tháng thì khi các bạn muốn tính ngày đầu tháng sau chỉ cần làm tương tự và cộng thêm 1. Vì trước đó đã là ngày cuối tháng nên khi cộng với 1 sẽ ra kết quả là ngày đầu tháng sau.
Tính cộng ngày, tháng, năm trong excel
Các bạn có thể sử dụng hàm EDATE nếu các bạn muốn biết ngày hiện tại của 5 năm sau sẽ có giá trị là bao nhiêu. Hàm EDATE nhận vào 2 tham số, đầu tiên là ngày cố định và thứ 2 là số tháng. Ở trường hợp này, mình muốn mốc thời gian ở 5 năm sau nên sẽ lấy 12 nhân với 5 [1 năm có 12 tháng].
Tính cộng 15 ngày làm việc
Nếu các bạn là một người quản lý và có một nhân viên đến thử việc, các bạn muốn biết sau 15 ngày thử việc của nhân viên đó sẽ là ngày bao nhiêu để tiện sắp xếp thì hàm WORKDAY.INTL sẽ là lựa chọn số 1. Hàm WORKDAY.INTL nhận vào 4 tham số lần lượt là ngày bắt đầu, số ngày muốn cộng thêm, ngày nghỉ cuối tuần [thông thường là thứ 7 và chủ nhật], ngày nghỉ lễ [ví dụ như Tết, Quốc khánh,…]. Các bạn lưu ý ở tham số cuối cùng nhớ phải ấn nút F4 để cố định các ngày nghỉ lễ để khi các bạn kéo xuống để áp dụng cho các ô bên dưới sẽ không có sai xót.
Tính ngày thứ 2 gần nhất
Khi các bạn đã có một ngày cố định và muốn tìm ngày thứ 2 gần nhất là ngày nào thì có thể sử dụng hàm WEEKDAY theo cách như bên dưới. Trong trường hợp mình muốn tìm ngày thứ 2 gần nhất, hàm WEEKDAY sẽ có tham số đầu tiên là ngày đã có còn tham số thứ 2 sẽ chọn số 3.
Tính tổng số ngày
Nếu các bạn muốn biết từ một ngày này đến một ngày khác đã trải qua tổng cộng bao nhiêu ngày thì cách làm rất đơn giản. Các bạn chỉ cần lấy giá trị của ngày lớn hơn trừ cho giá trị của ngày nhỏ hơn. Ví dụ minh họa như hình bên dưới.
Tính số ngày làm việc
Trong trường hợp các bạn muốn biết một người đã làm được bao nhiêu ngày kể từ khi bắt đầu làm [trừ ngày nghỉ cuối tuần và nghỉ lễ] thì có thể sử dụng hàm NETWORKDAYS. Trong hàm này, các tham số lần lượt là ngày bắt đầu, ngày kết thúc, các ngày nghỉ lễ. Chúng ta không cần ghi thêm thông tin các ngày nghỉ cuối tuần bởi vì hàm này đã tự động trừ những ngày đó ra.
Tính số năm + số tháng
Nếu các bạn không muốn tính bằng số ngày thì cũng có thể tính bằng số năm cộng với số tháng. Các bạn có thể sử dụng hàm DATEDIF, lưu ý rằng khi các bạn nhập thì excel sẽ không tự động gợi ý hàm này cho bạn. Ở nửa đầu tiên, DATEDIF[B13,C13,“y”] sẽ trả về số năm tính từ ngày bắt đầu đến ngày kết thúc còn DATEDIF[B13,C13,“ym”] sẽ trả về số tháng còn dư ra [không đủ 1 năm]. Các bạn nên lưu ý thêm nếu sử dụng “m” thay cho “ym” thì sẽ trả về tổng số tháng tính từ ngày bắt đầu đến ngày kết thúc.
Tính số tuổi
Khi các bạn đã có một ngày sinh và muốn biết đến hiện tại người đó đã bao nhiêu tuổi thì có thể sử dụng hàm DATEDIF. Hàm này sẽ nhận vào 3 tham số đầu tiên là ngày đã có, thứ 2 là ngày bạn muốn tính đến từ ngày đã có [trong trường hợp này mình muốn tính tuổi nên sẽ dùng hàm TODAY để trả về ngày hiện tại] và cuối cùng là định dạng. Ở đây mình dùng “y” để kết quả trả về sẽ là số năm, đồng nghĩa với số tuổi. Có thể nói việc tính số tuổi chính là việc tính số năm.
Tính số tháng
Tương tự như ở phía trên, nếu các bạn muốn tính số tháng cũng sẽ dùng hàm DATEDIF. Tuy nhiên bởi vì mình muốn tính số tháng nên tham số thứ 3 mình sẽ ghi là “m”. Khi đó kết quả trả về sẽ là số tháng tính từ ngày bắt đầu.
Tính số ngày
Cuối cùng là cách tính số ngày kể từ một ngày nào đó. Các bạn cũng có thể sử dụng hàm DATEDIF trong trường hợp này, chỉ cần thay tham số thứ 3 bằng “d”.