Bài viết: Các ví dụ đơn giản về sử dụng hàm Ngày Tháng và Thời Gian

[BNTT]

Tìm một ngày, một tháng, một năm nào đó tính từ hôm nay

Nếu biết kết hợp những hàm tôi đã trình bày ở trên, bạn sẽ thấy rất dễ dàng để xác định một ngày, một tháng, một năm nào đó tính từ hôm nay (hoặc từ bất kỳ một ngày nào)

Ví dụ, bạn muốn biết Quốc Khánh năm sau là thứ mấy?
= WEEKDAY(DATE(2008, 9, 2)) = 3 (thứ Ba)

Năm 2008 mới được có mấy ngày, có nhiều người chưa kịp sửa thói quen, vẫn dùng 2007 (!),
vậy thì dùng công thức này, xác định luôn số năm là năm nay (cho chắc ăn):
= WEEKDAY(DATE(YEAR(TODAY()), 9, 2)) = 3 (thứ Ba)

Ví dụ khác, hôm nay là ngày 04/01/2008, 34 ngày nữa là ngày nào:
= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 34) = 07/02/2008 (Mồng Một Tết Mậu Tý)

Nói cho vui thôi, cái công thức trên dài lắm (thế nào cũng có người cười), đó là tôi ví dụ cách kết hợp hàm, còn nếu chỉ cần biết 34 ngày nữa là ngày nào, ta chỉ cần công thức này:
= TODAY() + 34

Xác định một ngày dựa vào tuần và thứ

Có những ngày lễ trong năm trong rơi vào một ngày nhất định, mà nó tùy thuộc vào ngày thứ mấy trong một tuần lễ nào đó của một tháng. Ví dụ, ở Mỹ, Ngày Lao Động(Labor Day) luôn là ngày thứ Hai (Monday) của tuần thứ nhất của tháng 9, đó là ngày mấy của tháng 9 năm nay? Hoặc, nếu bạn là người Công Giáo, có thể bạn biết Chủ Nhật Truyền Giáo là Chủ Nhật thứ hai của tháng 10, nhưng bạn không biết năm nay nó là ngày mấy của tháng 10...

Ở Việt Nam, thì hình như không có ngày lễ nào tính theo kiểu như vậy. Tuy nhiên, có thể có lúc nào đó, bạn có dự tính sẽ đi Nha-Trang chơi vào Chủ Nhật cuối tháng của tháng 6 chẳng hạn, bạn muốn biết hôm đó sẽ ngày bao nhiêu...

Giở lịch ra thì dễ rồi, nhưng dùng Excel được không? Chúng ta cùng thử nhé.

Chúng ta đã có Tháng và Năm rồi, vậy thử dùng ngày đầu tháng để tính tới thêm bao nhiêu ngày. Tôi sẽ dùng công thức có dạng sau:= DATE(Năm, Tháng, 1) + số ngày cộng thêm

Vấn đề là ở chỗ tính cho ra số ngày cộng thêm này. Ta sẽ dựa vào Thứ (weekday).

Một tuần thì có 7 ngày. Ở trên, ta dùng ngày 1 của tháng để làm mốc tính, vậy ta sẽ xác định xem cái Thứ của ngày cần tìm thì lớn hơn hay nhỏ hơn Thứ của ngày 1 (tôi dùng "lớn hơn" hay "nhỏ hơn" là ý muốn dùng số, ví dụ thứ Năm thì nhỏ hơn thứ Bảy nhưng lớn hơn thứ Ba); sau đó dựa vào số Thứ chênh lệch này để tìm ra số ngày cần phải cộng thêm, tính từ ngày 1.

• Nếu Thứ của ngày cần tìm nhỏ hơn Thứ của ngày 1 (của tháng), ta sẽ lấy 7 trừ đi Thứ của ngày 1 rồi cộng thêm Thứ của ngày cần tìm:

7 - WEEKDAY(DATE(Năm, Tháng, 1)) + Thứ​

Chữ Thứ in nghiêng ở trên là con số chỉ Thứ của ngày ta cần tìm.
Ví dụ, muốn tìm ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay, thì công thức trên sẽ là:
7 - WEEKDAY(DATE(2008, 9, 1)) + 2​

• Nếu Thứ của ngày cần tìm lớn hơn Thứ của ngày 1 (của tháng), ta sẽ lấy Thứ của ngày cần tìm trừ đi Thứ của ngày 1:

Thứ - WEEKDAY(DATE(Năm, Tháng, 1))​

Cũng ví dụ trên, muốn tìm ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay, thì công thức này sẽ là:
2 - WEEKDAY(DATE(2008, 9, 1))​

