Hướng dẫn điều chỉnh và thiết lập định dạng ngày tháng mặc định trên Excel

Bài viết dưới đây do Siêu Marketing thực hiện sẽ chỉ rõ cho bạn cách làm thế nào để thay đổi định dạng ngày tháng trên Excel, đồng thời giải thích về việc đặt định dạng mặc định cho dữ liệu ngày tháng và cách hay đổi ngày tháng phù hợp với từng địa phương cụ thể.

Ngày tháng cùng với thời gian là những loại dữ liệu được sử dụng khá thường xuyên khi làm việc với Excel. Nhưng chúng cũng có thể tạo ra khó khăn do có rất nhiều cách để hiển thị cùng thông tin và Excel lại thường tự động điều chỉnh định dạng ngày tháng theo chuẩn mặc định của nó, dù cho bạn muốn nó được hiển thị theo cách khác.

Tìm hiểu kĩ cách Excel định dạng ngày tháng sẽ giúp bạn tiết kiệm công sức, và đấy cũng là lý do chính mà bài viết này muốn đạt được.

Định dạng ngày tháng trong Excel

Trước khi bắt đầu sử dụng các tính năng liên quan đến ngày tháng và thời gian của Excel, điều quan trọng cần biết là Excel lưu trữ dữ liệu này như thế nào. Bởi thường xuyên người dùng gặp rắc rối trong việc xử lý dữ liệu là do không hiểu cách Excel ghi nhận nó. Excel không giữ thông tin dưới dạng ngày, tháng, năm mà lưu dưới dạng số và dùng định dạng ở ô để chuyển dãy số thành ngày, tháng hoặc giờ, phút.

Đối với ngày tháng trong Excel

Trong Excel, ngày tháng sẽ được ghi dưới dạng số nguyên, thể hiện cho số ngày tính từ 1/1/1990. Cách này có thể giải thích như sau:
Số 1 đại diện cho 1/1/1990
Số 2 đại diện cho 2/1/1990
Số 3 đại diện cho 3/1/1990
Số 42005 đại diện cho 1/1/2015 (vì có sự chênh lệch là 42,005 ngày giữa ngày 1/1/2015 và 1/1/1990)
Số 2958465 đại diện cho 31/12/9999

Đối với thời gian trong Excel

Thời gian, bao gồm giờ, phút và giây, được biểu diễn bằng số thập phân từ .0 đến .99999 trong Excel, phản ánh việc chia thời gian trong một ngày, cụ thể là:
Số 0 đại diện cho 00:00:00
Số 0.25 đại diện cho 06:00 sáng
Số 0.5 đại diện cho 12:00 trưa
Số 0.541655093 đại diện cho 12:59:59 tối

Về thời gian và ngày tháng trong Excel

Excel lưu giữ thông tin thời gian và ngày tháng bằng cách kết hợp số nguyên (ngày) và số thập phân (thời gian). Ví dụ minh họa:
Số 1.25 chỉ ngày 1/1/1900 lúc 6:00 sáng
Số 42005.5 chỉ ngày 1/1/2015 lúc 12:00 trưa.

Phương pháp để đưa ngày tháng về dạng số trong Excel

Để chuyển ngày tháng hay thời gian sang dạng số, có hai phương pháp bạn có thể áp dụng.

1. Sử dụng cửa sổ Định dạng ô (Format Cells)

Chọn ô chứa dữ liệu ngày tháng bạn muốn chuyển đổi, sử dụng tổ hợp phím Ctrl + 1 để mở hộp thoại Format Cells, rồi lựa chọn tab General.

Trong trường hợp chỉ muốn nhìn số mà không cần thay đổi từ ngày tháng thành số, bạn chỉ cần ghi nhận số hiển thị tại ô Sample và nhấp vào Cancel để đóng hộp thoại lại. Nếu bạn muốn thực hiện việc đổi định dạng, hãy bấm OK.

2. Sử dụng hàm DATEVALUE và TIMEVALUE của Excel

Để chuyển ngày tháng sang số, hãy áp dụng hàm DATEVALUE(), ví dụ: =DATEVALUE(“1/1/2015”).

Hàm TIMEVALUE() giúp bạn biến thời gian thành số thập phân, ví dụ: =TIMEVALUE(“6:30 AM”).

Combining the two functions, you can get a number representing both the date and time, such as:
=DATEVALUE(“1/1/2015”) & TIMEVALUE(“6:00 AM”)

Ghi chú: Excel không ghi nhận số âm trong dãy số của nó, do đó không có khả năng chuyển ngày trước ngày 1 tháng 1 năm 1900 thành số. Kết quả là, nhập ngày từ trước năm 1900 không thể chuyển thành dạng số.

