Công thức thời gian trong Excel: Tính toán, định dạng và thao tác thời gian trong Excel

Excel giúp việc làm việc với con số trở nên dễ dàng, nhưng xử lý thời gian (theo quan điểm của tôi) lại là câu chuyện khác. Phần gây rối (tôi sẽ trình bày bên dưới) là Excel lưu trữ và xử lý giá trị thời gian dưới dạng phần lẻ của ngày, điều này có thể dẫn đến kết quả không như mong đợi nếu bạn chưa quen.

Trong hướng dẫn này, tôi sẽ dẫn bạn qua mọi thứ từ tạo và định dạng giá trị thời gian đến giải quyết các vấn đề thường gặp như khoảng thời gian âm và tràn thời gian. Kết thúc bài, bạn sẽ biết cách xây dựng bảng tính thông minh và đáng tin cậy hơn cho lập kế hoạch dự án, bảng lương, hay bất kỳ công việc nào bạn đang thực hiện.

Tìm hiểu hệ thống ngày tháng của Excel

Chúng ta cần bắt đầu với hệ thống ngày tháng của Excel. Excel lưu trữ ngày tháng dưới dạng số sê-ri tuần tự, bắt đầu từ ngày 1 tháng 1 năm 1900, được biểu diễn bằng giá trị 1. Mỗi ngày tiếp theo tăng số sê-ri thêm 1. Chẳng hạn:

• Ngày 1 tháng 1 năm 1900 = 1

• Ngày 2 tháng 1 năm 1900 = 2

• Ngày 31 tháng 12 năm 2023 = 45205

Giá trị thời gian được lưu dưới dạng phần lẻ của một ngày. Ví dụ:

• 6:00 AM = 0.25 (6 giờ là 25% của một ngày 24 giờ)

• 12:00 PM = 0.5

• 6:00 PM = 0.75

Kết hợp ngày và thời gian đơn giản như cộng hai giá trị lại với nhau. Ví dụ, công thức dưới đây kết hợp một ngày và thời gian:

=A2 + TIME(10, 30, 0)

Nếu A2 chứa 45205 (ngày 31 tháng 12 năm 2023), kết quả sẽ là 45205.4375, biểu thị 10:30 AM ngày 31 tháng 12 năm 2023.

Cách tạo giá trị thời gian trong Excel

Hãy xem hai cách phổ biến nhất để tạo giá trị thời gian hợp lệ trong Excel: dùng hàm TIME() và chuyển đổi văn bản với TIMEVALUE().

Dùng hàm TIME()

Tôi đã nhắc đến hàm này trước đó. Hàm TIME() cho phép bạn tạo một giá trị thời gian từ các thành phần riêng: giờ, phút và giây. Cú pháp như sau:

=TIME(hour, minute, second)

Ví dụ, nếu bạn muốn biểu diễn 10:30 AM, bạn có thể viết:

=TIME(10, 30, 0)

Hàm này tạo một giá trị thời gian Excel đúng cho 10:30 AM, được lưu nội bộ là 0.4375 (vì 10 giờ 30 phút là 43,75% của một ngày 24 giờ).

Excel linh hoạt với các giá trị bạn nhập. Nếu vượt quá giới hạn thời gian thông thường, Excel sẽ cuộn (roll over) phần dư. Chẳng hạn:

=TIME(27, 0, 0)

Hàm này trả về 3:00 AM vì 27 giờ tương đương một ngày đầy đủ (24 giờ) cộng 3 giờ. Tương tự:

=TIME(10, 75, 0)

Điều này được hiểu là 11:15 AM, vì 75 phút trở thành 1 giờ 15 phút.

Các trường hợp biên này rất quan trọng để hiểu, đặc biệt khi bạn tạo giá trị thời gian một cách động bằng công thức.

Dùng hàm TIMEVALUE()

Bạn có thể chuyển đổi văn bản thành thời gian với TIMEVALUE(). Vì vậy, nếu bảng tính của bạn có thời gian được viết dạng văn bản, như “2:30 PM” hoặc “14:45”, Excel có thể không tự động coi đó là giá trị thời gian. Đó là lúc TIMEVALUE() phát huy tác dụng.

=TIMEVALUE("2:30 PM")

Hàm này chuyển chuỗi văn bản thành một giá trị thời gian trong Excel. Bạn có thể dùng nó trong tính toán như bất kỳ thời gian nào khác.

