Thực đơn
trang chủExcel

Các công thức làm việc với ngày tháng trong Excel. Cách tìm và đánh dấu giá trị và định dạng ngày không chính xác trong Excel

Hàm DATE() , phiên bản tiếng Anh NGÀY(), trong Trả về một số nguyên biểu thị một ngày cụ thể. Công thức =DATE(2011;02;28) sẽ trả về số 40602. Nếu định dạng ô được đặt thành Chung trước khi nhập công thức này, kết quả sẽ được định dạng dưới dạng ngày, tức là. 28/02/2011.

Cú pháp hàm

NGÀY(năm;tháng;ngày)

Năm- một đối số có thể có từ một đến bốn chữ số.
Tháng là số nguyên dương hoặc âm nằm trong khoảng từ 1 (tháng 1) đến 12 (tháng 12) đại diện cho tháng trong năm.
Ngày là số nguyên dương hoặc âm nằm trong khoảng từ 1 đến 31 đại diện cho ngày trong tháng.

TRONG ngày EXCELđược lưu trữ dưới dạng một chuỗi số (1, 2, 3, ...), cho phép bạn thực hiện các phép tính trên chúng. Theo mặc định, ngày 1 tháng 1 năm 1900 được đánh số 1 và ngày 28 tháng 2 năm 2011 được đánh số 40602, vì khoảng cách giữa các ngày này là 40.602 ngày. Để tìm hiểu cách EXCEL lưu trữ ngày và giờ, hãy đọc phần này.

Ví dụ

Để cộng thêm ngày 28/02/2011 có trong ô A1 , ví dụ: 5 năm, bạn có thể sử dụng công thức sau:
=NGÀY(NĂM(A1)+5,THÁNG(A1),NGÀY(A1))
chúng tôi nhận được kết quả vào ngày 28/02/2016

Ví dụ: để cộng 15 tháng vào ngày 28/02/2011, bạn có thể sử dụng công thức sau:
=DATE(YEAR(A1),THÁNG(A1)+15,DAY(A1)) hoặc công thức =DATEMES(A1,15)
chúng tôi nhận được kết quả vào ngày 28/05/2012

Ghi chú. Có sự khác biệt giữa DATEMES() và DATE() khi thêm tháng. Hãy cộng thêm một tháng vào ngày 30/01/2009:

  • =DATEMES("30/01/2009";1) sẽ trở lại vào ngày 28/02/2009, bởi vì Ngày 30 tháng 2 không tồn tại thì hàm sẽ trả về ngày cuối cùng của tháng, tức là. 28/02/2009 (“thêm” 2 ngày sẽ bị loại bỏ);
  • =NGÀY(NĂM("30/01/2009");THÁNG("30/01/2009")+1;NGÀY("30/01/2009")) sẽ trở lại 02/03/2009: “thêm” 2 ngày (29 và 30 tháng 2) sẽ được cộng vào ngày.

Nếu bạn viết công thức =DATE(2008;3;), thì công thức sẽ trả về 29/02/2008. Những thứ kia. Bằng cách bỏ qua đối số cuối cùng Day, chúng ta đặt nó bằng 0. Do đó, công thức trả về

Một trong tính năng thú vị Microsoft Excel là HÔM NAY. Toán tử này được sử dụng để nhập ngày hiện tại vào một ô. Nhưng nó cũng có thể được sử dụng kết hợp với các công thức khác. Hãy xem xét các tính năng chính của chức năng HÔM NAY, các sắc thái trong công việc của nó và sự tương tác với các toán tử khác.

Chức năng HÔM NAY xuất ngày được đặt trên máy tính tới ô được chỉ định. Cô thuộc nhóm người vận hành "Ngày và giờ".

Nhưng bạn cần phải hiểu điều đó một mình công thức này sẽ không cập nhật các giá trị trong ô. Nghĩa là, nếu bạn mở chương trình một vài ngày sau đó và không tính toán lại các công thức trong đó (thủ công hoặc tự động), thì ngày tương tự sẽ được đặt trong ô chứ không phải ngày hiện tại.

