Ngăn lỗi ngày trong công thức sử dụng hàm DATE
Ngày và Tổng quan chức năng DATE
Chức năng DATE của bảng tính Google sẽ trả về ngày hoặc số sê-ri của một ngày bằng cách kết hợp các yếu tố ngày, tháng và năm riêng lẻ được nhập làm đối số của hàm.
Ví dụ: nếu hàm DATE sau được nhập vào ô bảng tính,
= DATE (2016,01,16)
số sê-ri 42385 được trả lại, đề cập đến ngày 16 tháng 1 năm 2016.
Thay đổi số sê-ri thành ngày
Khi được nhập riêng - như được hiển thị trong ô D4 trong hình trên, số sê-ri thường được định dạng để hiển thị ngày tháng. Các bước cần thiết để hoàn thành nhiệm vụ này được liệt kê bên dưới nếu cần.
Nhập ngày vào ngày
Khi được kết hợp với các chức năng Bảng tính Google khác, DATE có thể được sử dụng để tạo nhiều công thức ngày khác nhau như được hiển thị trong hình ở trên.
Một cách sử dụng quan trọng cho hàm - như được hiển thị trong các hàng từ 5 đến 10 trong hình trên - là để đảm bảo rằng ngày tháng được nhập và diễn giải chính xác bởi một số chức năng ngày khác của Bảng tính Google. Điều này đặc biệt đúng nếu dữ liệu đã nhập được định dạng dưới dạng văn bản.
Hàm DATE được sử dụng chủ yếu:
- để hiển thị ngày kết hợp các yếu tố ngày - chẳng hạn như năm, tháng hoặc ngày từ các vị trí khác nhau trong trang tính
- để đảm bảo rằng ngày được sử dụng trong tính toán được hiểu là ngày (dữ liệu số) thay vì dữ liệu văn bản
Cú pháp và đối số của hàm DATE
Cú pháp của hàm liên quan đến cách bố trí của hàm và bao gồm tên, khung và đối số của hàm.
Cú pháp cho hàm DATE là:
= DATE (năm, tháng, ngày)
năm - (bắt buộc) nhập năm dưới dạng số có bốn chữ số (yyyy) hoặc tham chiếu ô đến vị trí của nó trong trang tính
tháng - (bắt buộc) nhập tháng dưới dạng số có hai chữ số (mm) hoặc tham chiếu ô đến vị trí của nó trong trang tính
ngày - (bắt buộc) nhập ngày dưới dạng số có hai chữ số (dd) hoặc tham chiếu ô đến vị trí của nó trong trang tính
- giá trị! giá trị lỗi được trả về bởi hàm nếu dữ liệu văn bản được nhập thay vì một số - điều này bao gồm việc nhập tham chiếu đến ô chứa văn bản
- #NUM! giá trị lỗi được trả về bởi hàm nếu một số có năm chữ số - chẳng hạn như 20016 - được nhập cho đối số năm
- nếu một giá trị không hợp lệ cho các đối số tháng hoặc ngày, hàm sẽ tự động điều chỉnh đầu ra của hàm thành ngày hợp lệ tiếp theo. Ví dụ,
- = DATE (2016,13,1) - trong đó có 13 cho đối số tháng - điều chỉnh đối số năm và trả về 1/1/2017
- = DATE (2016,01,32) - trong đó có 32 ngày trong tháng 1 - điều chỉnh đối số tháng và trả về 2/01/2016
- nếu giá trị thập phân được nhập cho một đối số, giá trị được cắt ngắn thành giá trị số nguyên. Ví dụ: giá trị "10.25" sẽ được diễn giải là "10" theo hàm
Ví dụ về hàm DATE
Trong hình trên, hàm DATE được sử dụng kết hợp với một số hàm khác trong một số công thức ngày tháng.
Các công thức được liệt kê được dự định như là một ví dụ về việc sử dụng hàm DATE. Công thức trong:
- hàng 5 vào ngày đầu tiên của tháng hiện tại;
- hàng 6 chuyển đổi một chuỗi văn bản (ô A5) thành một ngày;
- hàng 7 hiển thị ngày trong tuần cho một ngày cho;
- hàng 8 đếm ngày giữa ngày hiện tại và ngày trước đó;
- hàng 9 chuyển đổi Số Ngày Julian (ô A9) thành ngày hiện tại;
- hàng 10 chuyển đổi ngày hiện tại (ô A10) thành Số Ngày Julian.
Thông tin bên dưới bao gồm các bước được sử dụng để nhập hàm DATE nằm trong ô B4. Đầu ra của hàm trong trường hợp này hiển thị ngày kết hợp được tạo bằng cách kết hợp các phần tử ngày riêng lẻ nằm trong các ô từ A2 đến C2.
Nhập hàm DATE
Các tùy chọn để nhập hàm và đối số của nó vào một trang tính bao gồm:
1) Nhập thủ công vào hàm đầy đủ - chỉ cần nhớ rằng thứ tự phải là yyyy, mm, dd, chẳng hạn như:
= DATE (2016,01,16) hoặc,
= DATE (A2, B2, C2) nếu sử dụng tham chiếu ô
2) Sử dụng hộp đề xuất tự động để nhập hàm và các đối số của nó
Bảng tính Google không sử dụng hộp thoại để nhập đối số của hàm như có thể tìm thấy trong Excel. Thay vào đó, nó có một hộp gợi ý tự động bật lên khi tên của hàm được gõ vào một ô.
Dấu phân cách bằng dấu phẩy
Khi sử dụng một trong hai phương thức để nhập hàm, hãy lưu ý rằng dấu phẩy ( , ) được sử dụng để tách các đối số của hàm trong các dấu ngoặc tròn.
Các bước bên dưới bao gồm cách nhập hàm DATE nằm trong ô B4 trong hình trên bằng cách sử dụng hộp đề xuất tự động .
- Nhấp vào ô D4 để đặt ô hiện hoạt - đây là nơi kết quả của hàm DATE sẽ được hiển thị
- Nhập dấu bằng (=) theo sau là tên của hàm - ngày tháng
- Khi bạn nhập, hộp đề xuất tự động xuất hiện với tên và cú pháp của các hàm bắt đầu bằng chữ D
- Khi DATE xuất hiện trong hộp, hãy nhấp vào tên bằng con trỏ chuột để nhập tên hàm và mở dấu ngoặc tròn vào ô D4
- Nhấp vào ô A2 trong trang tính để nhập tham chiếu ô này làm đối số năm
- Sau khi tham chiếu ô, hãy nhập dấu phẩy ( , ) để hoạt động như một dấu tách giữa các đối số
- Nhấp vào ô B2 để nhập tham chiếu ô này làm đối số tháng
- Sau khi tham chiếu ô, hãy nhập một dấu phẩy khác
- Bấm vào ô C2 để nhập tham chiếu ô này làm đối số ngày
- Nhấn phím Enter trên bàn phím để vào dấu ngoặc tròn " ) " và để hoàn thành chức năng
- Ngày sẽ xuất hiện trong ô B1 theo định dạng 15/11/2015
- Khi bạn bấm vào ô B1, hàm đầy đủ = DATE (A2, B2, C2) xuất hiện trong thanh công thức phía trên trang tính
Lưu ý : nếu đầu ra trong ô B4 không chính xác sau khi nhập hàm, có thể ô được định dạng không chính xác. Dưới đây là các bước được liệt kê để thay đổi định dạng ngày.
Thay đổi định dạng ngày
Để thay đổi định dạng ngày trong Google Spreadsheets
- Đánh dấu các ô trong trang tính chứa hoặc sẽ chứa ngày
- Nhấp vào Định dạng> Số> Ngày trong trình đơn để thay đổi định dạng ô thành định dạng ngày được sử dụng bởi cài đặt khu vực hiện tại - xem bên dưới để thay đổi cài đặt khu vực.
Thay đổi cài đặt vùng
Giống như nhiều ứng dụng trực tuyến, Bảng tính Google mặc định theo định dạng ngày của Mỹ - còn được gọi là trung gian - của MM / DD / YYYY.
Nếu vị trí của bạn sử dụng định dạng ngày khác - chẳng hạn như bảng tính Google lớn (YYYY / MM / DD) hoặc nhỏ gọn (DD / MM / YYYY) có thể được điều chỉnh để hiển thị ngày theo đúng định dạng bằng cách điều chỉnh cài đặt khu vực .
Để thay đổi cài đặt khu vực:
- Nhấp vào Tệp để mở menu Tệp;
- Nhấp vào Cài đặt bảng tính ... để mở hộp thoại Cài đặt ;
- Trong Ngôn ngữ trong hộp thoại, nhấp vào hộp - giá trị mặc định của Hoa Kỳ - để xem danh sách các cài đặt quốc gia khả dụng;
- Nhấp vào quốc gia bạn chọn để chọn nó là lựa chọn hiện tại;
- Nhấp vào Lưu cài đặt ở cuối hộp thoại để đóng và quay lại trang tính;
- Các ngày mới được nhập vào trang tính phải theo định dạng của quốc gia đã chọn - các ngày hiện tại có thể cần được định dạng lại để thay đổi có hiệu lực.
Số sê-ri âm và Số ngày Excel
Theo mặc định, Microsoft Excel dành cho Windows sử dụng hệ thống ngày bắt đầu vào năm 1900. Nhập số sê-ri 0 trả về ngày: ngày 1 tháng 1 năm 1900. Ngoài ra, hàm DATE của Excel sẽ không hiển thị ngày trước năm 1900.
Bảng tính Google sử dụng ngày 30 tháng 12 năm 1899 cho số sê-ri bằng không, nhưng không giống như Excel, Bảng tính Google hiển thị ngày trước ngày này bằng cách sử dụng số âm cho số sê-ri.
Ví dụ: ngày 1 tháng 1 năm 1800 dẫn đến số sê-ri -36522 trong Bảng tính Google và cho phép sử dụng nó trong các công thức, chẳng hạn như trừ ngày 1 tháng 1 năm 1850 - ngày 1 tháng 1 năm 1800, kết quả là giá trị 18, 262 - số ngày giữa hai ngày.
Khi cùng một ngày được nhập vào Excel, mặt khác, chương trình sẽ tự động chuyển đổi ngày thành dữ liệu văn bản và trả về giá trị lỗi #VALUE! giá trị lỗi nếu ngày được sử dụng trong công thức.
Số ngày Julian
Số Ngày Julian, như được sử dụng bởi một số cơ quan chính phủ và các tổ chức khác, là những con số đại diện cho một năm và ngày cụ thể. Độ dài của những con số này thay đổi tùy thuộc vào số lượng chữ số được sử dụng để đại diện cho các thành phần năm và ngày của số.
Ví dụ, trong hình trên, Số Ngày Julian trong ô A9 - 2016007 - dài bảy chữ số với bốn chữ số đầu tiên của số đại diện cho năm và ba chữ số cuối cùng của ngày trong năm. Như được hiển thị trong ô B9, con số này đại diện cho ngày thứ bảy của năm 2016 hoặc ngày 7 tháng 1 năm 2016.
Tương tự, số 2010345 đại diện cho ngày thứ 345 của năm 2010 hoặc ngày 11 tháng 12 năm 2010.