Đáng lưu ý sự khác nhau giữa TIME() và TIMEVALUE():

• TIME() tạo thời gian từ các số riêng lẻ (giờ, phút, giây)

• TIMEVALUE() chuyển chuỗi văn bản thành thời gian

Cả hai đều trả về giá trị mà Excel nhận diện là thời gian hợp lệ, điều bạn cần để tính toán và định dạng chính xác.

Cách tính chênh lệch thời gian

Khi đã có các giá trị thời gian hợp lệ trong Excel, một tác vụ phổ biến là tính xem bao nhiêu thời gian đã trôi qua giữa hai thời điểm. Dù bạn đang đo độ dài ca làm việc hay tính thời gian hoàn thành một nhiệm vụ, Excel cung cấp nhiều cách để ra đáp án.

Hãy khám phá những cách hữu ích nhất, từ phép trừ đơn giản đến các mẹo nâng cao.

Trừ trực tiếp hai thời điểm

Cách đơn giản nhất để tính chênh lệch thời gian là lấy thời gian kết thúc trừ thời gian bắt đầu. 

Ví dụ, nếu ô A1 chứa 10:00 AM và B1 chứa 1:30 PM, công thức sẽ như sau:

=B1 - A1

Kết quả sẽ là 0.145833. Trông có vẻ lạ lúc đầu, nhưng nhớ rằng Excel biểu diễn thời gian dưới dạng phần lẻ của ngày. Trong trường hợp này, 0.145833 tương đương 3 giờ 30 phút.

Để kết quả hữu ích hơn, bạn thường sẽ muốn chuyển nó sang định dạng dễ đọc.

Chuyển chênh lệch thời gian sang giờ, phút hoặc giây

Bạn có thể nhân kết quả phép trừ thời gian để biểu diễn chênh lệch theo giờ, phút hoặc giây. Đây là cách chuyển chênh lệch thời gian sang giờ:

=(B2 - A2) * 24

Đây là cách chuyển chênh lệch thời gian sang phút:

=(B2 - A2) * 1440

Đây là cách chuyển chênh lệch thời gian sang giây:

=(B2 - A2) * 86400

Các công thức này hoạt động vì trong một ngày có 24 giờ, 1.440 phút và 86.400 giây. Nếu chênh lệch thời gian là ba tiếng rưỡi, bạn sẽ được 3,5 từ công thức giờ, 210 từ công thức phút và 12.600 từ công thức giây.

Trích xuất đơn vị thời gian cụ thể

Nếu bạn chỉ muốn lấy một phần của thời gian, ví dụ chỉ phút hoặc giờ, bạn có thể dùng HOUR(). HOUR() trả về phần giờ của một thời gian.

=HOUR(B2 - A2)

MINUTE() trả về phần phút.

=MINUTE(B2 - A2)

SECOND() trả về phần giây.

=SECOND(B2 - A2)

Những hàm này hữu ích, nhưng chúng sẽ đặt lại sau 23 giờ, 59 phút hoặc 59 giây. Vì vậy nếu khoảng thời gian dài hơn một ngày, chúng sẽ không phản ánh tổng thời gian đã trôi qua. Trong các trường hợp đó, tốt hơn là nhân chênh lệch hoặc dùng định dạng tùy chỉnh, chúng ta sẽ đề cập sau.

Cộng giá trị thời gian vượt quá 24 giờ

Nếu bạn cần cộng số giờ vượt quá 24 giờ, dùng trực tiếp TIME() sẽ không cho kết quả như mong đợi. Thay vào đó, bạn có thể tính toán bằng phần lẻ của một ngày.

Đây là cách cộng số giờ vượt 24.

=A2 + (28/24)  ' Adds 28 hours

Đây là cách cộng số phút vượt 60.

=A2 + (90/1440)  ' Adds 90 minutes

Đây là cách cộng số giây vượt 60.

=A2 + (3600/86400)  ' Adds 3600 seconds

Excel lưu thời gian dưới dạng số thập phân, trong đó 1 ngày bằng 1, vì vậy chia cho 24, 1440 hoặc 86400 sẽ điều chỉnh giá trị để biểu diễn một phần của ngày.

Xử lý giá trị thời gian âm

Mặc định, nếu bạn lấy một thời gian nhỏ trừ một thời gian lớn hơn (ví dụ, 10:00 AM - 2:00 PM), Excel sẽ trả về lỗi hoặc hiển thị một chuỗi ###.