Ví dụ, nếu bạn nhập ngày 31/12/1899 (hay 12/31/1899 theo định dạng tháng/ngày của Excel), giá trị này sẽ không được hiển thị dưới dạng số mà là dạng văn bản. Sử dụng công thức =DATEVALUE(“12/31/1899”) sẽ dẫn đến lỗi #VALUE!.

Định dạng ngày mặc định trong Excel

Trong Excel, định dạng ngày dài hoặc ngắn được áp dụng dựa trên cài đặt khu vực của Windows. Trong hộp thoại Format Cells, các định dạng mặc định được hiển thị với dấu *.

Nếu thay đổi cài đặt ngày tháng mặc định của Windows thông qua Control Panel, định dạng ngày trong Format Cells cũng sẽ cập nhật.

Cách chỉnh sửa định dạng ngày và thời gian trong Excel

Để sửa đổi định dạng ngày hoặc thời gian mặc định, như thay đổi từ định dạng Mỹ sang Anh, bạn vào Control Panel và mục Region and Language. Trong trường hợp Control Panel ở chế độ Category, bạn điều hướng theo Clock, Language and Region > Region and Language > Change the date, time, or number format.

Chọn khu vực thích hợp trong tab Formats như hình minh họa bên dưới:

Tip: Để hiểu ý nghĩa của các ký hiệu như mmm, ddd, yyy, hãy click vào liên kết “What does the notation mean” dưới phần Date and time formats.

Nếu các định dạng trong mục Formats không đáp ứng yêu cầu, bạn vào mục Additional settings ở góc dưới bên phải của hộp thoại Region and Language. Tiếp theo, chuyển sang tab Date và nhập định dạng mong muốn trong hộp thoại Customize.

Cách định dạng nhanh ngày và thời gian trong Excel

Excel cung cấp hai loại định dạng ngày và thời gian: dài và ngắn.

Để thực hiện việc định dạng nhanh theo cài đặt mặc định, hãy làm như sau:

  • Chọn dữ liệu ngày tháng riêng biệt.
  • Trên tab Home, trong mục Number, click vào mũi tên kế bên ô Number Format và lựa chọn định dạng bạn mong muốn – short date, long date hoặc time.

Để xem thêm lựa chọn, hãy chọn More Number Formats từ danh sách hoặc nhấn vào Dialog Box Launcher gần mục Number. Lựa chọn này sẽBản có thể kích hoạt hộp thoại Format Cells giống như được mô tả ở trên để chỉnh sửa định dạng mặc định.

Lời khuyên. Để nhanh chóng thực hiện thay đổi định dạng trong Excel sang dd-mmm-yy, bạn có thể dùng tổ hợp phím Ctrl + Shift + #. Điều quan trọng cần ghi nhớ là phương thức này chỉ thực hiện định dạng dd-mmm-yy, ví dụ 01-Jan-15, bất kể định dạng chuẩn của Windows là như thế nào.

Bí quyết đổi định dạng ngày tháng trên Excel

Trong Excel, bạn có thể hiển thị ngày tháng theo nhiều cách khác nhau. Muốn cập nhật định dạng cho một hoặc nhiều ô, bạn chỉ cần thực hiện các bước sau đây để mở hộp thoại Format Cells và lựa chọn một định dạng như sau:

1. Đánh dấu ô ngày tháng bạn muốn đổi định dạng, hoặc ô trống chuẩn bị để nhập ngày tháng.
2. Sử dụng phím tắt Ctrl + 1 để mở Format Cells, hoặc click chuột phải vào ô và chọn Format Cells.
3. Trong cửa sổ Format Cells, đi tới tab Number và tìm mục Date trong bảng Category.
4. Chọn định dạng bạn mong muốn tại Type và xem trước mẫu trong ô Sample dựa trên ngày tháng bạn đã nhập.

5. Hoàn tất việc chọn lựa, nhấn OK để lưu các thay đổi và đóng cửa sổ.

Nếu không thấy sự thay đổi định dạng, có khả năng là ngày tháng đã nhập được xem là dạng văn bản và bạn cần phải chuyển đổi nó thành ngày tháng đúng đắn trước.

Chuyển định dạng ngày tháng dựa theo vùng địa lý

