Trong Microsoft Excel, có bao giờ bạn tự hỏi làm thế nào phần mềm này có thể thực hiện các phép tính phức tạp với ngày và giờ một cách chính xác đến vậy không? Khi bạn thay đổi định dạng của một ô chứa ngày hoặc giờ sang định dạng “General” (Chung), bạn sẽ thấy những con số tưởng chừng như ngẫu nhiên xuất hiện. Trên thực tế, những con số này không hề ngẫu nhiên mà là các “số sê-ri” (serial numbers) tuần tự, đóng vai trò cốt lõi trong cách Excel xử lý dữ liệu thời gian.
Việc hiểu rõ cơ chế lưu trữ ngày và giờ dưới dạng số sê-ri trong Excel không chỉ giúp bạn giải mã những con số bí ẩn này mà còn mở ra cánh cửa để thực hiện các phép tính ngày tháng, thời gian phức tạp một cách nhanh chóng và hiệu quả hơn. Bài viết này sẽ đi sâu vào cách Excel tạo ra và sử dụng các số sê-ri này, đồng thời cung cấp các ví dụ thực tế để bạn có thể áp dụng kiến thức này vào công việc hàng ngày, từ đó nâng cao kỹ năng sử dụng Excel của mình.
Ngày và Giờ Được Lưu Trữ Dưới Dạng Số Sê-ri Trong Excel
Microsoft Excel cho phép bạn định dạng một ô dưới dạng ngày hoặc giờ. Ví dụ, trong một bảng tính, ô F1 có thể được định dạng là ngày.
Excel nhận diện các ngày được nhập theo nhiều cách khác nhau. Bạn có thể nhập ngày với dấu gạch nối (ví dụ: 2024-12-16 hoặc 16-Thg12-2024), dấu gạch chéo (ví dụ: 2024/12/16 hoặc 24/12/16), hoặc dấu cách và dấu phẩy (ví dụ: Thg12 16, 2024). Tuy nhiên, nếu bạn sử dụng dấu chấm để phân tách năm, tháng và ngày, Excel sẽ nhận diện đây là văn bản chứ không phải là ngày.
Tương tự, giá trị trong ô F2 có thể được định dạng là giờ.
Ô F2 trong Excel hiển thị định dạng thời gian được áp dụng
Bạn có thể nhập thời gian trong Excel dưới dạng giờ và phút được phân tách bằng dấu hai chấm (ví dụ: 10:00), và bạn cũng có thể bao gồm cả giây (ví dụ: 10:00:00) nếu muốn. Để bao gồm AM hoặc PM, chỉ cần thêm một dấu cách và gõ AM hoặc PM sau khi nhập thời gian. Đối với các thời điểm chỉ có giờ, bạn có thể nhập giờ theo sau là AM hoặc PM (ví dụ: 10 AM).
Định dạng mặc định của ngày, giờ, tiền tệ và các yếu tố khác trong Excel phụ thuộc vào cài đặt hệ thống của bạn. Bạn có thể thay đổi các cài đặt này trong Windows 11 bằng cách thay đổi khu vực của mình.
Tuy nhiên, ẩn dưới bề mặt, các ô chứa ngày và giờ thực chất chứa các số sê-ri tuần tự. Bạn có thể nhìn thấy chúng bằng cách chọn các ô đã định dạng ngày và giờ, sau đó nhấp vào “General” (Chung) trong nhóm Number (Số) trên tab Home (Trang chủ) của thanh Ribbon.
Hãy chú ý cách các số sê-ri cho ngày là các số nguyên, trong khi số sê-ri cho giờ là các số thập phân. Điều này là do thời gian được coi là một phần của một ngày, và bạn có thể có 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ố sê-ri sẽ đại diện cho cả phần nguyên của ngày và phần thập phân của giờ.
Bảng Excel với ô F2 chứa số sê-ri cho giá trị ngày và giờ kết hợp
Số Sê-ri Ngày và Giờ Được Tạo Ra Như Thế Nào Trong Excel?
Các số sê-ri cho ngày trong tất cả các phiên bản hiện đại của Microsoft Excel bắt đầu từ số 1 cho ngày 1 tháng 1 năm 1900, và tăng thêm một đơn vị cho mỗi ngày tiếp theo.
Ví dụ, số sê-ri cho ngày 31 tháng 1 năm 1900 là 31, vì đó là 31 ngày sau điểm khởi đầu. Tuy nhiên, hệ thống này bao gồm cả một số sê-ri cho ngày 29 tháng 2 năm 1900, mặc dù năm 1900 không phải là năm nhuận. Vì vậy, mỗi số sê-ri sau ngày này sẽ lệch một đơn vị so với những gì bạn mong đợi. Tuy nhiên, điều này chỉ quan trọng nếu bạn đang xử lý 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 ngày trước ngày 1 tháng 1 năm 1900. Do đó, các ngày trước đó không có số sê-ri và bạn không thể sử dụng chúng trong các phép tính.
Lưu ý: Các số sê-ri ngày trong các phiên bản đầu của Excel dành cho Mac bắt đầu 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 bằng cách nhấp vào File > Options > Advanced trên PC chạy Windows, hoặc Excel Menu > Preferences > Formulas And Lists trên máy Mac.
Đối với thời gian, 12 AM (0 giờ sáng) có số sê-ri là 0, và con số này tăng thêm 1/1440 với mỗi phút tiếp theo, vì có 1440 phút trong một ngày. Ví dụ, số sê-ri cho 1 AM (1 giờ sáng) là 0.0416666666666667, tương đương với 60/1440.
Ứng Dụng Số Sê-ri Ngày Giờ Trong Các Phép Tính Excel
Giờ đây bạn đã hiểu cách các số sê-ri hoạt động đối với ngày và giờ trong Excel, bạn đã sẵn sàng tận dụng kiến thức này trong các phép tính.
1. Tìm Sự Chênh Lệch Giữa Hai Ngày
Trong ví dụ này, giả sử bạn muốn tính số ngày giữa ngày 1 tháng 1 và ngày 31 tháng 3 năm 2025.
Vì số sê-ri ngày tăng thêm một mỗi ngày, trong ô D2, bạn chỉ cần lấy ngày kết thúc trừ đi ngày bắt đầu. Hãy đảm bảo rằng ô chứa kết quả được định dạng là một số, không phải là ngày:
=C2-B2
Kết quả phép trừ ngày trong Excel hiển thị số ngày chênh lệch
Khi tính toán sự khác biệt giữa hai ngày, kết quả sẽ không bao gồm ngày bắt đầu.
2. Cộng Thêm Số Ngày Vào Một Ngày Cụ Thể
Ở đây, mục tiêu của bạn là thêm 60 ngày vào ngày 1 tháng 1 năm 2025.
Bảng tính Excel minh họa cộng thêm số ngày vào một ngày đã cho
Để làm điều này, trong ô C2, hãy cộng ô chứa số ngày chênh lệch vào ngày bắt đầu, đảm bảo rằng ô này được định dạng là ngày.
=B2+D23. Trừ Bớt Số Ngày Từ Một Ngày Cụ Thể
Cũng như việc cộng thêm ngày vào một ngày, bạn cũng có thể sử dụng nguyên tắc tương tự để trừ đi số ngày từ một ngày. Ví dụ, bạn có thể muốn biết ngày nào là 35 ngày trước ngày 1 tháng 7 năm 2025.
Bảng Excel hiển thị phép trừ số ngày từ một ngày cụ thể
Trong ô B2, gõ:
=C2-D2và định dạng ô đó là ngày.
4. Tìm Sự Chênh Lệch Giữa Hai Giờ Trong Cùng Một Ngày
Điều đầu tiên cần lưu ý khi tìm sự khác biệt giữa hai thời điểm là chúng phải nằm trong cùng một ngày. Điều này là do các số sê-ri thời gian chạy từ đầu ngày đến cuối ngày, trước khi khởi động lại vào ngày hôm sau.
Ví dụ, nếu bạn cố gắng tìm sự khác biệt giữa 7 PM (7 giờ tối) của một ngày và 7 AM (7 giờ sáng) của ngày hôm sau, số sê-ri cho 7 PM lớn hơn số sê-ri cho 7 AM, và Excel không thể hiển thị thời gian âm. Nói cách khác, đối số đầu tiên trong phép tính phải là thời điểm muộn hơn đối số thứ hai.
Để khắc phục điều này, bạn cần chuyển đổi tất cả dữ liệu sang định dạng ngày-giờ. Xem ví dụ 7 để biết thêm chi tiết.
Thay vào đó, giả sử bạn muốn tính toán sự khác biệt thời gian giữa 7:12:45 AM và 10:14:57 AM trong cùng một ngày.
Tính toán chênh lệch giữa hai thời điểm trong cùng một ngày trong Excel
Để làm điều này, trong ô D2, gõ:
=C2-B2Vì ô D2 được định dạng là thời gian, nó có một số sê-ri. Trong ví dụ trên, điều này có nghĩa là nó đã lấy định dạng “AM” từ các ô B2 và C2.
Để loại bỏ những chữ cái này, hãy chọn ô D2 và nhấn Ctrl+1 để mở hộp thoại Format Cells (Định dạng ô). Sau đó, trong tab Number (Số), nhấp vào “Time” (Thời gian) và chọn định dạng thời gian chỉ hiển thị giờ, phút và giây. Khi hoàn tất, nhấp vào “OK”.
Hộp thoại Format Cells trong Excel chọn định dạng thời gian chỉ hiển thị số
Bây giờ, bạn có một biểu diễn rõ ràng hơn về sự khác biệt thời gian giữa các ô B2 và C2, và số sê-ri tương ứng xác nhận rằng phép tính là chính xác.
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 ra thời điểm sau khi thêm 12.5 giờ vào 7 PM (7 giờ tối).
Bảng Excel minh họa cộng giờ và phút vào một thời điểm
Bạn sẽ nhận thấy rằng sự khác biệt thời gian trong ô D2 được định dạng là thời gian. Tuy nhiên, bạn biết rằng nó sẽ hoạt động như mong đợi, vì số sê-ri đạ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 bạn muốn làm gọn gàng hơ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. Xem ví dụ 4 để biết thêm chi tiết về cách thực hiện điều này.
Bây giờ, trong ô C2, gõ:
=B2+D2Số sê-ri cho thời gian kết thúc lớn hơn 1, vì việc thêm 12.5 giờ vào 7 PM sẽ đưa bạn sang ngày tiếp theo. Tuy nhiên, vì ô C2 được định dạng chỉ hiển thị thời gian, nên chỉ phần sau dấu thập phân được trả về.
6. Trừ Bớt Giờ và Phút Từ Một Thời Điểm
Cũng như ví dụ 4, điều quan trọng cần lưu ý là việc trừ giờ và phút từ một thời điểm sẽ chỉ hoạt động nếu kết quả rơi vào cùng ngày với thời điểm trong phép tính. Nếu không, bạn sẽ thấy một loạt 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 toán thời điểm khi bạn trừ đi năm giờ, 30 phút và 20 giây từ 9 AM (9 giờ sáng).
Bảng tính Excel minh họa trừ giờ và phút từ một thời điểm
Để làm điều này, trong ô B2, gõ:
=C2-D27. Thực Hiện Tính Toán Với Giá Trị Ngày Kèm Giờ
Trong một 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 với giá trị ngày kèm giờ để tính toán chênh lệch tổng thể
Đầu tiên, áp dụng định dạng General (Chung) hoặc Number (Số) cho ô D2. Sau đó, trong cùng ô đó, gõ:
=C2-B2Excel có thể xử lý phép tính này, vì số sê-ri cho giá trị trong ô C2 lớn hơn số sê-ri cho giá trị trong ô B2.
Bây giờ, để thông tin này rõ ràng hơn, bạn có thể chia kết quả thành ngày và giờ. Đầu tiên, trong ô E2, gõ:
=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 biết số ngày nguyên.
Sử dụng hàm INT trong Excel để trích xuất số ngày nguyên từ kết quả chênh lệch ngày giờ
Cuối cùng, trong ô F2, gõ:
=SUM(MOD(D2,1)*24)để trích xuất phần thập phân và chuyển nó thành số giờ.
Ngoài việc sử dụng số sê-ri ngày và giờ để thực hiện các loại phép tính này, Microsoft Excel cũng cung cấp nhiều hàm ngày và giờ khác – bao gồm DATEDIF và NETWORKDAYS – có thể giúp bạn đạt được các kết quả ngày và giờ mong muốn.
Kết Luận
Việc hiểu sâu sắc cách Excel lưu trữ ngày và giờ dưới dạng số sê-ri là một kiến thức nền tảng vô cùng giá trị. Nó không chỉ giúp bạn giải thích những con số “bí ẩn” khi chuyển đổi định dạng mà còn là chìa khóa để thực hiện các phép tính ngày tháng và thời gian một cách linh hoạt, chính xác và hiệu quả. Từ việc tính toán chênh lệch giữa hai ngày, cộng trừ thời gian, cho đến xử lý các giá trị ngày kèm giờ phức tạp, việc nắm vững cơ chế này sẽ giúp bạn làm chủ Excel một cách tự tin hơn.
Hy vọng với những giải thích chi tiết và ví dụ minh họa cụ thể trong bài viết, bạn đã có thể áp dụng ngay những kiến thức này vào công việc của mình. Hãy nhớ rằng, Excel là một công cụ mạnh mẽ, và việc khám phá các hàm cũng như cơ chế ẩn đằng sau nó sẽ luôn mở ra những khả năng mới để tối ưu hóa quy trình làm việc của bạn. Hãy tiếp tục tìm hiểu và thực hành để trở thành một chuyên gia Excel thực thụ!
