Trong thế giới kỹ thuật số ngày nay, Microsoft Excel không chỉ là một công cụ bảng tính thông thường mà còn là trợ thủ đắc lực cho hàng triệu người dùng, từ sinh viên đến các chuyên gia. Tuy nhiên, ít ai hiểu rõ về cách Excel xử lý một trong những loại dữ liệu phổ biến nhất: ngày và giờ. Khi bạn chuyển định dạng một ô chứa ngày hoặc giờ sang định dạng “General”, những gì xuất hiện là một dãy số dường như ngẫu nhiên. Thực tế, đây là những “số serial” tuần tự, chứa đựng một cơ chế lưu trữ thông minh và mạnh mẽ, cho phép Excel thực hiện các phép tính phức tạp về thời gian một cách nhanh chóng và chính xác.
Việc nắm vững cách Excel tạo và sử dụng các số serial này không chỉ giúp bạn hiểu sâu hơn về phần mềm mà còn mở ra vô vàn khả năng trong việc tính toán, phân tích và quản lý dữ liệu liên quan đến thời gian. Từ việc xác định khoảng cách giữa hai ngày, cộng thêm số ngày vào một mốc thời gian, cho đến việc xử lý các giá trị ngày-giờ kết hợp, kiến thức về số serial sẽ là chìa khóa để bạn khai thác tối đa sức mạnh của Excel.
Cách Excel Lưu Trữ Ngày và Giờ Dưới Dạng Số Serial
Khi bạn nhập một ngày hoặc giờ vào Microsoft Excel, phần mềm sẽ tự động áp dụng định dạng phù hợp. Ví dụ, nếu bạn nhập “16/12/2024”, Excel sẽ định dạng ô đó là “Date” (Ngày). Tương tự, nếu bạn nhập “10:00 AM”, ô sẽ được định dạng là “Time” (Thời gian).
Bạn có thể nhập ngày theo nhiều cách khác nhau như 2024-12-16, 16-Dec-2024, 2024/12/16, hoặc Dec 16, 2024. Tuy nhiên, nếu bạn dùng dấu chấm (.) để phân tách năm, tháng, ngày (ví dụ: 2024.12.16), Excel sẽ coi đó là văn bản và không thể nhận diện là ngày. Đối với giờ, bạn có thể nhập giờ và phút cách nhau bằng dấu hai chấm (10:00), hoặc thêm giây (10:00:00). Để chỉ rõ buổi sáng (AM) hoặc buổi chiều (PM), bạn chỉ cần thêm một khoảng trắng và gõ AM hoặc PM sau thời gian.
Định dạng ngày được áp dụng cho ô F1 trong Microsoft Excel
Định dạng thời gian được áp dụng cho ô F2 trong Microsoft Excel
Tuy nhiên, đằng sau lớp vỏ định dạng dễ nhìn, các ô chứa ngày và giờ thực chất chứa đựng những số serial tuần tự. Bạn có thể xem những số này bằng cách chọn ô chứa ngày/giờ, sau đó vào tab “Home”, tìm nhóm “Number” và chọn định dạng “General”.
Bạn sẽ nhận thấy rằng số serial cho ngày là các số nguyên, trong khi số serial cho giờ là các số thập phân. Điều này là do Excel coi thời gian là một phần của một ngày. Chính vì vậy, bạn hoàn toàn có thể kết hợp cả ngày và giờ trong cùng một ô. Ví dụ, nếu bạn nhập “2024-12-16 10:00:00” vào một ô, số serial sẽ là sự kết hợp của phần nguyên (ngày) và phần thập phân (giờ).
Một bảng Excel chứa số serial ngày-giờ trong ô F2
Cơ Chế Tạo Số Serial Ngày và Giờ Trong Excel
Các số serial cho ngày trong tất cả các phiên bản Excel hiện đại (trên Windows) đều bắt đầu từ số 1 cho ngày 1 tháng 1 năm 1900, và tăng lên 1 đơn vị cho mỗi ngày tiếp theo.
Ví dụ, ngày 31 tháng 1 năm 1900 có số serial là 31, vì đó là 31 ngày sau điểm khởi đầu. Một điều cần lưu ý là hệ thống này của Excel tính cả ngày 29 tháng 2 năm 1900 là một ngày nhuận, mặc dù thực tế năm 1900 không phải là năm nhuận. Điều này có nghĩa là mỗi số serial sau ngày đó sẽ lệch đi một đơn vị so với thực tế, nhưng điều này chỉ quan trọng nếu bạn làm việc với các ngày trong quý đầu tiên của năm 1900. Ngoài ra, Excel không nhận diện được các ngày trước ngày 1 tháng 1 năm 1900, do đó bạn không thể sử dụng chúng trong các phép tính.
Đối với thời gian, 12 AM (nửa đêm) có số serial là 0. Giá trị này tăng lên 1/1440 cho mỗi phút tiếp theo, bởi vì có 1440 phút trong một ngày (24 giờ * 60 phút). Ví dụ, 1 AM có số serial là 0.0416666666666667, tương đương với 60/1440.
Lưu ý: Các phiên bản Excel đời đầu trên Mac có thể bắt đầu hệ thống ngày từ ngày 1 tháng 1 năm 1904. Để tránh các vấn đề tương thích khi sao chép ngày giữa các phiên bản Excel, bạn có thể thay đổi hệ thống ngày trong tùy chọn tính toán của Excel (File > Options > Advanced trên Windows, hoặc Excel Menu > Preferences > Formulas And Lists trên Mac).
Hướng Dẫn Sử Dụng Số Serial Ngày Giờ Trong Các Phép Tính Excel
Giờ đây, khi đã nắm rõ cách số serial hoạt động cho ngày và giờ trong Excel, bạn có thể áp dụng kiến thức này vào các phép tính thực tế.
Ví dụ 1: Tìm Sự Khác Biệt Giữa Hai Ngày
Giả sử bạn muốn tính số ngày giữa ngày 1 tháng 1 năm 2025 và ngày 31 tháng 3 năm 2025.
Bảng Excel minh họa phép tính tìm sự khác biệt giữa hai ngày 01/01/2025 và 31/03/2025, trước khi áp dụng công thức
Vì số serial của ngày tăng lên một mỗi ngày, bạn chỉ cần lấy ngày kết thúc trừ đi ngày bắt đầu. Đảm bảo ô chứa kết quả được định dạng là “Number” (Số), không phải “Date” (Ngày).
=C2-B2
Kết quả tính toán 90 ngày giữa 01/01/2025 và 31/03/2025 trong Excel bằng cách trừ số serial ngày
Khi tính toán sự khác biệt giữa hai ngày, kết quả không bao gồm ngày bắt đầu.
Ví dụ 2: Cộng Thêm Số Ngày Vào Một Ngày Cụ Thể
Mục tiêu của bạn là cộng thêm 60 ngày vào ngày 1 tháng 1 năm 2025.
Bảng Excel hiển thị ngày bắt đầu 01/01/2025 và số ngày cần cộng thêm là 60, chuẩn bị tính ngày kết quả
Để thực hiện, bạn chỉ cần cộng ô chứa số ngày cần thêm vào ô chứa ngày bắt đầu. Đảm bảo ô chứa kết quả được định dạng là “Date”.
=B2+D2
Kết quả sau khi cộng 60 ngày vào 01/01/2025 trong Excel, hiển thị ngày 02/03/2025
Ví dụ 3: Trừ Bớt Số Ngày Khỏi Một Ngày Cụ Thể
Tương tự như phép cộng, bạn có thể trừ bớt số ngày khỏi một ngày cụ thể. Ví dụ, bạn muốn biết ngày 35 ngày trước ngày 1 tháng 7 năm 2025 là ngày nào.
Bảng Excel minh họa phép tính tìm ngày bắt đầu bằng cách trừ 35 ngày từ ngày kết thúc 01/07/2025
Trong ô B2, bạn nhập công thức:
=C2-D2và định dạng ô đó là “Date”.
Kết quả ngày bắt đầu là 27/05/2025 sau khi trừ 35 ngày từ 01/07/2025 trong Excel
Ví dụ 4: Tính Khoảng Thời Gian Giữa Hai Giờ Trong Cùng Một Ngày
Điều đầu tiên cần lưu ý khi tính sự khác biệt giữa hai giờ là chúng phải nằm trong cùng một ngày. Điều này là do số serial thời gian chạy từ đầu ngày đến cuối ngày, sau đó sẽ khởi động lại vào ngày hôm sau. Nếu bạn cố gắng tìm sự khác biệt giữa 7 PM của một ngày và 7 AM của ngày hôm sau, số serial của 7 PM sẽ lớn hơn 7 AM, và Excel không thể hiển thị thời gian âm. Trong trường hợp này, đối số đầu tiên trong phép tính phải là thời gian muộn hơn đối số thứ hai. Nếu cần tính toán qua đêm, bạn sẽ cần chuyển đổi dữ liệu thành định dạng ngày-giờ (xem Ví dụ 7).
Bây giờ, giả sử bạn muốn tính khoảng thời gian giữa 7:12:45 AM và 10:14:57 AM trong cùng một ngày.
Bảng Excel cho phép tính toán khoảng thời gian giữa 7:12:45 AM và 10:14:57 AM trong cùng một ngày
Trong ô D2, bạn nhập công thức:
=C2-B2
Hiển thị kết quả chênh lệch thời gian 03:02:12 AM giữa hai mốc thời gian đã cho, trước khi định dạng lại
Vì ô D2 được định dạng là “Time”, nó sẽ hiển thị “AM” từ các ô B2 và C2. Để loại bỏ các chữ cái này, bạn chọn ô D2 và nhấn Ctrl+1 để mở hộp thoại “Format Cells”. Trong tab “Number”, chọn “Time” và chọn định dạng thời gian chỉ hiển thị giờ, phút và giây. Sau đó nhấn “OK”.
Hộp thoại "Format Cells" trong Excel, chọn định dạng thời gian chỉ hiển thị giờ, phút, giây để loại bỏ AM/PM
Bây giờ, bạn sẽ có một biểu diễn rõ ràng hơn về sự khác biệt thời gian giữa ô B2 và C2, và số serial tương ứng sẽ xác nhận rằng phép tính là chính xác.
Chênh lệch thời gian đã được định dạng lại thành 03:02:12 PM, loại bỏ AM/PM để hiển thị rõ ràng hơn
Ví dụ 5: Cộng Thêm Giờ và Phút Vào Một Thời Điểm
Trong ví dụ này, bạn muốn tìm thời gian sau khi cộng thêm 12.5 giờ vào 7 PM.
Bảng Excel để tính thời gian sau khi cộng 12.5 giờ vào 7:00:00 PM
Bạn sẽ thấy rằng sự khác biệt thời gian trong ô D2 được định dạng là “Time”. Tuy nhiên, bạn biết rằng nó sẽ hoạt động như mong đợi, vì số serial đại diện cho hơn nửa ngày, đó là những gì bạn muốn thêm vào thời gian bắt đầu. Nếu muốn, bạn có thể định dạng lại ô D2 trong hộp thoại “Format Cells” để chỉ hiển thị giờ, phút và giây.
Bây giờ, trong ô C2, bạn nhập công thức:
=B2+D2
Kết quả thời gian là 07:30:00 AM sau khi cộng 12.5 giờ vào 7:00:00 PM, hiển thị tự động chuyển sang ngày tiếp theo
Số serial cho thời gian kết thúc lớn hơn 1, vì việc cộng 12.5 giờ vào 7 PM sẽ đưa bạn sang ngày hôm sau. Tuy nhiên, vì ô C2 được định dạng chỉ hiển thị thời gian, chỉ phần sau dấu thập phân được trả về.
Ví dụ 6: Trừ Bớt Giờ và Phút Khỏi Một Thời Điểm
Tương tự như Ví dụ 4, điều quan trọng cần lưu ý là việc trừ giờ và phút khỏi một thời gian chỉ hoạt động nếu kết quả rơi vào cùng ngày với thời gian trong phép tính. Nếu không, bạn sẽ thấy một chuỗi các ký hiệu thăng (#), vì Excel không thể hiển thị thời gian âm. Trong trường hợp này, bạn sẽ cần nhập các giá trị ngày-giờ thay vì chỉ giá trị thời gian, như trong Ví dụ 7.
Hãy tưởng tượng bạn muốn tính thời gian khi bạn trừ năm giờ, 30 phút và 20 giây từ 9 AM.
Bảng Excel thiết lập phép trừ 5 giờ 30 phút 20 giây từ 9:00:00 AM
Để thực hiện, trong ô B2, bạn nhập công thức:
=C2-D2
Kết quả thời gian là 03:29:40 AM sau khi trừ 5 giờ 30 phút 20 giây từ 9:00:00 AM
Ví dụ 7: Thực Hiện Tính Toán Với Giá Trị Ngày-Giờ Kết Hợp
Trong ví dụ cuối cùng, giả sử mục tiêu của bạn là tính toán sự khác biệt thời gian giữa 7 AM ngày 12 tháng 12 năm 2023 và 9 AM ngày 31 tháng 1 năm 2024.
Bảng Excel để tính toán sự khác biệt giữa hai giá trị ngày-giờ: 12/12/2023 7:00:00 AM và 31/01/2024 9:00:00 AM
Đầu tiên, áp dụng định dạng “General” hoặc “Number” cho ô D2. Sau đó, trong cùng ô đó, nhập công thức:
=C2-B2
Kết quả số serial chênh lệch 50.08333333333333 trong cột D, đại diện cho sự khác biệt giữa hai ngày-giờ
Excel có thể xử lý phép tính này, vì số serial cho giá trị trong ô C2 lớn hơn số serial cho giá trị trong ô B2.
Để làm cho thông tin này rõ ràng hơn, bạn có thể tách kết quả thành ngày và giờ. Đầu tiên, trong ô E2, nhập công thức:
=INT(D2)để làm tròn số thập phân xuống số nguyên gần nhất. Điều này cho bạn số ngày nguyên vẹn.
Sử dụng hàm INT(D2) để trích xuất 50 ngày nguyên vẹn từ số serial chênh lệch ngày-giờ trong Excel
Cuối cùng, trong ô F2, nhập công thức:
=SUM(MOD(D2,1)*24)để trích xuất phần thập phân và chuyển nó thành số giờ.
Hàm MOD(D2,1)*24 tính toán phần giờ lẻ là 2.0 (tức 2 giờ) từ số serial chênh lệch ngày-giờ trong Excel
Kết Luận
Việc hiểu rõ cách Excel lưu trữ và xử lý ngày giờ dưới dạng số serial không chỉ là một kiến thức nền tảng quan trọng mà còn là một công cụ mạnh mẽ giúp bạn thực hiện các phép tính phức tạp về thời gian một cách hiệu quả. Từ việc tính toán khoảng thời gian, cộng trừ ngày giờ, cho đến xử lý các giá trị kết hợp, khả năng ứng dụng của số serial là vô cùng rộng lớn. Nó giải thích tại sao Excel có thể thực hiện những phép tính này và cách bạn có thể khắc phục các lỗi thường gặp, đặc biệt là liên quan đến thời gian âm.
Ngoài việc sử dụng trực tiếp các số serial như đã hướng dẫn, Microsoft Excel còn cung cấp nhiều hàm ngày và giờ chuyên dụng khác như DATEDIF, NETWORKDAYS, EDATE, EOMONTH, v.v., giúp bạn đơn giản hóa các tác vụ quản lý thời gian phức tạp hơn. Nắm vững cả hai cách tiếp cận này sẽ giúp bạn trở thành một “cao thủ” Excel thực thụ, quản lý dữ liệu thời gian một cách chuyên nghiệp và chính xác. Đừng ngần ngại khám phá và áp dụng những kiến thức này vào công việc hàng ngày của mình!