Trong Excel, bạn muốn chuyển toàn bộ những định dạng ngày tháng không thống nhất sang một dạng chuẩn nào đó. Ví dụ, để đổi từ định dạng kiểu Mỹ (tháng/ngày/năm) sang kiểu Châu Âu (ngày/tháng/năm), hãy thực hiện các bước sau:

  • Chọn ô cần đổi định dạng ngày tháng.
  • Sử dụng Ctrl+1 để mở hộp thoại Format Cells.
  • Lựa chọn vùng địa lý mong muốn tại phần Locale (location) trong hộp thoại và sau đó nhấp OK để cập nhật. Trước khi nhấn OK, bạn có thể xem trước định dạng trong phần mẫu.

Để ngày tháng xuất hiện bằng ngôn ngữ khác, bạn sẽ cần đến việc tạo ra một định dạng tùy chỉnh với mã khu vực cụ thể.

Phương pháp tạo định dạng ngày tháng riêng biệt trong Excel

Trường hợp không tìm thấy định dạng nào sẵn có trong Excel phù hợp với yêu cầu, bạn hoàn toàn có thể tự tạo một định dạng mới.

1. Trong bảng tính Excel, lựa chọn ô bạn muốn chỉnh sửa định dạng.
2. Gõ phím tắt Ctrl + 1 để mở ra hộp thoại Format Cells.
3. Chọn mục Custom trong tab Number ở bảng Category và nhập vào định dạng bạn mong muốn tại ô Type.

Mẹo. Bạn có thể bắt đầu từ một định dạng sẵn có và tiếp tục chỉnh sửa để có được định dạng mới phù hợp với mong muốn của bạn. Để thực hiện,Khởi đầu, bạn hãy lựa chọn một định dạng có sẵn trong phần Type tại Date của bảng Category. Kế đến, truy cập phần Custom để chỉnh sửa theo định dạng bạn mong muốn.

Để tùy chỉnh ngày tháng trên Excel, hãy sử dụng các ký hiệu sau đây:

m Tháng không bắt đầu bằng số 0 1
mm Tháng bắt đầu bằng số 0 01
mmm Tháng rút gọn Jan
mmmm Tháng đầy đủ January
mmmmm Chữ cái đầu tiên của tên tháng J (cho January, June và July)
d Ngày không bắt đầu bằng số 0 1
dd Ngày bắt đầu bằng số 0 01
ddd Thứ ngắn gọn Mon
dddd Thứ đầy đủ Monday
yy Năm hai chữ số 05
yyyy Năm bốn chữ số 2005

Về định dạng thời gian trên Excel, dùng các ký tự sau:

h Giờ không có số 0 phía trước 0-23
hh Giờ có số 0 phía trước 00-23
m Phút không có số 0 phía trước 0-59
mm Phút có số 0 phía trước 00-59
s Giây không có số 0 phía trước 0-59
ss Giây có số 0 phía trước 00-59
AM/PM Kỳ gian buổi sáng hoặc chiều 6AM or 6PM

Lưu ý: Khi bạn cần tạo định dạng ngày giờ mà sử dụng “m” ngay sau “hh” hoặc “h”, hoặc ngay trước “ss” hay “s”, Excel sẽ nhận ra rằng đó là phút, chứ không phải là tháng.

Định dạng cũng có thể bao gồm dấu phẩy, dấu gạch chéo, dấu gạch ngang và dấu hai chấm.

Xem xét ví dụ với ngày “January 13, 2015 13:03”, có rất nhiều cách biểu thị:

dd-mmm-yy 13-Jan-15
mm/dd/yyyy 01/13/2015
m/dd/yy 1/13/15
dddd, m/d/yy h:mm AM/PM Tuesday, 1/13/15 1:03 PM
ddd, mmmm dd, yyyy hh:mm:ss Tue, January 13, 2015 13:03:00

Cách tạo định dạng ngày tháng tùy thuộc vào khu vực địa lý trên Excel như thế nào?

Bạn có thể cần phải sử dụng các ký tự đặc biệt dành cho mỗi khu vực cụ thể, được đặt trong dấu ngoặc vuông, kèm theo dấu $ và dấu gạch ngang. Ví dụ:

[$-409] – Tiếng Anh theo kiểu Mỹ
[$-1009] – Tiếng Anh theo kiểu Canada
[$-407] – Tiếng Đức theo kiểu Đức
[$-807] – Tiếng Đức theo kiểu Thụy Sĩ
[$-804] – Tiếng Bengali của Ấn Độ
[$-804] – Tiếng Trung tại Trung Quốc
[$-404] – Tiếng Trung tại Đài Loan

Bạn có thể tham khảo một danh sách đầy đủ các ký tự khu vực tại đây.

Ví dụ, nếu bạn muốn tạo một định dạng ngày tháng theo kiểu Trung Quốc thể hiện theo thứ tự năm-tháng-ngày, bạn có thể nhập như sau:

