Ngày tháng thường là một phần quan trọng trong việc phân tích dữ liệu. Bạn thường đặt câu hỏi như: sản phẩm được mua khi nào, một nhiệm vụ trong một dự án sẽ mất bao lâu hoặc doanh thu trung bình của một quý tài chính là bao nhiêu? Nhập chính xác ngày tháng là điều cần thiết để đảm bảo kết quả chính xác. Nhưng định dạng ngày tháng để dễ hiểu cũng quan trọng không kém để đảm bảo diễn giải đúng kết quả đó.
Quan trọng
Vì các quy tắc chi phối cách thức mọi chương trình tính toán diễn giải ngày rất phức tạp, cho nên bạn nên cụ thể nhất có thể về ngày tháng mỗi khi bạn nhập ngày. Việc này sẽ tạo ra mức độ chính xác cao nhất trong các tính toán ngày của bạn.
Tìm hiểu về tính toán ngày và định dạng
Excel lưu trữ ngày tháng ở dạng số liên tiếp được gọi là giá trị sê-ri. Ví dụ: trong Excel for Windows, ngày 01 Tháng 1 năm 1900 là số sê-ri 1 và ngày 01 Tháng 1 năm 2008 là số sê-ri 39448 bởi nó là 39.448 ngày sau ngày 01 Tháng 1 năm 1900.
Excel lưu trữ thời gian dưới dạng phân số thập phân vì thời gian được coi là một phần của một ngày. Số thập phân là một giá trị từ 0 (không) đến 0,999999999, thể hiện các khoảng thời gian từ 0:00:00 (12:00:00 SA) đến 23:59:59 (11:59:59 CH).
Vì ngày và giờ là các giá trị nên bạn có thể cộng, trừ và đưa chúng vào các phép tính khác. Bạn có thể xem ngày tháng dưới dạng giá trị sê-ri và thời gian dưới dạng phân số thập phân bằng cách thay đổi định dạng của ô có chứa ngày hoặc giờ thành định dạng Chung.
Tìm hiểu về hai hệ thống ngày
Cả Excel cho Mac và Excel cho Windows đều hỗ trợ hệ thống ngày 1900 và 1904. Hệ thống ngày mặc định cho Excel dành cho Windows là 1900; và hệ thống ngày mặc định cho Excel for Mac là 1904.
Ban đầu, Excel cho Windows dựa trên hệ thống ngày 1900, vì nó cho phép tương thích tốt hơn với các chương trình bảng tính khác được thiết kế để chạy trong MS-DOS và Microsoft Windows, và do đó nó trở thành hệ thống ngày mặc định. Ban đầu, Excel cho Mac dựa trên hệ thống ngày 1904, vì nó cho phép tương thích tốt hơn với các máy tính Macintosh đời đầu không hỗ trợ ngày trước ngày 2 tháng 1 năm 1904, và do đó nó đã trở thành hệ thống ngày mặc định.
Bảng sau đây hiển thị ngày đầu tiên và ngày cuối cùng cho mỗi hệ thống ngày và giá trị sê-ri liên kết với mỗi ngày.
| Hệ ngày | Ngày đầu tiên | Ngày cuối cùng |
|---|---|---|
| 1900 | 1 Tháng 1, 1900 (giá trị sê-ri 1) |
31 Tháng Mười Hai năm 9999 (giá trị sê-ri 2958465) |
| 1904 | 2 tháng 1 năm 1904 (giá trị sê-ri 1) |
31 Tháng Mười Hai năm 9999 (giá trị sê-ri 2957003) |
Vì hai hệ thống ngày sử dụng ngày bắt đầu khác nhau, nên cùng một ngày được thể hiện bởi các giá trị sê-ri khác nhau trong mỗi hệ thống ngày tháng. Ví dụ: ngày 5 tháng 7 năm 2007 có thể có hai giá trị sê-ri khác nhau, tùy theo hệ thống ngày được sử dụng.
| Hệ ngày | Giá trị sê-ri ngày 5 tháng 7 năm 2007 |
|---|---|
| 1900 | 37806 |
| 1904 | 39268 |
Sự khác biệt giữa hai hệ thống ngày là 1.462 ngày; Nghĩa là, giá trị sê-ri của một ngày trong hệ thống ngày tháng năm 1900 luôn lớn hơn giá trị sê-ri của cùng ngày đó trong hệ thống ngày tháng năm 1904 1.462 ngày. Ngược lại, giá trị sê-ri của một ngày trong hệ thống ngày tháng năm 1904 luôn nhỏ hơn 1.462 ngày so với giá trị sê-ri của cùng ngày trong hệ thống ngày tháng năm 1900. 1.462 ngày bằng bốn năm và một ngày (bao gồm một ngày nhuận).
Thay đổi cách diễn giải năm có hai chữ số
Quan trọng
Để đảm bảo rằng giá trị năm được hiểu như bạn dự định, hãy nhập giá trị năm dưới dạng bốn chữ số (ví dụ: 2001, không phải 01). Bằng cách nhập năm có bốn chữ số, Excel sẽ không diễn giải thế kỷ cho bạn.
Nếu bạn nhập ngày tháng có năm gồm hai chữ số vào ô được định dạng văn bản hoặc nhập vào dạng đối số văn bản trong hàm, chẳng hạn như =YEAR("1/1/31"), Excel sẽ diễn giải năm như sau:
- 00 đến 29 được hiểu là các năm từ 2000 đến 2029. Ví dụ: nếu bạn nhập ngày 28/05/19, Excel giả định ngày là 28/05/2019.
- 30 đến 99 được hiểu là những năm từ 1930 đến 1999. Ví dụ: nếu bạn nhập ngày 28/05/98, Excel giả định ngày này là 28/05/1998.
Trong Microsoft Windows, bạn có thể thay đổi cách diễn giải năm có hai chữ số cho tất cả các chương trình Windows mà bạn đã cài đặt.
Windows 10
- Trong hộp tìm kiếm trên thanh tác vụ, nhập panel điều khiển, rồi chọn Panel Điều khiển.
- Bên dưới Đồng hồ, Ngôn ngữ và Khu vực, hãy bấm Thay đổi định dạng ngày, giờ hoặc số
- Bấm Tùy chọn Khu vực và Ngôn ngữ.
- Trong hộp thoại Khu vực , bấm vào Cài đặt bổ sung.
- Bấm vào tab Ngày .
- Trong hộp Khi nhập năm có hai chữ số, hãy hiểu là năm giữa các năm, thay đổi giới hạn trên cho thế kỷ.
Khi bạn thay đổi năm giới hạn trên, năm giới hạn dưới sẽ tự động thay đổi. - Bấm vào OK.
Windows 8
- Vuốt từ cạnh phải màn hình vào, nhấn Tìm kiếm (hoặc nếu bạn đang sử dụng chuột, hãy trỏ đến góc trên bên phải màn hình, di chuyển con trỏ chuột xuống dưới, rồi bấm Tìm kiếm), nhập Panel điều khiển vào hộp tìm kiếm, rồi nhấn hoặc bấm vào Panel điều khiển.
- Bên dưới Đồng hồ, Ngôn ngữ và Khu vực, hãy bấm Thay đổi định dạng ngày, giờ hoặc số.
- Trong hộp thoại Khu vực , bấm vào Cài đặt bổ sung.
- Bấm vào tab Ngày .
- Trong hộp Khi nhập năm có hai chữ số, hãy hiểu là năm giữa các năm, thay đổi giới hạn trên cho thế kỷ.
Khi bạn thay đổi năm giới hạn trên, năm giới hạn dưới sẽ tự động thay đổi. - Bấm vào OK.
Windows 7
- Bấm nút Bắt đầu rồi bấm Ngăn Điều khiển.
- Bấm vào Khu vực và Ngôn ngữ.
- Trong hộp thoại Khu vực , bấm vào Cài đặt bổ sung.
- Bấm vào tab Ngày .
- Trong hộp Khi nhập năm có hai chữ số, hãy hiểu là năm giữa các năm, thay đổi giới hạn trên cho thế kỷ.
Khi bạn thay đổi năm giới hạn trên, năm giới hạn dưới sẽ tự động thay đổi. - Bấm vào OK.
Thay đổi định dạng ngày tháng mặc định để hiển thị các năm có bốn chữ số
Theo mặc định, khi bạn nhập ngày trong một sổ làm việc, ngày tháng sẽ được định dạng để hiển thị các năm có hai chữ số. Khi bạn thay đổi định dạng ngày tháng mặc định thành một định dạng khác bằng cách sử dụng thủ tục này, hiển thị ngày tháng đã được nhập trước đó trong sổ làm việc của bạn sẽ thay đổi thành định dạng mới miễn là ngày tháng chưa được định dạng bằng cách sử dụng hộp thoại Định dạng Ô (Trên tab Trang đầu , trong nhóm Số , bấm vào Công cụ Khởi động Hộp thoại).
Windows 10
- Trong hộp tìm kiếm trên thanh tác vụ, nhập panel điều khiển, rồi chọn Panel Điều khiển.
- Bên dưới Đồng hồ, Ngôn ngữ và Khu vực, hãy bấm Thay đổi định dạng ngày, giờ hoặc số
- Bấm Tùy chọn Khu vực và Ngôn ngữ.
- Trong hộp thoại Khu vực , bấm vào Cài đặt bổ sung.
- Bấm vào tab Ngày .
- Trong danh sách Định dạng ngày dạng Ngắn , bấm vào định dạng sử dụng bốn chữ số cho năm ("yyyy").
- Bấm vào OK.
Windows 8
- Vuốt từ cạnh phải màn hình vào, nhấn Tìm kiếm (hoặc nếu bạn đang sử dụng chuột, hãy trỏ đến góc trên bên phải màn hình, di chuyển con trỏ chuột xuống dưới, rồi bấm Tìm kiếm), nhập Panel điều khiển vào hộp tìm kiếm, rồi nhấn hoặc bấm vào Panel điều khiển.
- Bên dưới Đồng hồ, Ngôn ngữ và Khu vực, hãy bấm Thay đổi định dạng ngày, giờ hoặc số.
- Trong hộp thoại Khu vực , bấm vào Cài đặt bổ sung.
- Bấm vào tab Ngày .
- Trong danh sách Định dạng ngày dạng Ngắn , bấm vào định dạng sử dụng bốn chữ số cho năm ("yyyy").
- Bấm vào OK.
Windows 7
- Bấm nút Bắt đầu rồi bấm Ngăn Điều khiển.
- Bấm vào Khu vực và Ngôn ngữ.
- Trong hộp thoại Khu vực , bấm vào Cài đặt bổ sung.
- Bấm vào tab Ngày .
- Trong danh sách Định dạng ngày dạng Ngắn , bấm vào định dạng sử dụng bốn chữ số cho năm ("yyyy").
- Bấm vào OK.
Thay đổi hệ thống ngày trong Excel
Hệ thống ngày sẽ tự động thay đổi khi bạn mở tài liệu từ nền tảng khác. Ví dụ: nếu bạn đang làm việc trong Excel và mở một tài liệu được tạo trong Excel for Mac, hộp kiểm Hệ thống ngày 1904 sẽ được chọn tự động.
Bạn có thể thay đổi hệ thống ngày bằng cách thực hiện các thao tác sau:
- Bấm Tùychọn>Tệp>Nâng cao.
- Bên dưới mục Khi tính toán sổ làm việc này , hãy chọn sổ làm việc bạn muốn, rồi chọn hoặc bỏ chọn hộp kiểm Dùng hệ thống ngày 1904 .
Sự cố: Tôi đang gặp vấn đề với ngày tháng giữa các sổ làm việc dùng các hệ thống ngày khác nhau
Bạn có thể gặp phải sự cố khi sao chép và dán ngày tháng hoặc khi tạo tham chiếu ngoài giữa các sổ làm việc dựa trên hai hệ thống ngày khác nhau. Ngày có thể sớm hơn hoặc muộn hơn bốn năm và một ngày so với ngày mà bạn mong đợi. Bạn có thể gặp phải những vấn đề này dù bạn đang dùng Excel for Windows, Excel for Mac hay cả hai.
Ví dụ, nếu bạn sao chép ngày 5 tháng 7 năm 2007 từ một sổ làm việc sử dụng hệ thống ngày 1900 và sau đó dán ngày vào một sổ làm việc sử dụng hệ thống ngày 1904, ngày sẽ xuất hiện là 6 tháng 7 năm 2011, tức là 1.462 ngày sau. Ngoài ra, nếu bạn sao chép ngày 5 tháng 7 năm 2007 từ một sổ làm việc sử dụng hệ thống ngày 1904 và sau đó dán ngày vào một sổ làm việc sử dụng hệ thống ngày 1900, ngày sẽ xuất hiện là 4 tháng 7 năm 2003, nghĩa là 1.462 ngày trước đó. Để biết thông tin cơ bản, hãy xem Hệ thống ngày trong Excel.
Sửa vấn đề sao chép và dán
Trong ô trống, nhập giá trị 1462.
Chọn ô đó, sau đó trên tab Trang chủ , trong nhóm Bảng tạm , bấm Sao chép.
Chọn tất cả các ô chứa ngày tháng không chính xác.
Mẹo
Để hủy bỏ một vùng chọn ô, hãy bấm vào bất kỳ ô nào trên trang tính. Để biết thêm thông tin, hãy xem các ô, phạm vi, hàng hoặc cột trên một trang tính.
Trên tab Trang đầu , trong nhóm Bảng tạm , bấm Dán, rồi bấm Dán Đặc biệt.
Trong hộp thoại Dán Đặc biệt , bên dưới Dán, bấm vào Giá trị, rồi bên dưới Thao tác, hãy thực hiện một trong các thao tác sau:
- Để đặt ngày thành bốn năm và một ngày sau, bấm Thêm.
- Để đặt ngày thành bốn năm và trước đó một ngày, bấm Trừ.
Sửa vấn đề về tham chiếu ngoài
Nếu bạn đang dùng tham chiếu ngoài đến ngày trong sổ làm việc khác với hệ thống ngày khác, bạn có thể sửa đổi tham chiếu ngoài bằng cách thực hiện một trong những thao tác sau đây:
- Để đặt ngày thành bốn năm và một ngày sau, hãy cộng 1.462 vào ngày đó. Ví dụ:
=[Book2]Sheet1!$A$1+1462
- Để đặt ngày thành bốn năm và sớm hơn một ngày, trừ đi 1.462 từ nó. Ví dụ:
Bạn cần thêm trợ giúp?
Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.
Xem Thêm
Định dạng ngày theo cách bạn muốn
Hiển thị số dưới dạng ngày hoặc giờ