Số ngày cộng thêm sẽ là kết quả có được của một trong hai cái "Nếu" mà tôi vừa trình bày. Chúng ta sẽ dùng IF() để viết công thức cho hai trường hợp "Nếu" đó, và công thức đầu tiên của bài này bây giờ sẽ có dạng như sau (tôi xuống hàng để dễ nhìn):= DATE(Năm, Tháng, 1)
+ IF(Thứ < WEEKDAY(DATE(Năm, Tháng, 1)), 7 - WEEKDAY(DATE(Năm, Tháng, 1)) + Thứ, Thứ - WEEKDAY(DATE(Năm, Tháng, 1)))
Và đây là công thức để tính ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay là ngày mấy:= DATE(2008, 9, 1) + IF(2 < WEEKDAY(DATE(2008, 9, 1)), 7 - WEEKDAY(DATE(2008, 9, 1)) + 2, 2 - WEEKDAY(DATE(2008, 9, 1)))

Xong rồi ? Chưa, bạn à. Công thức trên chỉ đúng cho ngày cần tìm nằm trong tuần đầu tiên của tháng thôi. Tôi đang làm cho ví dụ tìm ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay mà...

Tuần thứ hai cộng thêm 7 ngày nữa, tuần thứ ba cộng thêm 14 ngày nữa... Vậy số ngày cần cộng thêm tuần thứ n sẽ là: (n - 1) * 7

Rồi đó, công thức của chúng ta đây:
= DATE(Năm, Tháng, 1)
+ IF(Thứ < WEEKDAY(DATE(Năm, Tháng, 1)), 7 - WEEKDAY(DATE(Năm, Tháng, 1)) + Thứ, Thứ - WEEKDAY(DATE(Năm, Tháng, 1)))
+ (n - 1) * 7

Với Năm, Tháng là tháng và năm cần tính, Thứ, tuần thứ n là những cái đã biết.

Tôi thiết kế trong Excel một cái bảng để tính như sau.

Công thức vừa làm xong tôi bỏ trong ô B7 (ô đã được định dạng theo kiểu "dddd, dd mmmm, yyyy"):
= DATE(B4, B3, 1) + IF(B2 < WEEKDAY(DATE(B4, B3, 1)), 7 - WEEKDAY(DATE(B4, B3, 1)) + B2, B2 - WEEKDAY(DATE(B4, B3, 1))) + (B1 - 1) * 7
Có hai cái công thức trong C2 và C4, dùng hàm CHOOSE() để minh họa cho con số trong cột B tương ứng. Xin xem lại ở đây.

Trong hình, tôi đang tính thử xem Chủ Nhật của tuần thứ ba của tháng Sáu năm nay là ngày mấy, và tôi có kết quả là ngày 15.

Sẵn đó, tôi tìm luôn Chủ Nhật cuối tháng 6 là ngày mấy, tôi nhập B1 = 4, B2 = 1, B3 = 6, B4 = 2008, kết quả là ngày Chủ Nhật 22/6/2008.
Hình như chưa phải Chủ Nhật cuối tháng, thì thay B1 = 5, nó ra Chủ Nhật 29/6/2008

Có cái này vui lắm, tôi tăng luôn ô B1 lên, bằng 8 (ý nói tuần thứ 8 của tháng 6 !), thử xem Excel nó cho ra bao nhiêu. Kết quả là: Chủ Nhật 20/7/2008... Công thức này cũng hay đấy chứ, tự động tính lên, chứ không báo lỗi...

Tính toán với ngày tháng năm

Chúng ta đã biết rằng, Excel quan niệm ngày tháng năm là một dãy những con số liên tục, với 1 = ngày 1/1/1900.
Cho nên một trong những cách dễ dàng nhất để tính số ngày giữa hai khoảng thời gian là lấy ngày này trừ đi ngày kia, còn kết quả hiện ra thế nào phụ thuộc vào định dạng của ô nhận kết quả (tốt nhất là định dạng số hoặc General, vì kết quả phải là một con số thì mới có lý):
= Date1 - Date2
Bên cạnh đó, còn có cách sử dụng những hàm ngày tháng của Excel:

Tính tuổi:

Nếu bạn có một ngày sinh nhật trong một ô đã được đặt tên là Birthdate, bạn sẽ nghĩ rằng công thức để tính tuổi đơn giản như sau:
= YEAR(NOW() - YEAR(Birthdate))
Nhưng... nếu tính đủ tuổi nghĩa là phải đủ tháng, đủ năm, đủ ngày, thì công thức trên chỉ đúng với người nào đã tổ chức sinh nhật rồi trong năm nay, chứ nếu chưa đến sinh nhật của họ, thì kết quả nhận được sẽ lớn hơn giá trị thực của nó.

Ví dụ, hôm nay là ngày 08/1/2008, đồng ý rằng 2008 - 1980 = 28, nhưng nếu nói bạn nào đó sinh ngày 30/12/1980 đã 28 tuổi thì... cũng hơi tội, phải không ? Em mới có 27 tuổi mấy ngày thôi, các bác à! Và, các bác bán bảo hiểm nhân thọ cũng nghĩ như thế đó!