Dưới đây là vài ví dụ về ngày tháng được hiển thị bằng các mã địa điểm khác nhau cho nhiều ngôn ngữ khác nhau:

Bạn muốn chèn ngày tháng trên Excel một cách dễ dàng dùng Calendar Control?

Nếu bạn đang tìm kiếm phương pháp nào để chèn ngày tháng trong

Bạn đã từng sử dụng tính năng Calendar Control trong Excel để chèn ngày tháng vô cùng tiện lợi chưa? Chỉ mất không quá 30 giây, bạn có thể dùng công cụ này để thêm ngày tháng vào bảng tính của mình.

Để biết cách tải xuống, hãy theo dõi video sau.

[wpcc-iframe src=”https://www.youtube.com/embed/2sMWMHQ1Rjs” width=”560″ height=”314″ allowfullscreen=”allowfullscreen”]

Khi định dạng ngày tháng trong Excel gặp trục trặc – bạn có thể thử các giải pháp sau

Excel thường xử lý rất tốt các thông tin dữ liệu, nhưng đôi khi bạn có thể gặp phải sự cố định dạng. Dưới đây là một số mẹo hữu ích.

Khi Ô không đủ lớn để hiển thị nội dung

Các ô hiển thị dãy dấu ##### thay vì ngày tháng báo hiệu rằng chúng quá nhỏ để chứa đầy đủ thông tin.

Giải pháp. Đôi khi chỉ cần một cú click chuột kéo cạnh phải của ô ra để thích ứng với nội dung hoặc kéo cạnh ô rộng hơn.

Số âm hiện ra như ngày tháng

Ở một số phiên bản Excel như 2010, 2013 và 2017, các ô định dạng dưới dạng ngày tháng có thể chỉ hiển thị dãy ##### nếu chứa giá trị âm. Điều này thường do kết quả của một phép tính hoặc khi bạn nhập giá trị âm sau đó thay đổi định dạng ô. Cho trường hợp muốn số âm hiển thị dưới dạng ngày âm, bạn có hai phương án.

Giải pháp 1. Chuyển qua hệ thống ngày thời gian 1904.

Vào File > Options > Advanced, tìm tới When calculating this workbook, chọn Use 1904 date system và nhấn OK.

Trong hệ thống này, số 0 tương ứng với ngày 1-Tháng 1-1904; còn số 1 và -1 sẽ lần lượt là ngày 2-Tháng 1-1904 và ngày âm -2-Tháng 1-1904.

Tuy phương pháp này không quen thuộc nhưng sẽ chính xác khi bạn cần biểu thị các ngày trước 1-Tháng 1-1904.

Giải pháp 2. Sử dụng hàm TEXT trong Excel

Bạn cũng có thể dùng hàm TEXT để biện diễn số âm thành ngày âm. Ví dụ: nếu muốn trừ C1 cho B1 và giá trị C1 lớn hơn B1, dùng công thức như sau để ra ngày tháng:
=TEXT(ABS(B1-C1),”-d-mmm-yyyy”)

Nếu bạn muốn căn lề phải, hãy thay đổi định dạng ngày tháng theo ý muốn trong công thức hàm TEXT.

Chú ý. Hàm TEXT chỉ đưa ra giá trị văn bản nên bạn sẽ không dùng được kết quả cho các phép tính khác.

Thời gian được nhập vào EXCEL dưới dạng văn bản

Đôi khi khi nhập dữ liệu từ file .csv hoặc các nguồn bên ngoài khác, thời gian trong Excel có thể dưới dạng văn bản mặc dù trông giống như định dạng ngày tháng.

Giải pháp. Để chuyển đổi từ “ngày tháng dạng văn bản” sang định dạng ngày tháng chuẩn cần sử dụng hàm DATEVALUE hoặc chức năng Text to Columns.

Bài viết trên đã cung cấp cách thiết lập định dạng ngày tháng trong Excel. Bài viết tiếp theo từ Siêu Marketing sẽ hướng dẫn bạn cách nhập dữ liệu ngày giờ vào Excel. Cảm ơn và hẹn gặp lại.

Để nâng cao kiến thức Excel, bạn đừng quên tham gia khóa học SM90 – Excel từ cơ bản đến chuyên gia của Siêu Marketing. Khóa học sẽ giúp bạn khám phá cách sử dụng các công cụ như conditional formatting, data validation, pivot table và nhiều hàm trong Excel từ cơ bản đến nâng cao. Hiện tại đang có nhiều ưu đãi lớn khi đăng ký, xem chi tiết tại Blogsieumarketing.com.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop