Excel's Date and Time Functions - Các Hàm về Ngày Tháng và Thời Gian

[BNTT]

Lập bảng chấm công và tính lương

Hình sau đây là một bảng chấm công và tính lương đã hoàn chỉnh.
Bảng chấm công này được tính 7 ngày một lần (tương đương với 1 tuần), lương tính theo giờ (làm bao nhiêu giờ hưởng bao nhiêu tiền, ban ngày cũng như ban đêm), có phần tính riêng nếu làm vượt số giờ quy định hoặc làm vào thứ Bảy, Chủ Nhật; và nếu làm vào ngày nghỉ lễ, cũng được tính lương riêng.

​

Trước khi bắt đầu thực hiện, xin nói sơ qua một chút:

• Giờ làm việc theo quy định: là số giờ làm việc tối thiểu trong một tuần để được hưởng lương cơ bản, chỉ tính tổng số giờ mà không phân biệt là làm ban ngày hay ban đêm
  
• Giờ làm việc ngoài giờ: là số giờ làm việc vượt số giờ làm tối thiểu, hoặc làm vào ngày Thứ Bảy, Chủ Nhật
• Giờ làm việc trong ngày nghỉ: là số giờ làm việc trong những ngày nghỉ lễ theo quy định (ở VN là 1/1, Tết Cổ Truyền, 30/4, 1/5, Giỗ tổ Hùng Vương, 2/9)

Nhập số liệu

Chúng ta sẽ cung cấp cho bảng tính này những số liệu sau (ở phần bảng phía trên):
​

• Tên nhân viên...
• Số giờ làm việc theo quy định trong tuần: Là số giờ làm việc tối thiểu trong một tuần để tính lương cơ bản. Nhập theo dạng hh:mm. Ở đây, ô D3 dùng định dạng là [hh]:mm để hiển thị chính xác số giờ (ô D3, = 40:00, tức 40 giờ mỗi tuần)
• Lương cơ bản mỗi giờ: Là số tiền trả cho mỗi giờ làm việc (ô D4, = 50.000)
• Mức lương làm ngoài giờ so với lương cơ bản: Là hệ số nhân với lương cơ bản để tính cho số giờ làm việc ngoài giờ (ô D5, = 1.5, tức gấp rưỡi lương cơ bản)
• Mức lương làm vào ngày nghỉ lễ so với lương cơ bản: Là hệ số nhân với lương cơ bản để tính cho số giờ làm việc trong những ngày nghỉ lễ (ô D6, = 2, tức gấp đôi lương cơ bản)

Ở phần bảng để tính toán số giờ làm việc, chúng ta cần cung cấp số liệu cho những cột sau:
​

• Date: Ngày trong tuần, được định dạng theo kiểu dddd mmm dd, yyyy (chỉ cần nhập ngày, Excel sẽ tự động điền thứ vào)
  
• Giờ bắt đầu vào làm việc: Là giờ bắt đầu làm việc trong ngày (không nhất thiết phải là một giờ nào cố định)
  
• Giờ ăn trưa: Là giờ bắt đầu nghỉ giữa giờ để ăn (ở đây dùng chữ "ăn trưa" nhưng không nhất thiết phải là buổi trưa, chính xác là giờ nghỉ giải lao để ăn)
  
• Giờ bắt đầu làm sau ăn trưa: Là giờ làm việc tiếp, sau khi đã ăn và nghỉ giữa giờ
  
• Giờ ra về: Là giờ kết thúc làm việc
  

Lập công thức tính toán (cho hàng 9, sau đó kéo công thức xuống thêm 6 hàng nữa - các ô trong khối cell F9:I15 đều được định dạng theo kiểu [hh]:mm)

Đầu tiên, tính Tổng số giờ làm việc trong một ngày (cột F):

Ta thấy rằng, nếu giờ làm việc là ban ngày hoàn toàn, thì số giờ ra về (cột E) lớn hơn số giờ vào làm (cột B); nhưng nếu làm ca đêm, thì số giờ ra về (cột E) có thể sẽ nhỏ hơn số giờ vào làm (cột B, vì có thể người đó ra về vào sáng hôm sau), do đó, công thức của chúng ta sẽ là:

IF(E9 < B9, 1 + E9 - B9, E9 - B9)

Người công nhân này có thể sẽ nghỉ giữa giờ để ăn, nghỉ giải lao, chúng ta phải trừ số giờ này ra, cũng theo suy luận như trên:

IF(D9 < C9, 1 + D9 - C9, D9 - C9)

Vậy công thức tính tổng số giờ làm việc trong ngày sẽ là (ở F9):

F9 = IF(E9 < B9, 1 + E9 - B9, E9 - B9) - IF(D9 < C9, 1 + D9 - C9, D9 - C9)
​