Hóa ra không đơn giản nhỉ!
Để tính tuổi chính xác, chúng ta làm một biểu thức logic để so sánh thử xem ngày sinh nhật với ngày hôm nay, ngày nào lớn hơn:
= DATE(YEAR(NOW()), MONTH(Birthdate), DAY(Birthdate)) > TODAY()
Biểu thức trên sẽ cho ra kết quả là TRUE nếu đúng và FALSE nếu sai.
TRUE, nghĩa là ngày sinh nhật "lớn hơn" hôm nay, hay có nghĩa là chưa tới ngày sinh nhật; và FALSE là ngược lại.

Và chúng ta cũng đã biết rằng, trong Excel, TRUE tương đương với giá trị 1, và FALSE thì tương đương với giá trị 0.
Lợi dụng chính điểm này, ta sẽ ghép biểu thức trên vào công thức lấy năm trừ năm đã làm hồi nãy:
= YEAR(NOW() - YEAR(Birthdate) - (DATE(YEAR(NOW()), MONTH(Birthdate), DAY(Birthdate)) > TODAY())
Nghĩa là, nếu sinh nhật đã qua, thì lấy năm nay trừ đi năm sinh ra là đúng, còn không thì phải trừ đi 1. Ai bảo lập công thức tính tuổi là đơn giản nhỉ!

Tính giờ phút hiện hành

Không có hàm nào để trả về kết quả là giờ phút giây hiện hành. Nhưng có cách:

Ở trên tôi có nói tới hàm TODAY(), là hàm trả về ngày tháng năm hiện hành, trong đó, thời gian được xem như là 0 (nửa đêm)

Và hàm NOW(), cũng trả về ngày tháng năm hiện hành, nhưng có thêm giờ phút giây nữa.

Vậy, chỉ việc lấy hàm NOW() trừ bớt đi ngày tháng năm là ra ngay thôi:
= NOW() - TODAY()

Tìm một giờ, phút, giây nào đó tính từ lúc này

Bằng cách sử dụng hàm TIME(), hàm sẽ tự động điều chỉnh kết quả của một giá trị thời gian khi những thông số trong hàm không hợp lý (giờ > 24, phút và giây > 60). Và do đó, khi cần tính toán hoặc tìm một giá trị thời gian nào đó kể từ lúc này (hoặc bất kỳ lúc nào), người ta thường sử dụng hàm TIME().

Ví dụ, công thức sau đây sẽ cho ra kết quả là thời gian vào 12 tiếng nữa kể từ lúc này:= TIME(HOUR(NOW()) + 12, MINUTE(NOW()), SECOND(NOW()))

Không giống như hàm DATE(), bạn không thể đơn giản cộng thêm giờ, phút, hay giây trong hàm TIME(). Ví dụ công thức sau đây chỉ làm mỗi chuyện là tăng thêm 1 ngày vào ngày tháng năm và thời gian hiện tại:= NOW() + 1
Nếu bạn muốn cộng thêm giờ, phút, hay giây vào một giá trị thời gian, bạn phải tính thời gian cộng thêm đó theo một tỷ lệ của một ngày. Ví dụ, bởi vì một ngày thì có 24 giờ, nên một giờ được tính như là 1/24. Cũng vậy, bởi vì một giờ thì có 60 phút, nên một phút sẽ được tính như là 1/24/60 (của một ngày). Và cuối cùng, bởi vì có 60 giây trong một phút, nên 1 giây trong một ngày sẽ được tính bằng 1/24/60/60.

Bảng sau đây sẽ hướng dẫn cách cộng thêm n giờ, phút, hay giây vào một giá trị thời gian:

Tính tổng thời gian
Khi tính tổng thời gian, bạn nên phân biết hai trường hợp sau đây:

• Cộng thêm giờ, phút, giây: Ví dụ, bây giờ là 8 giờ, cộng thêm 2 tiếng nữa, là 10 giờ... Hoặc bây giờ là 23 giờ, cộng thêm 3 tiếng nữa là 2 giờ sáng (chớ không phải 26 giờ)... Nếu cộng kiểu này thì bạn cứ cộng bình thường, dùng hàm TIME() và nếu cần thì theo bảng hướng dẫn ở trên.
  
• Cộng tổng thời gian làm việc: Mỗi ngày tôi làm việc 18 tiếng, vậy hai ngày tôi làm mấy tiếng? là 36 tiếng. Nhưng nếu bạn dùng format bình thường dạng thời gian thì Excel nó sẽ tự quy ra (36-24) = 12:00... Để được kết quả là 36:00, bạn phải định dạng thời gian cho ô theo kiểu:

[h]:mm:ss (giờ nằm trong một cặp dấu ngoặc vuông)

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)
​

Một số bài viết có liên quan:
1/ Offset truyền kỳ (kỳ 2)
2/ Offset truyền kỳ (kỳ 1)
3/ Kết hợp các chữ ở nhiều ô thành 1 dòng chỉ trong tíc tắc
4/ SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện
5/ Sử dụng Excel để ra quyết định hằng ngày
6/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
7/ 10 lý do để học công thức Excel
8/ Làm việc với công thức mảng trong Excel
9/ VLOOKUP với Cột động
10/ Sử dụng hàm Subtotal