Để kiểm tra xem tính năng tính toán lại tự động có được cài đặt trong một tài liệu cụ thể hay không, bạn cần thực hiện một loạt hành động tuần tự.




Bây giờ, mọi thay đổi trong tài liệu sẽ tự động tính toán lại.

Nếu vì lý do nào đó bạn không muốn cài đặt tính năng tính toán lại tự động thì để cập nhật trên ngay hiện tại nội dung của ô chứa hàm HÔM NAY, bạn cần chọn nó, đặt con trỏ vào thanh công thức và nhấn nút Đi vào.


Trong trường hợp này, khi tính năng tính toán lại tự động bị tắt, nó sẽ chỉ được thực hiện cho ô này chứ không phải cho toàn bộ tài liệu.

Cách 1: Nhập hàm thủ công

Toán tử này không có đối số. Cú pháp của nó khá đơn giản và trông như thế này:

HÔM NAY()




Phương pháp 2: Sử dụng Trình hướng dẫn hàm

Ngoài ra, bạn có thể sử dụng Trình hướng dẫn chức năng. Tùy chọn này đặc biệt phù hợp cho người mới bắt đầu Người dùng Excel, vẫn còn bị nhầm lẫn về tên hàm và cú pháp của chúng, mặc dù trong trong trường hợp này nó càng đơn giản càng tốt.




Cách 3: Thay đổi định dạng ô

Nếu trước khi vào hàm HÔM NAY Nếu ô có định dạng chung, nó sẽ tự động được định dạng lại thành định dạng ngày. Tuy nhiên, nếu phạm vi đã được định dạng cho một giá trị khác thì nó sẽ không thay đổi, điều đó có nghĩa là công thức sẽ tạo ra kết quả không chính xác.

Để xem giá trị định dạng của một ô hoặc vùng riêng lẻ trên một trang tính, bạn cần chọn phạm vi mong muốn và trong tab “Trang chủ”, hãy xem giá trị nào được đặt trong hình thức đặc biệtđịnh dạng trên ribbon trong hộp công cụ "Con số".


Nếu sau khi nhập công thức HÔM NAYĐịnh dạng ô không được đặt tự động "Ngày của", khi đó hàm sẽ hiển thị kết quả không chính xác. Trong trường hợp này, bạn phải thay đổi định dạng theo cách thủ công.




Ngoài ra, trong cửa sổ định dạng, bạn cũng có thể thay đổi cách trình bày ngày hôm nay. Định dạng mặc định là mẫu "dd.mm.yyyy". Làm nổi bật Các tùy chọn khác nhau các giá trị trong trường "Kiểu", nằm ở bên phải cửa sổ định dạng, có thể được thay đổi vẻ bề ngoài hiển thị ngày tháng trong một ô. Sau khi thực hiện thay đổi, đừng quên nhấn vào nút "ĐƯỢC RỒI".


Cách 4: dùng TODAY kết hợp với các công thức khác

Ngoài ra, chức năng HÔM NAY có thể được sử dụng như một phần của công thức phức tạp. TRONG Trong khả năng này toán tử này cho phép bạn giải quyết các vấn đề rộng hơn nhiều so với khi sử dụng độc lập.

Nhà điều hành HÔM NAY Nó rất thuận tiện để sử dụng để tính toán các khoảng thời gian, chẳng hạn như khi chỉ ra tuổi của một người. Để thực hiện việc này, hãy viết biểu thức thuộc loại này vào ô:

NĂM(HÔM NAY())-1965

Để áp dụng công thức nhấn vào nút ĐI VÀO.


Bây giờ trong phòng giam tại cài đặt đúng tính toán lại công thức văn bản sẽ liên tục hiển thị tuổi hiện tại của người sinh năm 1965. Một biểu thức tương tự có thể được sử dụng cho bất kỳ năm sinh nào khác hoặc để tính ngày kỷ niệm của một sự kiện.

