Cách định dạng ngày tháng năm trong Excel đơn giản, ví dụ dễ hiểu

Trong Excel, bạn có thể dễ dàng định dạng ngày tháng năm bằng cách sử dụng các công cụ có sẵn. Bài viết này sẽ cùng bạn tìm hiểu về cách định dạng ngày tháng trong Excel tuỳ theo đất nước mà bạn đến, cũng như đặt thành thời gian mặc định của Excel.

1. Cách đổi định dạng ngày tháng năm trong Excel

Trong Excel, khi bạn nhập dữ liệu ngày tháng năm, Excel không tự hiểu nó là ngày tháng. Thay vào đó, nó lưu trữ chúng ở dạng Text (mặc định là General). Vì vậy, định dạng của ô không nhận biết rằng đó là ngày tháng, gây khó khăn cho việc xử lý dữ liệu.

Để giải quyết vấn đề này, bạn có thể sử dụng các công cụ định dạng ngày tháng năm trong Excel để giúp Excel hiểu và hiển thị dữ liệu thời gian chính xác.

Mỗi khi thay đổi định dạng trong một hay nhiều ô hãy mở hộp thoại Format Cells và làm theo các bước sau để thay đổi định dạng ngày tháng trong Excel:

Bước 1. Chọn ô ngày tháng năm mà bạn muốn thay đổi định dạng hoặc một ô trống bất kỳ mà bạn muốn điền ngày tháng năm.

Bước 2. Bấm tổ hợp Ctrl + 1 để mở cửa sổ Format Cells.

Bước 3. Cửa sổ Format Cells hiện ra, ta chọn tab Number > Chọn Date.

Bước 4. Ở phần Type, lựa chọn đình dạng ngày tháng năm mà bạn muốn. Xem định dạng ngày tháng năm trong Excel sẽ hiển thị như thế nào ở mục Sample.

Bước 5. Nhấn OK để lưu và đóng cửa sổ.

Lưu ý: Nếu định dạng bạn yêu cầu không thay đổi thì có thể do ban đầu dữ liệu ngày tháng năm ban đầu bạn điền vào đã dưới dạng văn bản từ trước. Lúc này bạn cần sẽ phải chuyển nó về ngày tháng năm dạng chữ số.

Xem thêm: 2 cách làm hiển thị đầy đủ dữ liệu ngày tháng trong Excel

2. Định dạng ngày tháng năm trong Excel thành số (Number)

Cách Excel quy ước ngày tháng năm ở dạng Number

Theo thông tin từ Microsoft thì thời gian của Excel bắt đầu được hiển thị số từ ngày 1/1/1990. Dữ liệu thời gian trong Excel phải được lưu trữ là số nguyên. Ví dụ như sau:

• Số 1 tức ngày 1/1/1990
• Số 2 tức ngày 2/1/1990
• Số 42005 tức là ngày 1/1/2015 (có nghĩa ngày 1/1/1990 cách ngày 1/1/2015 tới 42005 ngày).
• Tương tự với con số 2958465 tức ngày 31/12/2999.

Xem thêm: Hướng dẫn cách định dạng số và ngày tháng trong Excel

Định dạng ngày tháng năm trong Excel thành số bằng Format Cells

Chẳng hạn yêu cầu công việc của bạn là điền dãy số tượng trưng cho thời gian thay vì đổi ngày tháng năm thành số, chúng ta định dạng lại như sau:

Chọn ô bất kỳ chứa dữ liệu ngày tháng năm thời gian trong Excel, nhấn Ctrl+1 (hoặc click chuột phải) để hiện hộp thoại Format Cell >> chọn General > nhấn OK.

Dùng hàm Datevalue và Timevalue để đổi định dạng ngày tháng năm trong Excel

Công thức Datevalue trong Excel để đổi từ ngày tháng năm sang một dãy số dài tượng trưng ở dạng Text.

=DATEVALUE("dd/mm/yyyy") 

Chẳng hạn ví dụ bên dưới =DATEVALUE(“1/1/2021”)