Để xử lý an toàn giá trị thời gian âm, hãy dùng hàm MOD(). Hàm này ngăn kết quả âm bằng cách “quấn” chúng về một thời gian hợp lệ.

Ví dụ, để trừ an toàn 3 giờ:

=MOD(A2 - TIME(3, 0, 0), 1)

Công thức này đảm bảo kết quả luôn là một giá trị thời gian dương, tránh lỗi hoặc vấn đề hiển thị không mong muốn.

Cách định dạng kết quả thời gian cho đúng

Các phép tính thời gian trong Excel thường cho ra các giá trị thập phân khó hiểu hoặc kết quả không như mong đợi, đặc biệt khi làm việc với khoảng thời gian trên 24 giờ hoặc giá trị âm. Định dạng đúng có thể biến các đầu ra thô thành định dạng dễ đọc, thân thiện với người dùng, dễ diễn giải và trình bày.

Dùng định dạng thời gian cơ bản

Nếu bạn muốn hiển thị chênh lệch thời gian như “3:30” thay vì số thập phân, hãy dùng hàm TEXT().

=TEXT(B2 - A2, "h:mm")

Hàm này định dạng kết quả thành giờ và phút. Hãy lưu ý đầu ra là văn bản, trông đẹp nhưng bạn sẽ không thể dùng tiếp cho tính toán trừ khi chuyển ngược lại.

Áp dụng định dạng thời gian tùy chỉnh

Các định dạng thời gian dựng sẵn của Excel xử lý tốt các giá trị tiêu chuẩn, nhưng định dạng tùy chỉnh cung cấp tính linh hoạt cao hơn để hiển thị khoảng thời gian, số giờ kéo dài, hoặc các thành phần thời gian cụ thể.

Để áp dụng định dạng thời gian tùy chỉnh, làm theo các bước sau:

1. Chọn ô chứa giá trị thời gian.
2. Nhấp chuột phải và chọn Format Cells.
3. Trong thẻ Number, chọn Custom.
4. Nhập mã định dạng mong muốn.

Một số định dạng thời gian tùy chỉnh thường dùng:

• [h]:mm: Hiển thị tổng số giờ và phút, kể cả vượt quá 24 giờ. Ví dụ: 36:45 (36 giờ 45 phút)

• d "days" h:mm:ss: Hiển thị ngày, giờ, phút và giây. Ví dụ: 1 days 12:30:00

• h:mm AM/PM: Hiển thị thời gian với ký hiệu AM/PM. Ví dụ: 10:30 PM

• mm:ss: Chỉ hiển thị phút và giây. Ví dụ: 45:15

Định dạng tùy chỉnh cho phép bạn trình bày dữ liệu thời gian trực quan hơn cho các mục đích cụ thể, như bảng chấm công hoặc nhật ký công việc.

Hiển thị chênh lệch thời gian thân thiện

Nếu bạn muốn hiển thị chênh lệch thời gian dưới dạng chuỗi dễ đọc, như “2 ngày, 4 giờ và 15 phút”, bạn có thể kết hợp nhiều hàm trong một công thức.

Đây là một ví dụ thực tế:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"

Trong công thức này, hàm INT() trích số ngày tròn, trong khi HOUR() và MINUTE() lấy phần giờ và phút còn lại.

Để tránh các giá trị 0 xuất hiện trong đầu ra, bạn có thể lồng các phép tính này trong các câu lệnh IF(). Ví dụ:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"

Công thức này chỉ hiển thị các đơn vị liên quan, giúp đầu ra gọn gàng và dễ đọc hơn.

Các hàm thời gian quan trọng trong Excel cần biết

Như bạn đã thấy, Excel có một bộ hàm thời gian mạnh mẽ. Để có cái nhìn đầy đủ hơn, dưới đây là tóm lược nhanh các hàm thời gian quan trọng nhất.

Dùng hàm TIME() để tạo giá trị thời gian

TIME() xây dựng một giá trị thời gian hợp lệ từ ba thành phần: giờ, phút và giây. Hàm này lý tưởng khi bạn cần tạo giá trị thời gian theo lập trình.

Đây là một ví dụ hay:

=TIME(14, 30, 0)  

Công thức trên sẽ trả về 2:30 PM nhờ cú pháp sau.

=TIME(hour, minute, second)

Chuyển văn bản thành thời gian với TIMEVALUE()