Tiếp theo, chúng ta nhảy sang tính số giờ làm việc ngoài giờ (cột H). Ở đây ta chỉ xét nếu làm vào thứ Bảy hoặc Chủ Nhật thôi, còn nếu làm vượt số giờ quy định, sẽ tính sau.

H9 = IF(OR(WEEKDAY(A9) = 7, WEEKDAY(A9) = 1), F9, 0)
​

Dùng hàm WEEKEND() để dò Thứ của cột A, nếu rơi vào ngày thứ Bảy hoặc Chủ Nhật thì lấy số giờ đã tính được ở cột F, còn không thì thôi.

Tiếp tục, sang cột I, ta tính số giờ làm việc trong những ngày nghỉ lễ. Giả sử ta đã có một danh sách những ngày nghỉ lễ theo quy định (1/1, 30/4, v.v...), và danh sách này là một vùng đã được đặt tên là HOLIDAYS. Lấy ngày ở cột A đem dò với danh sách này, nếu trùng với ngày nghỉ thì lấy số giờ tính được ở cột F, còn không thì thôi. Ở đây tôi dùng công thức mảng:

I9 = {=SUM(IF(A9 = HOLIDAYS, 1, 0)) * F9}
​

Cuối cùng, quay lại cột G, ta sẽ có số giờ làm việc theo quy định sẽ là tổng số giờ làm việc trừ đi số giờ làm việc ngoài giờ và số giờ làm việc trong những ngày nghỉ:

G9 = F9 - H9 - I9
​

Tính tổng số giờ làm việc trong tuần (khối cell ở góc dưới bên trái)

Số giờ làm việc trong tuần (B18) là tổng số giờ làm việc trong tuần mà chưa xét đến số giờ quy định hay số giờ làm ngoài giờ (cột F):

B18 = SUM(F9:F15)
​

Số giờ làm theo quy định (B19): Ở D3, ta đã nhập số giờ làm theo quy định (= 40), đem số giờ làm theo quy định của nhân viên này (cột G) so với số ở D3, ta có công thức:

B19 = IF(SUM(G9:G15) > D3, D3, SUM(G9:G15))
​

Nếu tổng số giờ làm việc của người này nhiều hơn số giờ đã quy định ở D3, thì lấy bằng số giờ quy định (còn dư sẽ chuyển sang số giờ làm ngoài giờ), còn nếu không thì lấy chính cái tổng số giờ làm việc này.

Số giờ làm ngoài giờ (B20) là số giờ làm trong những ngày thứ Bảy và Chủ Nhật (cột H), cộng với số dư của số giờ làm theo quy định (nếu có):

B20 = SUM(H9:H15) + IF(SUM(G9:G15) > D3, SUM(G9:G15) - D3, "0:00")
​

Và cuối cùng, số giờ làm trong ngày nghỉ lễ (B21) chính là tổng của số giờ làm nơi cột I:

B20 = SUM(I9:Ị)
​

Tính lương (khối cell ở góc dưới bên phải)

Lương cơ bản (F18): Lấy tổng số giờ làm việc theo quy định ở B19, nhân với mức lương cơ bản ở D4 là ra chứ gì?

Ta thử nhé: ở B19 đang là 40:00, và D4 = $ 50,000.00; vậy F18 = B19 * D4 = $ 83,333.33 ... Cái gì kỳ thế này ? Sao lại có số lẻ, và sao ít thế ?

Đây là cái sai lầm rất nhiều người mắc phải. Nhắc lại, định dạng trong B19 đang là dạng [hh]:mm, nghĩa là định dạng thời gian, chứ không phải là một con số. Giá trị của 40:00 chính là 1.666666667 chứ không phải là 40 !

Khi gặp những giá trị thời gian này, bạn phải nhân nó với 24 (là số giờ có trong 1 ngày) để quy đổi nó trở thành một con số thực (real number):

Vậy công thức trong F18 phải là:

F18 = B19 * D4 * 24 (= $ 2,000,000.00)
​

Ta nhẩm thử nhé: 40 giờ x 50.000 / giờ = 2 triệu (đúng rồi!)


Lương ngoài giờ (F19): Tương tự F18, lấy số giờ làm ngoài giờ (B20) nhân với lương cơ bản (D4) và nhân với hệ số của lương ngoài giờ (D5), và nhớ nhân với 24:

F19 = B20 * D4 * D5 * 24 (= $ 900,000.00)
​

Lương ngày nghỉ (F20): Tương tự F19, lấy số giờ làm ngày nghỉ (B21) nhân với lương cơ bản (D4) và nhân với hệ số của lương ngày nghỉ (D6), và nhân với 24:

F20 = B21 * D4 * D6 * 24 (= $ 875,000.00)
​

Tổng lương trong tuần (F21): Cái này thì quá dễ rồi, lấy 3 con số lương ở trên cộng lại:

F21 = F18 + F19 + F20 (= $ 3,775,000.00)
​

---------- HẾT PHẦN EXCEL'S DATE AND TIME FUNCTIONS ----------
​

​