Nếu bạn làm việc với các ngày trước năm 1900, Excel sẽ cung cấp ít trợ giúp trực tiếp. Tuy nhiên, một số công thức Excel và macro miễn phí có thể cung cấp nhiều trợ giúp mà bạn cần Show Qua Charley Kyd40501 liên kết  Nếu bạn làm việc với các ngày trước năm 1900, hệ thống xử lý ngày tiêu chuẩn của Excel sẽ không giúp được gì. Tuy nhiên, có một số cách xung quanh vấn đề này Excel lưu trữ các ngày gần đây dưới dạng số sê-ri ngày, cho phép chúng tôi sắp xếp các ngày đó và thực hiện phép tính ngày tháng. Thật không may, số sê-ri Excel bắt đầu vào ngày 1 tháng 1 năm 1900; (Ngày mặc định cho Macintosh là 1904. Để thay đổi ngày mặc định, hãy chọn Công cụ, Tùy chọn, chọn tab Tính toán, sau đó chọn hoặc bỏ chọn hộp kiểm hệ thống ngày 1904. ) Để minh họa hệ thống ngày tháng năm 1900, công thức này… Mặt khác, công thức này cho một ngày trước đó… Kết quả này là đáng tiếc, để nói rằng ít nhất. Bởi vì khi bạn làm việc với các ngày trước năm 1900, thông thường bạn sẽ muốn thực hiện hai điều giống như bạn làm với các ngày gần đây. Cụ thể, bạn muốn sắp xếp chúng và thực hiện số học ngày tháng May mắn thay, Excel cung cấp cho bạn sức mạnh để khắc phục hầu hết các vấn đề phổ biến với ngày sớm. Tuy nhiên, bạn cần ghi nhớ những vấn đề tiềm ẩn của những buổi hẹn hò sớm. Lịch Gregorian vs JulianThế giới ngày nay sử dụng một hệ thống hẹn hò khác so với cách đây hàng trăm năm. Hôm nay, chúng tôi sử dụng lịch Gregorian; Một biến thể có vẻ rất lạ ngày hôm nay. Ở Đế quốc Anh và các thuộc địa của nó, cho đến năm 1752, Lady Day (25 tháng 3) là ngày đầu tiên của năm mới Để làm cho vấn đề trở nên tồi tệ hơn, các khu vực khác nhau trên thế giới đã chuyển đổi từ Julian sang Gregorian vào những thời điểm khác nhau trong nhiều thế kỷ. Do đó, nhật ký của tàu có thể cho thấy rằng họ đã đến một cảng trước khi họ rời cảng trước đó Để minh họa những khác biệt khu vực ở Hoa Kỳ…
Lưu ý trong mọi trường hợp rằng một số ngày nhất định không bao giờ tồn tại cho ba vùng. Ví dụ, ở Biển Đông, những người thuộc địa chưa bao giờ trải qua khoảng thời gian từ ngày 3 tháng 9 đến ngày 13 tháng 9 năm 1752. Ngoài ra, năm 1752 của họ bắt đầu vào ngày 25 tháng 3 và kết thúc vào ngày 31 tháng 12 Từ góc độ Excel, như tôi sẽ trình bày, bạn có thể tạo ngày, chẳng hạn như ngày 10 tháng 9 năm 1752. Nhưng bạn sẽ cần diễn giải đúng ngày đó trong dữ liệu của mình Các vấn đề về ngày trong Excel điển hìnhTrước khi chúng tôi có thể giải quyết các vấn đề về ngày tháng của Excel, chúng tôi nên liệt kê các vấn đề phổ biến mà chúng tôi có thể gặp phải với chúng Để minh họa, giả sử chúng ta muốn làm việc với những ngày này trong một bảng tính 21 tháng 11 năm 20011 tháng 3 năm 1900 29 tháng 2 năm 1900 28 tháng 2 năm 1900 Jan 1, 19001 Sep 1841 Abt 1533 Circa 1492 Feb 29, 1200 Jan 1, 0100 Ở đây, chúng ta có ít nhất bốn vấn đề
Để làm phức tạp thêm vấn đề, nếu bạn nhập những ngày này vào bảng tính, Excel sẽ chỉ chuyển đổi những ngày gần đây thành số sê-ri ngày, cho bạn kết quả trong cột B Trong phạm vi B3. B7, Excel chuyển đổi ngày thành số sê-ri ngày. Nhưng những ngày khác vẫn ở dạng văn bản Thật là một mớ hỗn độn Khắc phục sự cố về ngày của ExcelCột E hiển thị một hướng hữu ích mà chúng ta có thể hướng tới với Excel. Trong cột đó, tất cả các ngày ban đầu đã được chuyển thành giá trị văn bản theo thứ tự năm-tháng-ngày. Điều này cho phép bạn sắp xếp chúng dễ dàng Cột F hiển thị một giải pháp liên quan. Ở đó, số sê-ri ngày âm kéo dài đến năm 0100. Vì mỗi số sê-ri ngày đại diện cho một ngày nên các số này cho phép bạn thực hiện các phép tính thời gian một cách dễ dàng Hãy xem hàng dữ liệu đầu tiên trong bảng này A3. ’21 tháng 11 năm 2001 Ở đây, tôi đã gõ một dấu nháy đơn (dấu nháy đơn) trước khi nhập văn bản ngày tháng. Điều này buộc Excel phải coi ngày này là văn bản chứ không phải là số B3. 21/11/2001 Khi tôi gõ văn bản vào ô A3, Excel sẽ tự động chuyển đổi ngày thành số trong bảng C3. =TEXT(B3,”dd mmm yyyy”) Công thức này chuyển đổi một số trong ô B3 thành văn bản ở định dạng ngày được hiển thị. Nếu ô B3 đã chứa văn bản, công thức chỉ trả về văn bản đó D3. =ISERROR(DATEVALUE(LEFT(C3,LEN(C3)-4)&”2000″)) Công thức này kiểm tra xem Excel có thể nhận ra các giá trị tháng và ngày trong cột C không. Nó làm như vậy bằng cách thay thế năm ban đầu bằng "2000", sau đó sử dụng hàm DATEVALUE để cố gắng tạo số sê-ri ngày. Nếu nỗ lực này không thành công, công thức trả về TRUE; E3. =RIGHT(C3,4)&”-“&IF(D3,”01-01″, E3. =DateText(B3) Tôi cung cấp hai phiên bản của công thức này. Phiên bản đầu tiên được chia thành hai dòng. Ở dòng đầu tiên, hàm RIGHT trả về năm. Chúng tôi có một dấu gạch ngang. Sau đó, nếu ô D3 cho biết chúng tôi có lỗi, công thức sẽ trả về “01-01”. (Theo quy ước, tôi chuyển đổi các năm bắt đầu bằng “Abt”, “Circa”, v.v. sang ngày 1 tháng 1 của năm được chỉ định. ) Nếu ô D3 trả về FALSE thì không có lỗi. Trong trường hợp đó, dòng thứ hai của công thức đầu tiên thay thế năm gốc bằng năm 2000; Phiên bản thứ hai của công thức trong ô E3 gọi một macro miễn phí mà tôi giới thiệu bên dưới. Cả hai phiên bản đều trả về cùng một kết quả cho cột E, được lặp lại tại đây F3. =DateTextValue(B3) Có thể tạo một công thức trong Excel trả về các giá trị được hiển thị trong cột F ở trên. Tuy nhiên, công thức sẽ rất phức tạp. Vì vậy, tôi đã sử dụng một macro đơn giản Để hoàn thành bảng, sao chép phạm vi C3. F3 xuống các cột như hình Lỗi cố ý với chức năng DATE của ExcelTrong Excel, ngày là số, được gọi là số sê-ri ngày, được định dạng theo cách đặc biệt. Trong bảng tính Excel, số sê-ri ngày 1 đại diện cho ngày 1 tháng 1 năm 1900. Nhưng trong VBA, số sê-ri ngày 1 đại diện cho một ngày trước đó. Ngày 31 tháng 12 năm 1899. Lotus 1-2-3 có thể bị đổ lỗi cho sự khác biệt một ngày này, bởi vì Lotus đã không xử lý các năm nhuận một cách chính xác Lịch của chúng tôi thêm ngày 29 vào tháng 2 trong tất cả các năm chia hết cho 4, ngoại trừ những năm kết thúc bằng 00. Những năm này chỉ nhận được thêm ngày nếu chúng chia hết cho 400. Do đó, 1600, 2000 và 2400 là năm nhuận nhưng 1700, 1800, 1900 và 2100 thì không Thật không may, các nhà thiết kế của Lotus 1-2-3 đã bỏ qua những sự thật này và đưa ngày 29 tháng 2 năm 1900 vào phiên bản của họ hàm DATE. Vì vậy, khi Microsoft giới thiệu Excel cho Windows 1. 0 vào năm 1987, các nhà thiết kế của Excel đã phải bắt chước một cách trung thực hiệu suất không chính xác của bảng tính dẫn đầu thị trường, Lotus 1-2-3. Do đó, hàm DATE của Excel cũng bao gồm ngày không tồn tại đó Mặt khác, VBA không có hạn chế như vậy khi nó được giới thiệu vào năm 1993. Nó có thể bỏ qua chính xác ngày nhuận không tồn tại vào năm 1900. Số sê-ri ngày của VBA bắt đầu sớm hơn một ngày để có thể đồng bộ hóa với số sê-ri ngày của Excel vào ngày 1 tháng 3 năm 1900 VBA cũng hỗ trợ số sê-ri ngày âm có thể theo dõi ngày trở lại ngày 1 tháng 1 năm 0100 Sử dụng Macro để khắc phục các sự cố về ngày tháng trong ExcelSử dụng liên kết này để tải xuống sổ làm việc VBA miễn phí tính toán các kết quả được hiển thị trong cột E và F ở trên. Bởi vì tôi đã bình luận rất nhiều về macro, hoạt động của nó sẽ rõ ràng nếu bạn quen thuộc với VBA Sổ làm việc cũng bao gồm một phiên bản làm việc của bảng được hiển thị ở trên và bao gồm một số bảng khác minh họa cách hai macro diễn giải nhiều loại dữ liệu phổ biến Để sử dụng sổ làm việc, hãy thêm các trang tính để chứa dữ liệu của bạn. Sau đó sử dụng các công thức như trong bảng. Bạn có thể xóa các trang tính mẫu nếu muốn Ngoài ra, bạn có thể thêm một Mô-đun vào sổ làm việc hiện có rồi sao chép mã VBA vào sổ làm việc đó. Sau đó, bạn có thể sử dụng hai chức năng VBA trong sổ làm việc đó
liên kết Bài viết trước Cách tạo bảng thanh toán đều và khấu hao theo đường thẳng trong Excel Bài viết tiếp theo Cách xóa các hàng trống hoặc không cần thiết, Phương pháp 2 Charley Kyd Khi mới vào nghề, tôi đã làm việc gần 20 năm với tư cách là Giám đốc tài chính của các công ty khởi nghiệp và quay vòng. Nhưng cuối cùng tôi đã kiệt sức khi phải vật lộn liên tục với dòng tiền. Đó là khi tôi bắt đầu viết về Lotus 1-2-3, phần mềm bảng tính mà tôi đã sử dụng hầu hết thời gian trong những ngày làm CFO. Khi Excel chuẩn bị được giới thiệu cho PC, một trong những biên tập viên tạp chí của tôi đã tổ chức một cuộc họp để tôi xem sản phẩm, nói chuyện với các nhà phát triển và viết một câu chuyện trang bìa về Excel. Vì vậy, tôi đã sử dụng phiên bản Excel đầu tiên trước khi nó được tung ra. Và tôi cũng đã sử dụng phiên bản đầu tiên của VisiCalc trước khi nó được ra mắt. Và sau đó, Tại sao Excel hiển thị 1900?Hệ thống ngày tháng năm 1900
. Nếu bạn chọn chuyển đổi dữ liệu đã dán, Excel sẽ điều chỉnh các giá trị cơ bản và ngày đã dán khớp với ngày bạn đã sao chép. This is the default date system in Excel for Windows, Excel 2016 for Mac, and Excel for Mac 2011. If you choose to convert the pasted data, Excel adjusts the underlying values, and the pasted dates match the dates that you copied.
Excel có nhận ra ngày trước năm 1900 không?Mặc dù Công thức ngày của Microsoft Excel chỉ có thể sử dụng các ngày được nhập từ 1/1/1900 đến 31/12/9999 , nhưng bạn có thể sử dụng một . |