Còn Hàm TIMEVALUE() dùng để chuyển thời gian ngày tháng năm thành một dãy số thập phân, 

=TIMEVALUE(datetime)

Ví dụ: =TIMEVALUE(“6:30 AM”) đối với máy tính cài chế độ 12 giờ, và =TIMEVALUE("6:30") đối với máy cài chế độ 24 giờ.

 

Để hiển thị thông tin của cả ngày lẫn tháng trên một con dãy số thập phân, ta có thể sử dụng cả hai chức năng trên như sau: 

=DATEVALUE(“1/1/2015”) & TIMEVALUE(“6:00 AM”)

Chú ý: Thời gian trong Excel được tính từ ngày 1/1/1900, đồng nghĩa với việc thời điểm trước năm 1900 sẽ không được chuyển đổi sang số trong Excel. Ngoài ra thời gian trên Excel cũng không được là số âm.

Còn trong trường hợp bạn muốn điền thông tin thời điểm 12/31/1899 (tức ngày 31 tháng 12 năm 1899  theo cách viết của Excel và phương Tây) thì giá trị xuất ra sẽ hiển thị lỗi #VALUE!

3. Thao tác nhanh để đổi định dạng ngày tháng năm trong Excel

Excel đưa ra hai lựa chọn linh hoạt cho người sử dụng về dữ liệu thời gian và ngày tháng năm là dài và ngắn.

Cách đổi vị trí ngày tháng trong Excel về mặc định, ta sẽ làm như sau:

• Chọn dữ liệu ngày tháng năm mà bạn muốn định dạng.

• Trong phần Home, tại nhóm Number , nhấn con trỏ chuột ô đổi định dạng như hình bên dưới:

• Chọn định dạng trong danh sách xổ xuống mà bạn yêu cầu gồm Short date, long date và time.

Bên cạnh đó Excel bạn cũng có thể nhấn vào mũi tên ở góc dưới bên phải nhóm Number. Từ đây hộp thoại Format Cells sẽ mở ra tương tự trên và bạn có thể thay đổi cài đặt mặc định thời gian trong đó. 

Lưu ý: nếu bạn muốn đổi vị trí ngày tháng trong Excel nhanh thành dạng dd-mm-yy thì chỉ cần nhấn Ctrl + Shift + #. Bằng cách chuyển nhanh này thì sẽ áp dụng định dạng dd-mm-yy mà áp dụng định dạng ngày tháng trong Excel của Windows.

4. Chuyển định dạng ngày tháng năm trong Excel theo khu vực

Chẳng hạn bạn nhận được một bảng dữ liệu với định dạng ngày tháng năm bị xếp lộn xộn và công việc của bạn là xếp làm sao cho chúng đồng bộ với nhau. Chẳng hạn lúc này bạn nhận dữ liệu từ Mỹ (tháng/ngày/năm) và chuyển qua thành kiểu châu Âu (Ngày/tháng/năm), sau đây Gitiho sẽ chỉ cho bạn cách thực hiện hóa nhiệm vụ này.

• Chọn ô ngày tháng năm cần định dạng.
• Nhấn Ctrl +1 để hiện cửa sổ Format Cells > Number > Date.
• Ở mục Locale (Location) trong hộp thoại sẽ là nơi mà bạn chọn vị trí, sau đó nhấn Ok để lưu thay đổi. Ngoài ra bạn cũng có thể xem trước định dạng trong phần Sample trước khi nhấn OK.

Xem thêm: Mách bạn 4 cách định dạng thứ trong Excel

5. Tuỳ chỉnh định dạng ngày tháng năm trong Excel 

Nếu như những lựa chọn Excel đưa ra mà bạn không thấy ưng ý thì ta có thể tự tạo cho mình định dạng ngày tháng năm tuỳ chỉnh phù hợp theo yêu cầu.

Bước 1. Lựa chọn ô Excel bạn cần sửa.

Bước 2. Nhấn Ctrl + 1 để hiện Format Cells.

Bước 3. Ở cửa sổ Number, bạn chọn Custom, cùng với đó là điền định dạng bạn muốn trong ô Type bên cạnh.

