Excel tính tuổi từ hai ngày
Tìm sự khác biệt chính xác giữa hai ngày trong Excel có thể không phải là cách sử dụng phổ biến, nhưng nó có thể rất thú vị. Một ví dụ kinh điển là tính tuổi của ai đó. Nghe có vẻ không khó và có một chức năng tiện dụng trong Excel có thể cung cấp cho bạn độ tuổi chính xác trong ngày. Đọc để biết các mẹo về cách tính tuổi trong Excel Show
Mọi người có thể không phải lúc nào cũng đánh giá cao việc bạn có một bảng tính tuổi chính xác của họ, nhưng nó chắc chắn có những lợi ích của nó. Đây là cách để bắt đầu Mục lục Cách tính tuổi trong Excel1. Đây là một nhiệm vụ ba cột khá đơn giản. Để bắt đầu, hãy nhập ngày sinh của một người vào ô đầu tiên của bạn. Trong ví dụ của chúng tôi, chúng tôi đang sử dụng Tom Hanks và ngày sinh của anh ấy nằm trong ô A2 2. Bây giờ, hãy nhập ngày hôm nay vào ô bên cạnh ô đầu tiên của bạn. Sẽ dễ dàng hơn để giữ cả hai ngày cùng nhau nếu bạn đang theo dõi nhiều độ tuổi 3. Trong ô thứ ba, đối với chúng tôi, đó là C2, hãy nhập công thức sau. =DATEDIF(A2, B2, “y”). Hai mã định danh ô khá đơn giản và sử dụng chữ cái y làm chỉ báo thứ ba có nghĩa là bạn chỉ quan tâm đến số năm Bạn cũng có thể biết tuổi của một người mà không cần nhập ngày hôm nay vào ô thứ hai. Để thực hiện việc này, hãy thay đổi công thức của bạn thành =DATEDIF(A2,TODAY(),”y”). Excel sẽ làm phần còn lại như xác định ngày và tính tuổi Nếu bạn thực sự muốn biết cụ thể, bạn cũng có thể tính tuổi của một người vào một ngày cụ thể trong lịch sử. Chúng ta sẽ lấy ví dụ về lịch sử và tính toán tuổi chính xác của Tom Hanks khi bộ phim Castaway được phát hành. Ngày 7 tháng 12 năm 2000 Công thức sẽ trông như thế này. =DATEDIF(A2, NGÀY(2000,12,7), “y”). Lưu ý rằng định dạng ngày là năm, tháng, ngày Phép đo cuối cùng, cụ thể nhất mà bạn có thể thực hiện là tuổi của một người, bao gồm cả tháng và ngày. Công thức dài hơn một chút so với các phép đo trước đó, nhưng quy trình vẫn như cũ. Công thức của bạn sẽ trông như thế này. =DATEDIF(A2,B2, “y”) & “y” & DATEDIF(A2, B2, “ym”) & “m” & DATEDIF(A2,B2, “md”) & “d. ” Mặc dù nó trông giống như một câu cửa miệng, nhưng một khi bạn chia nhỏ nó ra, nó sẽ có ý nghĩa hơn. Bạn cần có hàm DATEDIF cho từng cấp độ đo lường, nghĩa là hàm đầu tiên chỉ năm. Chức năng thứ hai đo năm và tháng, nhưng “m” đảm bảo rằng nó chỉ hiển thị tháng. Chức năng cuối cùng đo lường sự khác biệt về tháng và ngày mà không có năm và hiển thị ngày với “d. ” Bây giờ bạn đã biết cách tính tuổi trong Excel. Xin vui lòng chỉ sử dụng sức mạnh của bạn cho tốt Có liên quan. Đây là cách hợp nhất các ô trong Excel Tôi có thể thử những gì khác?Bây giờ bạn đã thành thạo một trong các hàm Excel thích hợp hơn, bạn có thể làm gì khác? . Bạn cũng có thể viết macro tự động tính tuổi cho bạn. Thế giới Excel thực tế là vô tận nếu bạn có thời gian để thực hành và chúng tôi đang nêu bật một ưu đãi mới có thể giúp bạn làm điều đó Nó được gọi là Gói Excel hoàn chỉnh. Hộp công cụ khởi động và nó bao gồm tổng cộng 12 mô-đun. Bạn có thể dành thời gian thực hành hầu hết mọi thứ bạn muốn học, từ bảng tính lớn đến hàm logic và công thức điều kiện. Sự lựa chọn là của bạn và bạn có quyền tự do hoàn thành từng mô-đun theo tốc độ của riêng mình 12 mô-đun thực hành có giá trị bán lẻ kết hợp là 2.800 đô la, nhưng bạn có thể mua nó chỉ với 39 đô la ngay bây giờ trên Ưu đãi công nghệ. Đây là một bộ công cụ học tập tương đối mới, nhưng đã có hơn 400 người đăng ký. Bạn có thể tham gia cùng họ và tìm hiểu thêm thông qua tiện ích bên dưới Công thức đơn giản và chính xác nhất để tính tuổi trong Excel là =DATEDIF(birth_date,as_of_date,"y"). Điều này trả về số năm làm tròn xuống. Các phương pháp khác, chẳng hạn như =INT((end-start)/365. 25) hoặc =INT(YEARFRAC(start,end)) không đúng 100%. Xem bên dưới để biết các ví dụ và giải thích về các công thức khác liên quan đến tính tuổi trong Excel Để xem các ví dụ đang hoạt động, hãy tải xuống tệp Excel bên dưới Tải xuống tệp ví dụ (CalculateAge. xlsx) Bài viết này (dấu trang) Cú pháp cho DATEDIFDATEDIF(ngày_bắt đầu,ngày_kết thúc,khoảng thời gian) IntervalReturns"y"Số năm hoàn chỉnh giữa hai ngày"m"Số tháng hoàn chỉnh giữa hai ngày"d"Số ngày giữa hai ngày"ym"Số nguyên tháng còn lại (xuất phát từ "y" và "m")"Tính tuổi theo nămPhương pháp 1. Sử dụng DATEDIF để trả về số nguyên năm giữa hai ngàyĐây là phương pháp đơn giản và chính xác nhất. Mặc dù ngày tháng không thể sớm hơn năm 1900 nhưng chúng có thể là giá trị ngày tháng hoặc văn bản. DATEDIF tự động sử dụng DATEVALUE để chuyển đổi văn bản ngày hợp lệ thành giá trị ngày, vì vậy DATEDIF("17-Aug-1968","2017-08-17","y") sẽ hoạt động =DATEDIF(birth_date,as_of_date,"y") GHI CHÚ Nếu bạn muốn as_of_date luôn là ngày hôm nay, hãy sử dụng TODAY() thay cho as_of_date DATEDIF trả về 0 năm cho 29-Feb-2016 đến 28-Feb-2017 (đúng với hầu hết các diễn giải về cả năm) Lịch sinh nhật theo tuổiXem DATEDIF đang hoạt động. Mẫu này có thể được sử dụng để hiển thị ngày sinh của người thân của bạn trên lịch hàng năm và hàm DATEDIF tính toán tuổi của họ vào ngày sinh nhật của họ Phương pháp 2. Tính tuổi theo năm mà không cần sử dụng DATEDIFCông thức này là một công thức thay thế hợp lệ cho DATEDIF(bắt đầu,kết thúc,"y"). Nó trả về kết quả tương tự nếu đầu vào là ngày hợp lệ sau năm 1900 và kết thúc > bắt đầu. Làm thế nào nó hoạt động. Nó trừ các năm và sau đó trừ 1 nếu tháng và ngày của ngày kết thúc đến trước tháng và ngày của ngày bắt đầu =YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end) LƯU Ý Để buộc báo lỗi khi bắt đầu > kết thúc, hãy bọc hàm bằng =IF(start>end, NA(), original_formula). Trong Excel, giá trị số cho TRUE là 1. Đây là lý do tại sao chúng ta có thể trừ kết quả của (x > y). Phương pháp 3. Trả về số thập phân của năm giữa hai ngàySố ngày chính xác trong một năm là 365. 2422[1]. Sử dụng 365. 25 là một xấp xỉ. Để tính tuổi bằng phương pháp này, trước tiên hãy trừ các ngày để tính số ngày, sau đó chia cho 365. 2422 =(end_date-start_date)/365.2422 GHI CHÚ [1] "Số ngày chính xác trong một năm" là 365. 2422, theo https. // báo sư tử. gsfc. nasa. gov/files/04_21_97_1. pdf Một cách phổ biến khác để tính số thập phân của năm là sử dụng hàm YEARFRAC, tuy nhiên kết quả có thể không phải lúc nào cũng đúng (e. g. 31-Jul-2012 đến 30-Jul-2015) =YEARFRAC(start_date,end_date) thận trọng Sử dụng INT hoặc ROUNDDOWN với phương pháp này để trả về số năm nguyên giữa hai ngày đôi khi sẽ dẫn đến kết quả không chính xác (e. g. 28-Feb-2013 đến 28-Feb-2016). Mặc dù hiếm khi xảy ra lỗi (< 1% trong số các kết hợp ngày mà tôi đã kiểm tra), nhưng phương pháp này không phải là phương pháp thay thế hợp lệ cho DATEDIF(start,end,"y") Tính tuổi theo năm, tháng và ngàyĐộ tuổi thường được biểu thị bằng định dạng 5y 11m 3d hoặc 5 năm 11 tháng và 3 ngày. Có nhiều phương pháp để tính tuổi dưới dạng kết hợp của năm, tháng và ngày, nhưng không phải tất cả các phương pháp đều cho kết quả giống nhau. Sự phức tạp và khác biệt đến từ cách chúng ta xử lý các tháng với số ngày khác nhau trong đó Ví dụ: nếu bạn trừ một tháng từ ngày 31 tháng 3 năm 2017, thì tháng đó có phải là ngày 28 tháng 2 năm 2017 không? . Tuy nhiên, nếu chúng ta sử dụng phương thức DATE(year,month-1,day) thì kết quả sẽ là 3-Mar-2017. Nếu giả sử một tháng có 30 ngày, thì (end_date-30) sẽ cho kết quả là 1-Mar-2017. Vấn đề là có thể có nhiều câu trả lời đúng, tùy thuộc vào phương pháp Phương pháp 1. Sử dụng DATEDIF để tính tuổi theo năm, tháng và ngàyBước 1. Tính số năm hoàn thànhyears =DATEDIF(start_date, end_date, "y") Bước 2. Tính số tháng còn lại sau khi trừ đi số năm kể từ ngày kết thúcmonths =DATEDIF(start_date, end_date, "ym") Bước 3. Tính số ngày còn lại sau khi trừ tháng kể từ ngày kết thúcdays =DATE( YEAR(end), MONTH(end) - DATEDIF(start,end,"m"), DAY(end)) - start thận trọng Bạn có thể nghĩ rằng mình có thể sử dụng DATEDIF(start,end,"md") cho bước này, nhưng có một lỗi đã biết với tùy chọn "md" có thể dẫn đến các giá trị âm trong cả Excel và Google Trang tính (chẳng hạn như khi sử dụng 31 . Công thức khắc phục =end-DATE(YEAR(end),MONTH(end,1) được liệt kê trên trang web Hỗ trợ của Microsoft tại đây và tại đây là không chính xác (e. g. 31-Jan-2016 đến 31-Jan-2017 trả về giá trị 30 và nó phải là 0) Bước 4. Nối các kết quả từ các bước 1-3Công thức bên dưới trả về một chuỗi văn bản giống như 49y 11m 6d =years & "y " & months & "m " & days & "d" Nếu bạn muốn có một công thức duy nhất kết hợp các bước 1-4, bạn có thể kết hợp các công thức để có công thức lớn sau ________số 8_______GHI CHÚ Sau khi sử dụng phương pháp này để tính tuổi, bạn có thể sử dụng công thức DATE được đề cập trong phần tiếp theo để tính ngày sinh ban đầu từ tuổi và ngày mất. Thực tế này làm cho nó trở thành một phương pháp rất hữu ích vì quá trình này có thể đảo ngược (thông tin không bị mất) Phương pháp 2. Kỹ Thuật Tháng 30 NgàyPhương pháp Tháng 30 Ngày là một kỹ thuật tôi học được khi làm gia phả. Nó dựa trên quy trình trừ ngày và tuổi bằng tay bằng cách sử dụng kỹ thuật viết ngày và tuổi đầu tiên dưới dạng YYYYMMDD. Ở trường tiểu học, bạn đã học được rằng khi thực hiện phép trừ, bạn có thể mượn từ hàng chục hoặc hàng trăm khi cần. Ở đây, nếu chúng ta cần vay từ địa điểm MM, chúng ta trừ 1 từ MM và sau đó thêm 30 ngày vào DD. Nếu chúng ta cần mượn từ địa điểm YYYY, chúng ta trừ đi một từ địa điểm YYYY và thêm 12 vào MM Bước 1. Trừ ngày ở vị trí DD, mượn 30 ngày từ vị trí MM nếu cầndays =DAY(end)+30*(DAY(end)<DAY(start))-DAY(start) GHI CHÚ Làm thế nào nó hoạt động. Bắt đầu bằng ngày của ngày kết thúc, sau đó cộng 30 ngày nếu bạn cần mượn, sau đó trừ đi ngày của ngày bắt đầu. Excel có thể cố gắng chuyển đổi kết quả thành một ngày, vì vậy hãy thay đổi định dạng số trở lại General Bước 2. Trừ tháng ở vị trí MM, mượn 12 tháng ở vị trí YY nếu cần=YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)0 GHI CHÚ Công thức này là một công thức thay thế hợp lệ cho DATEDIF(bắt đầu,kết thúc,"ym"). Làm thế nào nó hoạt động. Bắt đầu bằng tháng của ngày kết thúc, sau đó trừ 1 nếu bạn phải mượn 30 ngày, sau đó cộng 12 tháng nếu bạn cần mượn năm, sau đó trừ tháng của ngày bắt đầu Bước 3. Trừ các năm ở vị trí YYYY=YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)1 GHI CHÚ Công thức này là một công thức thay thế hợp lệ cho DATEDIF(bắt đầu,kết thúc,"y"). Làm thế nào nó hoạt động. Bắt đầu bằng năm của ngày kết thúc, sau đó trừ 1 nếu trước đó bạn phải vay 12 tháng, sau đó trừ năm của ngày bắt đầu Phương pháp 2 dẫn đến độ tuổi khác với Phương pháp 1 khoảng 32% thời gian, nhưng tối đa chỉ 2 ngày. Hóa ra cả hai phương pháp đều trả về cùng một số năm và số tháng. Chỉ có số ngày có thể khác Tính ngày sinh nếu bạn biết ngày chết và tuổiSử dụng DATE để trừ tổ hợp năm, tháng và ngày từ một ngày. Câu trả lời có đúng hay không phụ thuộc vào phương pháp được sử dụng để tính tuổi =YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)2 GHI CHÚ Nếu bạn sử dụng Phương pháp 1 ở trên để tính tuổi theo năm, tháng và ngày, công thức này sẽ trả về ngày bắt đầu ban đầu 100% thời gian Tính ngày chết nếu bạn biết ngày sinh và tuổiSử dụng DATE để thêm tổ hợp năm, tháng và ngày vào một ngày. Câu trả lời có đúng hay không phụ thuộc vào phương pháp được sử dụng để tính tuổi =YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)3 GHI CHÚ Nếu bạn sử dụng Phương pháp 1 ở trên để tính tuổi, công thức này sẽ trả về ngày kết thúc ban đầu khoảng 75% thời gian (có thể trễ tới 3 ngày) Hóa ra là nếu bạn đồng ý với tùy chọn DATEDIF "md" dẫn đến giá trị âm trong nhiều ngày và sử dụng DATEDIF(bắt đầu,kết thúc,"md") thay cho Bước #3, thì công thức này sẽ trả về ngày kết thúc ban đầu là 100 Tính tuổi trong Excel cho các ngày trước năm 1900Các hàm Ngày trong Excel chỉ hoạt động cho các ngày sau ngày 1/1/1900, vì số sê-ri ngày trong Excel bắt đầu bằng 1 vào ngày 1/1/1900. Nếu bạn nhập một ngày chẳng hạn như 28-Aug-2017 trong Excel, nó sẽ lưu nó dưới dạng số 42975, nhưng nếu bạn nhập một ngày trước năm 1900, nó sẽ lưu ngày dưới dạng văn bản. Dưới đây là một số phương pháp tính tuổi trong Excel khi làm việc với các ngày trước năm 1900 Phương pháp 1. Thêm 2000 năm sau đó sử dụng các công thức trênVí dụ: sử dụng công thức dưới đây để tính tuổi khi ngày sinh là 28-Aug-1803 và ngày mất là 28-Aug-1850 =YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)4 GHI CHÚ Thêm 2000 năm cho phép công thức xử lý năm nhuận một cách chính xác. Ngoài ra, thật dễ dàng để cộng và trừ 2000 năm trong đầu bạn Phương pháp 2. Tạo Hàm do người dùng xác định dựa trên hàm ngày VBACác hàm ngày VBA có thể xử lý tất cả các ngày Gregorian hợp lệ. Xem bài viết "Cách tạo các hàm Excel tùy chỉnh" để biết mã VBA có thể tính tuổi cho các ngày trước năm 1900 Phương pháp 3. Tính tuổi bằng phương pháp Tháng 30 ngày và giá trị ngày YYYYMMDDNếu bạn nhập ngày ở dạng giá trị số theo định dạng YYYYMMDD, thì bạn có thể sử dụng phương pháp tháng 30 ngày (còn được gọi là kỹ thuật 8870) để trừ ngày. Sau khi trừ ngày bắt đầu từ ngày kết thúc, nếu DD > 31 thì trừ 70 và nếu MM > 12 thì trừ 8800. Kết quả sẽ là tuổi ở định dạng YYMMDD. Sau đó, bạn có thể sử dụng định dạng số tùy chỉnh là 0"y "00"m "00"d" để hiển thị tuổi =YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)5 GHI CHÚ Để chuyển đổi giá trị ngày (>1900) thành giá trị YYYYMMDD, hãy sử dụng =VALUE(YEAR(date) & TEXT(MONTH(date),"00") & TEXT(DAY(date),"00")) Bạn có thể sử dụng định dạng số tùy chỉnh sau cho giá trị tuổi YYMMDD để chỉ hiển thị năm và tháng nếu chúng khác không. [>9999]0"y "00"m "00"d";[>99]0"m "00"d";0"d" Đối với độ tuổi được lưu trữ dưới dạng giá trị YYMMDD, hãy sử dụng Lỗi DATEDIF "md" và cách khắc phụcNhư tôi đã đề cập ở trên, việc sử dụng đơn vị "md" trong hàm DATEDIF không phải lúc nào cũng đúng (cả trong Excel và Google Trang tính). Công thức khắc phục do Microsoft đề xuất trên trang hỗ trợ của họ thậm chí còn sai nhiều hơn. Có hai phương pháp để tính số ngày còn lại, được hiển thị bên dưới. Phương pháp #1 là phương pháp mà tôi nghĩ rằng DATEDIF sử dụng, nhưng nó có thể dẫn đến các giá trị âm rõ ràng là sai. Phương pháp #2 là phương pháp hợp lệ không bao giờ dẫn đến giá trị âm và phù hợp với định nghĩa "số ngày còn lại giữa các ngày sau khi trừ đi cả tháng. " Phương pháp 1. Trả về số ngày còn lại sau khi Thêm cả tháng vào Ngày bắt đầuCông thức sau đây khớp với kết quả của DATEDIF(start,end,"md") 100% thời gian (dựa trên việc lấy mẫu ngày ngẫu nhiên rộng rãi), vì vậy tôi khá chắc chắn rằng đây là cách DATEDIF đang thực hiện phép tính =YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)6 Tại sao DATEDIF trả về -1 hoặc -2? . Khi thêm một tháng vào ngày 31 tháng 1 năm 2015, nếu chúng tôi sử dụng EDATE("31-Jan-2015",1), chúng tôi sẽ nhận được ngày 28 tháng 2 năm 2016, đó là những gì bạn có thể mong đợi, nhưng DATEDIF không sử dụng EDATE. Nó sử dụng DATE(2015,1+1,31) trả về ngày 3-Mar-2015. Nếu ngày kết thúc là ngày 1 tháng 3 năm 2015, kết quả sẽ là -2 Phương pháp 2. Trả lại số ngày còn lại sau khi trừ cả tháng từ Ngày kết thúcCông thức sau đây là cách giải quyết chính xác để sử dụng thay vì tùy chọn "md" =YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)7 Lưu ý cuối cùngTôi cố tình bắt đầu bài viết này với câu trả lời đơn giản là sử dụng DATEDIF(start,end,"y") để tính tuổi - đây có lẽ là điều duy nhất mà 99% mọi người cần biết. Tuy nhiên, tôi cũng muốn nghiên cứu sâu để giải quyết một số nội dung khó hiểu của các phương pháp khác. Bảng tính tôi thiết lập để so sánh các phương pháp khác nhau này đã phân tích 100 trong số hàng nghìn kết hợp ngày. Đó là cách tôi đưa ra các ví dụ trong đó một số phương pháp này tạo ra lỗi và cách tôi kiểm tra để đảm bảo rằng các công thức thay thế là (hoặc không) đúng 100%. YEARFRAC và phép chia cho 365. 2422 phương pháp dẫn đến lỗi nhỏ hơn 0. 2% thời gian, nhưng đó vẫn có thể là một vấn đề nếu bạn đang mong đợi độ chính xác 100% Nếu bạn có bất kỳ câu hỏi nào liên quan đến cách tính tuổi hoặc các công thức cụ thể này hoặc thấy bất kỳ lỗi chính tả nào, bạn có thể bình luận bên dưới hoặc gửi email cho tôi Người giới thiệu
Bình luận14 nhận xét…
Để lại bình luận Tên của bạn sẽ được hiển thị cùng với bình luận của bạn. Địa chỉ Email và IP của bạn được lưu trữ cùng với nhận xét và được sử dụng để xác định/ngăn chặn thư rác (thông qua dịch vụ do Automattic cung cấp. com), nhưng không được chia sẻ công khai. Xem chính sách bảo mật của chúng tôi để tìm hiểu thêm Công thức tính tuổi từ hai ngày trong Excel là gì?=(NĂM(BÂY GIỜ())-NĂM(A2))
. Ví dụ này sử dụng hàm YEAR và NOW. Nếu ô này không hiển thị một số, đảm bảo rằng nó được định dạng là một số hoặc Chung. Tìm hiểu cách định dạng ô dưới dạng số hoặc ngày. |