Ngoài ra còn có một công thức hiển thị các giá trị trong một ô trước vài ngày. Ví dụ: để hiển thị ngày trong ba ngày kể từ bây giờ, nó sẽ trông như thế này:

HÔM NAY()+3


Nếu bạn cần liên tục theo dõi một ngày cách đây ba ngày, thì công thức sẽ như sau:

HÔM NAY()-3


Nếu bạn chỉ cần hiển thị trong một ô số của ngày hiện tại trong tháng chứ không phải toàn bộ ngày thì biểu thức sau sẽ được sử dụng:

NGÀY QUA NGÀY())


Thao tác tương tự để hiển thị số Tháng này sẽ trông như thế này:

THÁNG(HÔM NAY())


Nếu tế bào chứa sai định dạng giá trị - điều này có thể dẫn đến tính toán sai trong công thức. Ví dụ: thay vì loại giá trị "Ngày", ô chứa loại giá trị "Văn bản". Khi chuẩn bị lượng lớn dữ liệu, bạn nên kiểm tra tất cả các loại giá trị để đảm bảo chúng tuân thủ một định dạng cụ thể. Ví dụ: bảng chứa đầy dữ liệu từ nguồn khác nhauở đâu trong cách khác ngày đã được ghi lại. Bạn không thể thực hiện nhiều phép tính khác nhau với một bảng như vậy. Về mặt trực quan, rất khó để nhận biết mục nhập được nhập sai định dạng ở đâu. nhầm cuộc hẹn từ quan điểm của Excel.

Tìm định dạng văn bản thay vì ngày trong Excel

Để tìm nhanh các giá trị sai trong Excel và đánh dấu tất cả các ô có định dạng sai chúng ta sẽ sử dụng định dạng có điều kiện. Ví dụ: hãy lấy một bảng đơn giản:

Tìm và đánh dấu các ô có định dạng hiển thị giá trị không chính xác:



Như bạn có thể thấy trong hình, tất cả các ngày ở định dạng văn bản đều được đánh dấu bằng màu:

Về mặt định dạng, chúng tôi đã sử dụng chức năng đơn giản=ETEXT(), chỉ có 1 đối số - một liên kết đến ô đang được kiểm tra. Địa chỉ của liên kết trong đối số của hàm ITEXT phải mang tính tương đối vì mỗi ô trong phạm vi đã chọn sẽ được kiểm tra. Nếu ô hiện tại đang được kiểm tra có chứa văn bản (không phải ngày tháng) thì điều này không chính xác Giá trị Excel. Khi đó hàm ITEXT trả về TRUE và được gán ngay cho ô đó. Hình thức mới(tô màu xanh lá cây). Tên hàm ITEXT nên đọc là viết tắt của hai từ: If TEXT

Chúng ta đọc các hàm logic có thể được sử dụng để kiểm tra các định dạng và kiểu dữ liệu khác trong ô theo cách tương tự:

  • ENETEXT – nếu không phải là văn bản (chức năng này cũng cho phép bạn tìm nhanh ngày tháng trong văn bản Excel);
  • ISNUMBER – nếu là số (cho phép bạn nhanh chóng tìm thấy các định dạng số không chính xác trong Excel);
  • EOS – nếu có lỗi;
  • LỖI – nếu có lỗi;
  • IFERROR – nếu có lỗi (không phải hàm logic, nhưng rất dễ tối ưu hóa cho nhiệm vụ này);
  • EMPTY – nếu trống;
  • ELOGIC – nếu là giá trị logic;
  • UND – nếu giá trị không có sẵn (#N/A);
  • KHÔNG CHỈ CÓ – nếu giá trị là số lẻ;
  • NGAY LẬP TỨC – nếu giá trị là số chẵn;
  • LIÊN KẾT – nếu là một liên kết;
  • CÔNG THỨC – nếu công thức.

Nếu muốn, bạn có thể kiểm tra hoạt động của tất cả các chức năng bằng thực nghiệm.