Cách thay đổi định dạng ngày thành số và văn bản

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:

  1. Đi đến Tệp> Cài đặt bảng tính trong menu Google Sheets.
  2. 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:Thay đổi ngôn ngữ bảng tính của bạn để đảm bảo định dạng ngày chính xác của Google Sheets.

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

  1. Chọn tất cả các ô bạn muốn định dạng.
  2. Đ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ách định dạng ngày trong Google Sheets.

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:

Định dạng tự động (số nguyên) so với định dạng ngày.

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:

Một cách khác để thay đổi định dạng ngày trong Google Sheets.

Đị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:

Định dạng ngày và giờ tùy chỉnh của Google Sheets.

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:

Áp dụng các định dạng tùy chỉnh từ danh sách.

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:

  1. Chọn các ô bạn muốn định dạng.
  2. Đi đến Định dạng> Số> Định dạng khác> Định dạng ngày và giờ khác.
  3. Đặ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:Xóa định dạng ngày tùy chỉnh hiện có.
  4. 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):Thêm nhiều đơn vị vào định dạng ngày tùy chỉnh của bạn khi bạn cần.
  5. 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:Chọn cách ngày sẽ nhìn trong định dạng ngày của bạn.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.
  6. 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ờ:

Google Sheets định dạng ngày tùy chỉnh một lần nữa.

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:

Bảng của tôi với các đơn đặt hàng vận chuyển.

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:

Thay đổi định dạng ngày trong Google Sheets bằng chức năng QUERY.

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:

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
Tiền boa. Nếu bạn cũng muốn cung cấp định dạng ngày của mình theo thời gian, bạn cần thêm mã cho các đơn vị thời gian. Bạn sẽ tìm thấy danh sách đầy đủ các mã thời gian trong hướng dẫn này.

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

  1. Chọn những ô có ngày mà bạn muốn chuyển đổi thành số.
  2. Đ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.
  3. Voila – tất cả các ngày đã chọn đã biến thành số đại diện cho chúng:Chuyển đổi định dạng Ngày thành định dạng Số.

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:

Google Sheets: chuyển đổi ngày thành văn bản và thay đổi định dạng.

Đâ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. 😉

Bình chọn

Xếp hạng 5 / 5. Tổng số bình chọn 7

Trả lời