Bài viết này thuộc chuỗi bài viết học Excel từ Gitiho.com sẽ cùng bạn tìm hiểu về cách định dạng ngày tháng trong Excel tuỳ theo thay đổi của địa điểm, cũng như đặt thành thời gian mặc định của Excel.
Cách đổi định dạng ngày tháng năm trong Excel
Trên thực tế, mỗi lần bạn nhập một dữ liệu thời gian ngày tháng năm vào Excel, ứng dụng này sẽ không phân biệt được đâu là ngày tháng năm mà đơn giản Excel sẽ lưu trữ lại nó như một dãy số với ký tự tách từng số với nhau. Chính vì vậy mà định dạng ô chứa thông tin sẽ không “bắt sóng” được đây là định dạng về thời gian.
Bởi vậy đây là lí do chính gây khó khăn khi người sử dụng muốn lưu trữ dữ liệu về thời gian trong Excel.
Xem thêm: 2 cách làm hiển thị đầy đủ dữ liệu ngày tháng trong Excel
Cách Excel tính ngày tháng năm
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
Hướng dẫn định dạng ngày tháng trong Excel thành số
Tạo cửa sổ chứa định dạng ngày tháng trong Excel theo định dạng ô
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 để hiện hộp thoại Format Cell và chọn General.
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 hãy viết dãy số ấy ở ô Sample và nhấn Cancel để đóng hộp thoại. Và để đổi ngày tháng năm thành số chỉ cần nhấn OK.
Xem thêm: Hướng dẫn dùng checkbox để thay đổi giá trị hoặc định dạng ô trong Excel
Cách sử dụng hai chức năng Datevalue và Timevalue để đổi định dạng ngày tháng trong Excel
Công thức Datevalue trong Excel để đổi định dạng ngày tháng năm trong Excel từ ngày tháng năm sang một dãy số dài tượng trưng. Chẳng hạn như =DATEVALUE(“1/1/2021”).
Còn chức năng TIMEVALUE() để chuyển thời gian ngày tháng năm thành một dãy số thập phân, chẳng hạn như =TIMEVALUE(“6:30 AM”).
Để 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ẽ là chữ thay vì dưới dạng ngày tháng năm. Nếu như bạn nhập =DATEVALUE(“12/31/1899”), thì chắc chắn Excel sẽ hiển thị lỗi #VALUE!
Xem thêm: Hướng dẫn 3 cách sửa lỗi Value trong Excel
Sử dụng ngày tháng năm mặc định theo Excel
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 đổi định dạng ngày tháng năm trong Excel và thời gian
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ẽ vào Control Panel, chọn phần Region and Language. Còn khi bạn mở Control Panel mà hiện ra Categorythì chúng ta vào phần Clock -> Language and Region -> Region and language -> Change the date, time or number format.
Trong tab Format, chỉ cần chỉnh lại khu vực phù hợp trong danh sách của Windows.
.jpg)
Ở đây chúng ta sẽ thấy dòng chữ như dd/MM/yy tức là dd = day = ngày, mm = Month = tháng và cuối cùng là yy = Year = Năm. Hoặc để biết rõ hơn, chúng ta bấm vào dòng chữ xanh “What does the notation mean” bên dưới tab First day of week.
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 Region and Language. 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.
.jpg)
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
Cách thao tác nhanh đổi định dạng ngày tháng trong Excel và thời gian mặc định
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.
Để chỉnh 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 sẽ có thêm phần Number , nhấn con trỏ chuột vào cạnh ô Number Format rồi chọn định dạng mà bạn yêu cầu gồm Short date, long date và time.
Bên cạnh đó Excel cũng sẽ đưa ra một vài tuỳ chọn khác qua tab More Number Format từ danh sách, hoặc là Dialog Box Launcher bên cạnh phần Number. Từ đây hộp thoại Format Cells sẽ mở ra tương tự trên và bạn có thể thay đổi mặc định trong đó.
Lưu ý: nếu bạn muốn chuyển đổi thời gian nhanh thành form thời gian 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à không quan trọng định dạng ngày tháng trong Excel của Windows.
Tạo định dạng thời gian ngày tháng năm tuỳ chỉnh
Nếu như những lựa chọn mà Excel đưa ra cho người dụng 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.
1. Lựa chọn ô Excel bạn cần sửa.
2. Nhấn Ctrl + 1 để hiện Format Cells.
3. Ở cửa sổ Number, bạn chọn Custom ở bảng Category, cùng với đó là điền định dạng bạn muốn trong ô Type bên cạnh.
Mẹo: Cách đơn giản nhất là dựa trên định dạng có sẵn mà tạo nên định dạng gần giống với định dạng mà bạn muốn. Bước đầu tiên là ta vào ô Date trong bảng Category, chọn một trong những định dạng có sẵn trong Type, rồi tiếp đến phần Custom thay đổi định dạng mà bạn muốn.
Sau khi tạo định dạng ngày tháng trong Excel, bạn dùng những ký hiệu phù hợp sau đây.
Cần chú ý những ký hiệu sau khi tạo định dạng ngày tháng trong Excel.
Bên cạnh đó, khi bạn muốn tạo một định dạng ngày tháng trên Excel và sử dụng những ký hiệu như “m” sau “hh” hoặc “h” hoặc đứng trước “ss” hoặc “s” thì Excel sẽ hiển thị ký hiệu 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ị.
Xem thêm: Cách chuyển định dạng ngày tháng trong Excel (DATE) sang TEXT & NUMBER
Tạo định dạng ngày tháng trên Excel theo địa điểm khác nhau
Nếu như bạn là người thường xuyên đi công tác nước ngoài thì đây là một cách hữu hiệu để hiện thị đúng thời gian của nơi bạn đang làm việc 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, đu 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 người hàng xóm 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
Cách sửa lỗi định dạng ngày tháng của Excel không hoạt động và cách khắc phục
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ông khỏi 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 ô để đư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 trong Excel
Lỗi #### không chỉ hiện ra khi ô trong Excel không đủ rộng mà cả các phiên bản Excel từ 2010 đến 2017, ký hiệu #### cũng sẽ xuất hiện như một ô 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 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ừ C1 cho B1 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”)
Bên cạnh đó bạn có thể dùng bất kỳ định dạng ngày tháng năm trong Excel nào do bạn tạo ra từ TEXT thành căn lề phải.
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.
Thay đổi định dạng ngày tháng trong Excel
Đơn vị thời gian trong Excel sẽ được hiển thị theo nhiều cách khác nhau theo tùy chỉnh của người sử dụng. Và mỗi khi thay đổi định dạng trong một hay nhiều ô mà không lặp đi lặp lại thì bạn hãy mở hộp thoại Format Cells và chọn một trong những định dạng dưới đây.
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.
2. Bấm tổ hợp Ctrl + 1 để mở cửa sổ Format Cells.
3. Cửa sổ Format Cells hiện ra, ta chọn tab Number và Date ở bảng Categor y.
4. Ở phần Type, lựa chọn đình dạng ngày tháng năm mà bạn muốn. Sau đó trong ô Sample sẽ hiển thị ví dụ tương tự như dữ liệu ngày tháng năm bạn nhập vào ô.
.jpg)
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ố.
Chuyển đổi đị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.
- Ở hộp thoại Locale (Location) trong hộp thoại sẽ là nơi mà bạn chọn vị trí, sau đó nhấn Ok để lưa 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.
.jpg)
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 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.