Nếu bạn có thời gian viết dưới dạng chuỗi văn bản (ví dụ, "2:45 PM"), hãy dùng TIMEVALUE() để chuyển nó thành giá trị thời gian chuẩn của Excel có thể dùng trong tính toán.

=TIMEVALUE("2:45 PM")

Ví dụ, hàm này sẽ trả về 0.614583 là một phần của ngày:

=TIMEVALUE("14:45")

Lấy ngày giờ hiện tại với NOW()

NOW() trả về ngày và giờ hiện tại dưới dạng một giá trị. Nó tự động cập nhật mỗi khi trang tính tính toán lại.

=NOW()

Ví dụ hay: nếu thời gian hiện tại là 3:15 PM, NOW() có thể trả về 44710.63542, trong đó 44710 là ngày và .63542 biểu thị thời gian.

Truy xuất ngày hiện tại với TODAY()

Nếu bạn chỉ cần ngày hiện tại không có thời gian, hãy dùng TODAY(). Hàm này đặc biệt hữu ích để tính hạn chót hoặc tuổi dữ liệu.

=TODAY()

Trích xuất thành phần thời gian với HOUR(), MINUTE(), và SECOND()

Các hàm này cho phép bạn tách riêng từng phần của giá trị thời gian, hữu ích cho báo cáo và logic điều kiện.

HOUR() trả về phần giờ của một giá trị thời gian (0-23).

=HOUR(A2)

MINUTE() trả về phần phút (0-59).

=MINUTE(A2)

SECOND() trả về phần giây (0-59).

=SECOND(A2)

Định dạng giá trị thời gian với TEXT()

TEXT() chuyển một giá trị thời gian sang định dạng cụ thể, rất phù hợp để hiển thị khoảng thời gian hoặc tạo chuỗi thời gian tùy chỉnh. Cú pháp:

=TEXT(A2, "h:mm:ss AM/PM")

Xử lý giá trị thời gian âm với TEXT()

Khi trừ các giá trị thời gian, MOD() đảm bảo kết quả luôn dương, ngăn lỗi do giá trị thời gian âm.

=MOD(A2 - B2, 1)

Tạo giá trị ngày với DATE()

DATE() cho phép bạn tạo một ngày từ các thành phần riêng: năm, tháng, ngày. Hữu ích để kết hợp ngày và giờ thành một giá trị duy nhất.

=DATE(year, month, day)

Đây là một ví dụ hay:

=DATE(2023, 12, 31) + TIME(14, 30, 0)

Trả về 12/31/2023 2:30 PM.

Tạo ngày làm việc với NETWORKDAYS()

NETWORKDAYS() tính số ngày làm việc giữa hai ngày, loại trừ cuối tuần và (tùy chọn) các ngày nghỉ lễ chỉ định. Hàm này lý tưởng để tính thời lượng dự án hoặc kỳ trả lương.

=NETWORKDAYS(start_date, end_date, [holidays])

Ví dụ:

=NETWORKDAYS(A2, B2, C2:C5)

Nếu A2 chứa 12/1/2023 và B2 chứa 12/31/2023, kết quả sẽ là số ngày làm việc trong tháng 12, loại trừ các ngày nghỉ trong vùng C2:C5.

Tạo ngày làm việc và cuối tuần với WORKDAY.INTL()

WORKDAY.INTL() là phiên bản nâng cao của WORKDAY() cho phép bạn tùy chỉnh ngày nào là cuối tuần.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Ví dụ:

=WORKDAY.INTL(A2, 10, "0110000")

Ở đây, 0110000 chỉ định Thứ Ba (0) và Thứ Tư (1) là cuối tuần. Công thức tính ngày sau 10 ngày làm việc kể từ A2, loại trừ Thứ Ba và Thứ Tư.

Kết luận

Xử lý thời gian trong Excel có thể cảm thấy phức tạp, nhưng với các hàm phù hợp, mọi thứ trở nên dễ quản lý hơn nhiều.

Nếu bạn muốn củng cố kỹ năng Excel hơn nữa, hãy xem Introduction to Excel để nắm vững nền tảng hoặc khóa Data Analysis in Excel để luyện tập với dữ liệu thực tế.

Với các ứng dụng nâng cao, lộ trình kỹ năng Excel Fundamentals cung cấp dự án hướng dẫn và bài tập giúp bạn thành thạo tính toán thời gian và hơn thế nữa.