Ký hiệu phổ biến khi tùy chỉnh ngày tháng năm trong Excel

m	Số tháng chỉ có hàng đơn vị	1
mm	Số tháng có thêm hàng chục hoặc thêm số 0 đứng trước (như tháng 10-11-12)	01
mmm	Tên tháng với 3 ký tự	Feb
mmmm	Tên tháng đầy đủ	July
mmmmm	Tên tháng được ký hiệu bằng chữ cái đứng đầu	J ký hiệu cho June, July, January
d	Số ngày không có số 0 đứng trước	1
dd	Số này có số 0 đứng trước hoặc hàng chục	01
ddd	Tên thứ dạng ngắn	Wed
dddd	Tên thứ dạng dài	Wednesday
yy	Năm hiển thị bằng hai con số	21
yyyy	Năm hiển thị đầy đủ	2021

Cần chú ý những ký hiệu sau khi tạo định dạng ngày tháng trong Excel:

h	Giờ không có số 0 đứng trước	0 - 23
hh	Giờ có số 0 đằng trước	00-23
m	Phút không có số 0 đằng trước	0-59
mm	Phút có số 0 đằng trước	00-59
s	Giây không có sô 0 đằng trước	0-59
ss	Giây có số 0 đằng trước	00-59
AM/P	Buổi sáng/buổi tối	6 AM và 6 PM

Bên cạnh đó, khi tạo một định dạng và sử dụng những ký hiệu như "m" sau “hh”, h" hoặc đứng trước “ss”, "s" thì Excel sẽ hiển thị ký hiệu m này là phút chứ không phải tháng.

Khi tạo định dạng, Excel vẫn cho phép bạn sử dụng những ký hiệu đơn giản như dấu phẩy, gạch ngang, gạch chéo, hai chấm.

Chẳng hạn như ta muốn hiểu thị ngày 6 tháng 1 năm 2021, 15 giờ 30 phút thì có rất nhiều cách hiển thị.

dd-mmyy	6-Jan-21
mm/dd/yy	01/6/2021
m/dd/yy	1/6/2021
dddd, m/d/yy h:mm AM/PM	Wednesday, 1/6/21 3:30PM
ddd, mmmm dd, yyyy hh:mm:ss	Wed, January 6, 2021 15:30:00

Xem thêm: Cách chuyển định dạng ngày tháng trong Excel (DATE) sang TEXT & NUMBER

Tùy chỉnh định dạng ngày giờ theo quốc gia

Nếu như bạn thường xuyên có ra nước ngoài, bạn cần hiện thị đúng thời gian của nơi bạn đang ở bằng cách sử dụng các ký hiệu khu vực tương ứng. Ký hiệu khu vực phải được để trong dấu ngoặc vuông, đi cùng với ký hiệu $ và dấu gạch ngang, chẳng hạn như.

[$-404] Tiếng Trung của Đài Loan

[$-804] Tiếng Trung của Trung Quốc

[$-807] Tiếng Đức của Thụy Sĩ

[$-407] Tiếng Đức của Đức

[$-1009] Tiếng Anh của Canada

[$-409] Tiếng Anh của Mỹ

Chẳng hạn bạn muốn tạo định dạng ngày tháng năm trong Excel theo giờ của Trung Quốc với thứ tự năm/tháng/ngày thì bạn sẽ cần điền thông tin như sau.

Xem thêm: Cách định dạng Ngày Tháng Năm trong Excel chuẩn và thống nhất

6. Sử dụng ngày tháng năm mặc định theo máy tính

Cách này sẽ được sử dụng khi chúng ta muốn chỉnh thời gian về khu vực thời gian của Microsoft đã đặt trước. Định dạng mặc định này sẽ được đánh dấu (*) trong hộp thoại Format Cells.

Định dạng này sẽ thay đổi khi bạn thay đổi vùng thời gian ngày tháng năm mặc định của Windows trong Control Panel.

Cách chỉnh ngày tháng năm mặc định trên máy tính

Cách này đặc biệt hữu dụng dành cho những người hay phải đi công tác nước ngoài bởi sự linh hoạt của cách chỉnh thời gian này. 

