Máy tính ngày trong công thức Excel tải xuống

Trong ví dụ này, mục tiêu là xuất thời gian giữa ngày bắt đầu và ngày kết thúc dưới dạng chuỗi văn bản liệt kê năm, tháng và ngày riêng biệt. Ví dụ: với ngày bắt đầu là 1-Jan-2018 và ngày kết thúc là 1-Jul-2018, kết quả sẽ là một chuỗi như thế này

"1 years, 6 months, 0 days"

giải pháp DATEDIF

Hàm DATEDIF được thiết kế để tính toán sự khác biệt giữa các ngày trong năm, tháng và ngày. Có một số biến thể có sẵn (e. g. thời gian trong tháng, thời gian trong tháng bỏ qua ngày và năm, v.v. ) và chúng được đặt bởi đối số "đơn vị" trong hàm. Xem trang này trên hàm DATEDIF để biết danh sách đầy đủ các đơn vị có sẵn

Trong ví dụ được hiển thị, chúng tôi tính riêng năm, tháng và ngày, sau đó "dán" các kết quả lại với nhau bằng phép nối. Để có được cả năm, cả tháng và ngày giữa các ngày, chúng tôi sử dụng DATEDIF như thế này, chỉ thay đổi đối số đơn vị

DATEDIF(B5,C5,"y") // years
DATEDIF(B5,C5,"ym") // months
DATEDIF(B5,C5,"md") // days

Vì chúng tôi muốn tạo một chuỗi nối các đơn vị với mỗi số, nên chúng tôi nối số được trả về bởi DATEDIF với tên đơn vị bằng toán tử dấu và (&) như thế này

DATEDIF(B5,C5,"y")&" years" // years string
DATEDIF(B5,C5,"ym")&" months" // months string
DATEDIF(B5,C5,"md")&" days" // days string

Cuối cùng, chúng ta cần mở rộng ý tưởng trên để bao gồm khoảng trắng và dấu phẩy và nối mọi thứ lại với nhau thành một chuỗi

=DATEDIF(B5,C5,"y")&" years, "&DATEDIF(B5,C5,"ym")&" months, " &DATEDIF(B5,C5,"md")&" days"

Thực hiện chức năng LET

Hàm LET (mới trong Excel 365) có thể đơn giản hóa một số công thức bằng cách cho phép xác định và sử dụng lại các biến. Để sử dụng hàm LET trên công thức này, chúng ta cần suy nghĩ về các biến. Mục đích chính của các biến là xác định một tên hữu ích có thể được sử dụng lại ở nơi khác trong mã công thức. Nhìn vào công thức, có ít nhất năm cơ hội để khai báo biến. Hai ngày đầu tiên là ngày bắt đầu và ngày kết thúc, được sử dụng lại trong suốt công thức. Khi chúng ta gán giá trị cho bắt đầu và kết thúc, chúng ta chỉ cần tham chiếu đến ô C5 và D5 một lần. Ở bước đầu tiên, chúng ta có thể thêm dấu ngắt dòng, sau đó xác định các biến cho bắt đầu và kết thúc như thế này

=LET(
  start,B5,
  end,C5,
  DATEDIF(start,end,"y")&" years, "&
  DATEDIF(start,end,"ym")&" months, "&
  DATEDIF(start,end,"md")&" days"
)

Lưu ý rằng tất cả các phiên bản của B5 và C5 trong DATEDIF đã được thay thế bằng bắt đầu và kết thúc. Kết quả từ công thức này giống như công thức ban đầu ở trên, nhưng tham chiếu đến B5 và C5 chỉ xảy ra một lần. Sau đó, ngày bắt đầu và ngày kết thúc được sử dụng lại trong toàn bộ công thức. Điều này giúp đọc công thức dễ dàng hơn và giúp giảm lỗi

Ba cơ hội tiếp theo cho các biến là kết quả từ DATEDIF cho năm, tháng và ngày. Nếu gán các giá trị này cho các biến đã đặt tên, thì sau này chúng ta có thể kết hợp chúng dễ dàng hơn theo nhiều cách khác nhau, điều này sẽ trở nên hữu ích hơn trong phiên bản mở rộng của công thức được giải thích bên dưới. Đây là công thức được cập nhật để bao gồm các biến mới cho năm, tháng và ngày

=LET(
  start,B5,
  end,C5,
  years,DATEDIF(start,end,"y"),
  months,DATEDIF(start,end,"ym"),
  days,DATEDIF(start,end,"md"),
  years&" years,"&months &" months," &days &" days"
)

Lưu ý rằng chúng tôi đã gán kết quả từ DATEDIF cho các biến năm, tháng và ngày và các giá trị này được nối với nhau ở dòng cuối cùng bên trong hàm LET. Với hàm LET, đối số cuối cùng là phép tính hoặc giá trị trả về kết quả cuối cùng

Bắt mắt với LET

Khi chúng tôi có phiên bản LET cơ bản hoạt động, chúng tôi có thể dễ dàng mở rộng công thức để thực hiện xử lý phức tạp hơn mà không cần chạy lại các phép tính tương tự. Ví dụ: một điều chúng tôi có thể muốn làm là biến các đơn vị thành số nhiều hoặc số ít tùy thuộc vào số đơn vị thực tế. Công thức dưới đây thêm ba biến mới. ys, ms và ds để giữ các chuỗi được liên kết với từng đơn vị

=LET(
  start,B5,
  end,C5,
  years,DATEDIF(start,end,"y"),
  months,DATEDIF(start,end,"ym"),
  days,DATEDIF(start,end,"md"),
  ys,years&" year"&IF(years<>1,"s",""),
  ms,months&" month"&IF(months<>1,"s",""),
  ds,days&" day"&IF(days<>1,"s",""),
  ys&", "&ms&", "&ds
)

Các chuỗi bắt đầu số ít (i. e. "năm" không phải "năm"). Sau đó, chúng tôi sử dụng hàm IF để xử lý một cách có điều kiện chỉ khi số đó không phải là 1. Nếu số là 1, IF trả về một chuỗi rỗng ("") và tên đơn vị vẫn là số ít. Bởi vì chúng tôi thêm tên đơn vị vào số đơn vị khi chúng tôi xác định ys, ms và ds, bước cuối cùng bên trong LET đơn giản hơn;

Xem thêm chi tiết về hàm LET tại đây. Ngoài ra, hãy xem ví dụ hàm LET chi tiết để biết cách giải thích khác về việc chuyển đổi một công thức hiện có sang sử dụng LET

Công thức tính ngày trong Excel là gì?

Công thức tính ngày là gì?