Nội dung chính
Ngày tháng là một phần không thể tránh khỏi của Google Sheets. Và giống như nhiều khái niệm khác về bảng tính, chúng đòi hỏi một chút học hỏi.
Trong hướng dẫn này, bạn sẽ tìm hiểu cách Google lưu trữ ngày và cách bạn có thể định dạng chúng để thuận tiện hơn. Một số định dạng ngày được cung cấp cho bạn bằng bảng tính trong khi các định dạng khác nên được tạo từ đầu. Thậm chí có một vài chức năng tiện dụng cho nhiệm vụ.
Tôi cũng mô tả một vài cách để chuyển đổi ngày thành số và văn bản nếu cần thiết.
Định dạng Google Sheets ngày như thế nào
Điều đầu tiên trước tiên: trước mọi hoạt động liên quan đến ngày trong bảng tính, điều quan trọng là phải hiểu các nguyên tắc cốt lõi về cách thức hoạt động của ngày.
Đối với cơ sở dữ liệu nội bộ của mình, Google Sheets lưu trữ tất cả các ngày dưới dạng số nguyên. Không phải các chuỗi ngày, tháng và năm như chúng ta đã từng thấy, mà là các số nguyên đơn giản:
- 1 cho ngày 31 tháng 12 năm 1899
- 2 cho ngày 1 tháng 1 năm 1900
- 102 cho ngày 4 tháng 11 năm 1900 (100 ngày sau ngày 1 tháng 1 năm 1900)
- và như thế.
Không giống như Excel không thể lưu trữ ngày dưới dạng số âm, trong Google, đối với các ngày trước ngày 31 tháng 12 năm 1899, các số sẽ âm:
- -1 cho ngày 29 tháng 12 năm 1899
- -2 cho ngày 28 tháng 12 năm 1899
- -102 cho ngày 19 tháng 9 năm 1899
- Vân vân.
Bất kể định dạng Google Sheets định ngày như thế nào để bạn nhìn thấy trong các ô, bảng tính luôn lưu trữ chúng dưới dạng số nguyên. Đây là định dạng ngày Google Sheets tự động giúp xử lý ngày chính xác.
Tiền boa. Điều tương tự cũng xảy ra với các đơn vị thời gian – chúng chỉ đơn thuần là số thập phân cho bảng của bạn:
- 0,00 cho 12:00 sáng
- 0,5 cho 12:00 chiều
- 0,125 cho 3:00 sáng
- 0,55 trong 1:45 chiều
- Vân vân.
Một ngày được ghép nối với thời gian được giữ dưới dạng một số nguyên có vị trí thập phân:
- 31,528.058 là ngày 26 tháng 4 năm 1986, 1:23 sáng
- 43.679.813 là ngày 2 tháng 8 năm 2019, 7:30 tối
Thay đổi định dạng ngày trong Google Sheets sang ngôn ngữ khác
Một điều quan trọng khác cần ghi nhớ là ngôn ngữ bảng tính của bạn.
Vị trí là những gì đặt trước định dạng ngày Google Sheets của bạn dựa trên khu vực của bạn. Do đó, nếu bạn hiện đang ở Mỹ, ngày 06 tháng 8 năm 2019 sẽ được đặt là ngày 8/6/2019 trong trang tính của bạn, trong khi đối với Vương quốc Anh, đó sẽ là ngày 6/8/2019.
Để đảm bảo tính toán chính xác, điều quan trọng là phải có bộ ngôn ngữ chính xác, đặc biệt nếu tệp được tạo ở một quốc gia khác:
- Đi đến Tệp> Cài đặt bảng tính trong menu Google Sheets.
- Tìm thấy Địa phương phía dưới cái Chung tab và chọn vị trí mong muốn từ danh sách thả xuống:
Tiền boa. Là một phần thưởng, bạn cũng có thể chỉ định múi giờ của mình ở đây để ghi lại lịch sử tập tin của bạn trong đó.
Ghi chú. Bản địa không thay đổi ngôn ngữ của Trang tính của bạn. Tuy nhiên, định dạng ngày sẽ được áp dụng cho toàn bộ bảng tính. Mọi người làm việc với nó sẽ thấy những thay đổi, bất kể vị trí của họ trên toàn cầu.
Cách thay đổi định dạng ngày trong Google Sheets
Nếu ngày trong bảng của bạn được định dạng không nhất quán hoặc tất cả những gì bạn có thể thấy là các bộ số lạ thay vào đó, đừng hoảng sợ. Bạn chỉ cần thay đổi định dạng ngày trong Google Sheets của mình bằng các công cụ tích hợp.
Định dạng ngày Google Sheets mặc định
- Chọn tất cả các ô bạn muốn định dạng.
- Đi đến Định dạng> Số trong menu bảng tính và chọn Ngày để chỉ xem ngày hoặc Ngày giờ để có được cả ngày và thời gian trong một ô:
Các số nguyên thành công biến thành định dạng mà bạn sẽ nhận ra trong nháy mắt. Đây là các định dạng ngày Google Sheets mặc định:
Tiền boa. Bạn có thể tìm thấy các định dạng tương tự nếu bạn nhấp vào 123 biểu tượng trên thanh công cụ bảng tính:
Định dạng ngày tùy chỉnh
Nếu bạn không thích cách định dạng Google Sheets theo ngày theo mặc định, tôi sẽ không đổ lỗi cho bạn. May mắn thay, có rất nhiều chỗ để ứng biến nhờ các định dạng ngày tùy chỉnh.
Bạn có thể truy cập chúng từ cùng một menu Google Sheets: Định dạng> Số> Định dạng khác> Định dạng ngày và giờ khác:
Bạn sẽ thấy cửa sổ với rất nhiều định dạng ngày tùy chỉnh khác nhau có sẵn. Bất cứ ai bạn chọn và áp dụng, ngày của bạn sẽ giống nhau:
Nếu bạn vẫn không hài lòng với sự xuất hiện của các ngày của mình, bạn có thể điều chỉnh định dạng ngày tùy chỉnh của riêng mình:
- Chọn các ô bạn muốn định dạng.
- Đi đến Định dạng> Số> Định dạng khác> Định dạng ngày và giờ khác.
- Đặt con trỏ vào trường ở trên cùng có chứa đơn vị ngày và xóa mọi thứ bằng Backspace hoặc là Xóa bỏ chìa khóa:
- Nhấp vào mũi tên ở bên phải của trường và chọn đơn vị bạn muốn có đầu tiên. Đừng quên gõ dấu phân cách sau đó.Lặp lại cho đến khi tất cả các đơn vị cần thiết được thêm vào (không phải lo lắng, bạn sẽ có thể thêm hoặc xóa chúng sau này):
- Lưu ý rằng mỗi đơn vị có mũi tên đôi bên phải của nó. Nhấp vào chúng và bạn sẽ có thể điều chỉnh cách chính xác để hiển thị giá trị.Đây là những gì tôi có thể chọn cho ngày:Bằng cách này, bạn có thể chỉnh sửa tất cả các giá trị, chèn thêm và xóa các giá trị lỗi thời. Bạn có thể tự do phân tách các đơn vị với các ký tự khác nhau bao gồm dấu phẩy, dấu gạch chéo và dấu gạch ngang.
- Khi bạn đã sẵn sàng, nhấp vào Ứng dụng.
Đây là định dạng tôi đã tạo và cách hiển thị ngày của tôi bây giờ:
Hàm QUERY cho Google Sheets để định dạng ngày
Có thêm một cách để thay đổi định dạng ngày trong Google Sheets – tất nhiên là có công thức. Vì đây không phải là lần đầu tiên tôi hiển thị QUERY cho bạn, tôi bắt đầu nghĩ về nó như một phương pháp chữa bệnh thực sự cho tất cả các bảng tính. 🙂
Tôi có một bảng ví dụ nơi tôi theo dõi lô hàng của một vài đơn hàng:
Tôi muốn thay đổi định dạng ngày trong cột B. Đây là công thức QUERY của tôi:
=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")
- đầu tiên, tôi chỉ định phạm vi của toàn bộ bảng của mình – A1: C7
- sau đó tôi đang yêu cầu công thức trả về tất cả các cột – lựa chọn *
- và đồng thời định dạng lại cột B theo cách tôi đưa vào công thức – định dạng B ‘d-mmm-yy (ddd)’
Công thức hoạt động như một nét duyên dáng. Nó trả về toàn bộ bảng của tôi và thay đổi định dạng ngày trong cột B:
Như bạn có thể nhận thấy, để thay đổi định dạng ngày thông qua công thức, tôi đã sử dụng mã đặc biệt đại diện cho những cái nhìn khác nhau của ngày, tháng và năm. Nếu bạn không quen thuộc với chúng, đây là danh sách các mã này cho ngày:
Mã | Sự miêu tả | Thí dụ |
d | Ngày không có số 0 đứng đầu trong 1-9 | 7 |
đ | Ngày có số 0 đứng đầu trong 1-9 | 07 |
ddd | Ngày viết tắt | Thứ Tư |
ổ đĩa | Ngày như một tên đầy đủ | Thứ tư |
m (nếu không có trước hoặc theo sau giờ hoặc giây) |
Tháng không có số 0 | số 8 |
mm (nếu không có trước hoặc theo sau giờ hoặc giây) |
Tháng có số 0 đứng đầu | 08 |
Ừm | Tháng viết tắt | Tháng tám |
Ừm | Tháng như một tên đầy đủ | tháng Tám |
Ừm | Thư đầu tiên của tháng | Một |
y hoặc là yy |
Năm hai chữ số | 19 |
yyy hoặc là yyyy |
Năm số đầy đủ | 2019 |
Sử dụng các mã này, bạn có thể định dạng ngày theo nhiều cách:
- Chỉ nắm giữ năm, tháng hoặc ngày:
=QUERY(A1:C7,"select * format B 'yyyy'")
- Trả lại ngày, tháng và ngày trong tuần:
=QUERY(A1:C7,"select * format B 'dd mmmm, dddd'")
Nhân tiện, bạn đã quen với định dạng ngày nào? 🙂
Google Sheets: chuyển đổi ngày thành số
Trong trường hợp bạn cần xem số thay vì ngày, một trong những phương pháp dưới đây sẽ hữu ích.
Chuyển đổi ngày thành số bằng cách thay đổi định dạng
- Chọn những ô có ngày mà bạn muốn chuyển đổi thành số.
- Đi đến Định dạng> Số và lần này chọn Con số trong số các lựa chọn khác.
- Voila – tất cả các ngày đã chọn đã biến thành số đại diện cho chúng:
Chức năng DATEVALUE cho Google Sheets
Một cách khác để Google Sheets chuyển đổi ngày thành số là sử dụng chức năng DATEVALUE:
= DATEVALUE (date_ chuỗi)
Ở đâu date_ chuỗi đại diện cho bất kỳ ngày nào trong định dạng bảng tính được biết đến. Ngày nên được đặt trong dấu ngoặc kép.
Ví dụ, tôi muốn chuyển đổi Ngày 17 tháng 8 năm 2019 đến một số Tất cả các công thức dưới đây sẽ trả về cùng một kết quả: 43694.
=DATEVALUE("August 17, 2019")
=DATEVALUE("2019-8-17")
=DATEVALUE("8/17/2019")
Tiền boa. Nếu bạn không chắc chắn liệu Google Sheets có hiểu định dạng bạn sắp nhập hay không, trước tiên hãy thử nhập ngày vào một ô khác. Nếu ngày được công nhận, nó sẽ được căn bên phải.
Bạn cũng có thể điền vào các ô của mình bằng các ngày trong một cột và sau đó tham chiếu chúng trong các công thức của bạn trong một cột khác:
=DATEVALUE(A2)
Google Sheets: chuyển đổi ngày thành văn bản
Chuyển đổi ngày thành văn bản trong bảng tính là nhiệm vụ cho chức năng TEXT:
= Văn bản (số, định dạng)
- con số – bất kể số lượng, ngày hoặc thời gian bạn cung cấp cho chức năng, nó sẽ trả lại dưới dạng văn bản.
- định dạng – văn bản sẽ được định dạng theo cách bạn chỉ định trong công thức.
Tiền boa. Để đặt định dạng chính xác, sử dụng cùng mã như bạn đã làm cho hàm QUERY.
Công thức dữ liệu thực có thể trông như thế này:
=TEXT("8/17/2019","YYYY-MM-DD")
Đây là cách tôi chuyển đổi ngày của mình – 17/8/2019 – thành văn bản và thay đổi định dạng cùng một lúc:
Đây là nó! Tôi hy vọng bây giờ bạn biết cách thay đổi định dạng ngày trong Google Sheets và chuyển đổi ngày thành số hoặc văn bản. Hãy chia sẻ những cách hay ho khác trong phần bình luận bên dưới. 😉