Ví dụ ta cần đổi định dạng ngày tháng trong Excel từ kiểu Mỹ sang kiểu Anh, ta sẽ làm như sau:

• Đầu tiên vào Control Panel, chọn Clock and Region

• Lúc này, hộp thoại Region hiện lên, trong tab Format, chỉ cần chỉnh lại khu vực phù hợp trong danh sách của Windows.

Ở hộp thoại này, chúng ta cũng có thể thử cách chuyển đổi từ ngày tháng năm sang tháng/ngày/năm trong Excel ở mục Short Date (từ d/M/yyyy đổi thành M/d/yyyy)

Cài đặt mặc định thời gian bổ sung trên máy tính

Nếu như bạn chưa tìm được lựa chọn phù hợp trong phần Formats, ta đi tới phần Additional Setting phía bên dưới cùng của hộp thoại Region. 

Từ đây hộp thoại Customize sẽ hiện ra và chuyển sang tab Date và nhập định dạng ngày tháng trong Excel mong muốn.

Xem thêm: Hướng dẫn 3 cách đơn giản sửa lỗi ngày tháng có dấu chấm ngăn cách dạng 14.03.2021

7. Sửa lỗi định dạng ngày tháng năm trong Excel không hoạt động

Tuy việc Excel xảy ra lỗi định dạng là không thường xuyên, nhưng nếu bạn gặp phải trục trặc trong việc thiết lập định dạng thì chúng ta nên xem qua những mẹo sau.

Ô không đủ rộng để chứa hết thông tin ngày tháng năm

Nếu có trường hợp bạn bắt gặp một ô dữ liệu có một loạt ký hiệu ##### thay vì hiện đơn vị thời gian ngày tháng năm thì khả năng ô này không đủ rộng để chứa đầy đủ thông tin.

Đầu tiên, bạn nháy chuột đôi vào bên phải của cột chứa ngày giờ để đưa kích cỡ về vừa đúng với ô thông tin, hoặc kéo cạnh bên phải của ô sang phải cho đến khi ô đủ rộng để chứa thông tin của bạn.

Biến số âm thành định dạng ngày tháng năm trong Excel

Lỗi #### cũng sẽ xuất hiện khi ô dữ liệu được định dạng ngày tháng nhưng lại chứa giá trị âm. Nếu bạn vẫn muốn hiển thị số âm thành ngày âm, Gitiho sẽ hướng dẫn bạn hai cách để thực hiện hóa công đoạn này.

 Cách 1: Đổi sang hệ thống thời gian 1904

Vào phần File -> Option -> Advanced, nhìn xuống phần When calculating this workbook rồi đánh dấu vào ô Use 1904 date system.

Với hệ thống này thì 0 là 1-Jan-1904; 1 là 2-Jan-1904 và -1 sẽ hiển thị cho ngày -2-Jan-1904.

Cách hiển thị thời gian kiểu này trông tương đối "kỳ quái" nhưng là cách chính xác nhất để bạn hiển thị phép tính cho ngày trước đó.

Cách 2: Dùng hàm TEXT

Một phương pháp khác để hiển thị ngày âm trên Excel là chức năng hàm TEXT. Nói dễ hiểu, bạn trừ B1 cho C1 và giá trị của C1 lớn hơn B1 thì công thức sau sẽ lợi dụng điều này để ra kết quả dưới dạng ngày tháng năm.

=TEXT(ABS(B1-C1),”-d-mmm-yyyy”)

Xem thêm: Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Chú ý: Đúng như cái tên của chức năng này, kết quả của TEXT là giá trị chữ, bởi vậy mà bạn không thể dùng kết quả này vào những phép tính toán khác.

Kết luận

Trên đây là những mẹo và thủ thuật sẽ giúp các bạn thuận tiện hơn trong việc thiết lập định dạng ngày tháng năm trong Excel. Gitiho.com mong rằng những kiến thức này sẽ giúp bạn đọc rút ngắn thời gian làm việc mà vân giữ được tính chính xác trong công việc. 

Chúc bạn thành công!