Cách trừ ( giá trị: Jul 14 2020 để tính ra số ngày ?) (1 người xem)

[phamvandunghp84]

Cách trừ để tính ra số ngày: Mình có hai ô có hai giá trị như sau: mình muốn tính ra số ngày thì phải làm như nào mong được các bạn giúp Jul 14 2020 7/15/2020 17:55​

 

[phulien1902]

Cách trừ để tính ra số ngày: Mình có hai ô có hai giá trị như sau: mình muốn tính ra số ngày thì phải làm như nào mong được các bạn giúp Jul 14 2020 7/15/2020 17:55​

Bạn đưa vài dòng dữ liệu lên đây.

 

[VetMini]

"số ngày" tức là sao? Ngày B cách ngày A một đoạn B-A ngày, nhưng số ngày từ A đến B là B-A+1 ngày.

Nếu dữ liệu là date:
=DATEDIF(ô 1, ô 2, "D"), hoặc
=TRUNC(ô 2) - TRUNC(ô 1)

Nếu dữ liệu là chuỗi:
=DATEDIF(DATEVALUE(ô 1), DATEVALUE(ô 2), "D")

 

[phamvandunghp84]

Bạn đưa vài dòng dữ liệu lên đây.

ô A1 có giá trị là: Jul 14 2020 ô A2 có giá trị: 7/16/2020 15:22 Mình muốn công thức nào đó có thể tính được số ra khoảng cách số ngày ( ví dụ A2-A1 = 2 ngày ). Hiện tại mình dùng công thức A2-A1 thì bị lỗi do định dạng ngày tháng khác nhau. Mình muốn hỏi cách nào để khắc phục?

Bài đã được tự động gộp: 16/7/20

"số ngày" tức là sao? Ngày B cách ngày A một đoạn B-A ngày, nhưng số ngày từ A đến B là B-A+1 ngày.

Nếu dữ liệu là date:
=DATEDIF(ô 1, ô 2, "D"), hoặc
=TRUNC(ô 2) - TRUNC(ô 1)

Nếu dữ liệu là chuỗi:
=DATEDIF(DATEVALUE(ô 1), DATEVALUE(ô 2), "D")

Bạn xem giúp, mình đã thử cả 3 công thức trên của bạn nhưng vẫn bị lỗi. ( Chắc do ô A2 của mình có định dạng khác thường ?) mình không biết phải khắc phục như nào? mong bạn chỉ giúp !

 

[Nhattanktnn]

ô A1 có giá trị là: Jul 14 2020 ô A2 có giá trị: 7/16/2020 15:22 Mình muốn công thức nào đó có thể tính được số ra khoảng cách số ngày ( ví dụ A2-A1 = 2 ngày ). Hiện tại mình dùng công thức A2-A1 thì bị lỗi do định dạng ngày tháng khác nhau. Mình muốn hỏi cách nào để khắc phục?

Đưa file lên có phải nhanh không, ở trên cũng có bác bảo đưa file lên mà bạn cứ nói không vậy sao biết mà làm

 

[phamvandunghp84]

Đưa file lên có phải nhanh không, ở trên cũng có bác bảo đưa file lên mà bạn cứ nói không vậy sao biết mà làm

đây mình gửi file mong bạn giúp

 

[Nhattanktnn]

đây mình gửi file mong bạn giúp

Thử:

C2=INT(B2-DATE(RIGHT(A2,4),MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0),MID(A2,5,2)))

 

[phamvandunghp84]

Thử:

C2=INT(B2-DATE(RIGHT(A2,4),MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0),MID(A2,5,2)))

Tuyệt quá, nó đã chạy. Cảm ơn bạn !
Công thức này thật hay. bạn có thể giải thích ý nghĩa của nó cho mình hiểu với được không ?

 

[Quang_Hải]

đây mình gửi file mong bạn giúp

Thêm một lựa chọn

 

[Nhattanktnn]

Tuyệt quá, nó đã chạy. Cảm ơn bạn !
Công thức này thật hay. bạn có thể giải thích ý nghĩa của nó cho mình hiểu với được không ?

Để hiểu thì trước hết phải biết ô A2 không phải là định dạng ngày mà là dạng chuỗi (văn bản kiểu như "Nguyễn Văn A"). Nếu nó định dạng ngày tháng giống B2 thì rất đơn giản lấy B2-A2.
Giờ nó không phải ngày tháng thì quy về ngày tháng bằng cách dùng hàm Date (cú pháp DATE(year, month, day) )
Cụ thể trong công thức trên: (Dòng này quy A2 về ngày tháng năm)
DATE(RIGHT(A2,4),MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0),MID(A2,5,2))
Trong đó:
1/ Đỏ là năm (hàm right lấy 4 số cuối) : trả về 2020
2/ Vàng là tháng (phức tạp hơn vì phải tìm chữ "Jul" là tháng mấy) Trong này dùng hàm match (cú pháp google), tìm chữ "Jul" tức left(A2,3) trong đống 12 tháng đó, trùng nó sẽ trả về vị trí "Jul" là vị trí thứ 7 tính từ "jan","Feb"...(tương ứng 1,2,...,12)
3/ Xanh là ngày, dùng hàm mid lấy từ ký tự thứ 5, cần lấy 2 ký tự tính từ ký tự thứ 5 (tức là ký tự thứ 5 và ký tự thứ 6): Trả về 14

Xử lý xong, hàm Date trả về ngày 14/07/2020

Khi đó sẽ lấy B2 trừ đi hàm date đó, tuy nhiên B2 có giờ, còn hàm date thì giờ theo mặc định (0h00p) nên nó sẽ trừ ra bị lẻ (bỏ hàm Int đi bạn sẽ thấy). Do vậy phải lồng int vào để lấy ngày chẵn như bạn mong muốn.

Nhìn chung là vậy, không biết bạn hiểu không

 

[xuongrongdat]

Để hiểu thì trước hết phải biết ô A2 không phải là định dạng ngày mà là dạng chuỗi (văn bản kiểu như "Nguyễn Văn A"). Nếu nó định dạng ngày tháng giống B2 thì rất đơn giản lấy B2-A2.
Giờ nó không phải ngày tháng thì quy về ngày tháng bằng cách dùng hàm Date (cú pháp DATE(year, month, day) )
Cụ thể trong công thức trên: (Dòng này quy A2 về ngày tháng năm)
DATE(RIGHT(A2,4),MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0),MID(A2,5,2))
Trong đó:
1/ Đỏ là năm (hàm right lấy 4 số cuối) : trả về 2020
2/ Vàng là tháng (phức tạp hơn vì phải tìm chữ "Jul" là tháng mấy) Trong này dùng hàm match (cú pháp google), tìm chữ "Jul" tức left(A2,3) trong đống 12 tháng đó, trùng nó sẽ trả về vị trí "Jul" là vị trí thứ 7 tính từ "jan","Feb"...(tương ứng 1,2,...,12)
3/ Xanh là ngày, dùng hàm mid lấy từ ký tự thứ 5, cần lấy 2 ký tự tính từ ký tự thứ 5 (tức là ký tự thứ 5 và ký tự thứ 6): Trả về 14

Xử lý xong, hàm Date trả về ngày 14/07/2020

Khi đó sẽ lấy B2 trừ đi hàm date đó, tuy nhiên B2 có giờ, còn hàm date thì giờ theo mặc định (0h00p) nên nó sẽ trừ ra bị lẻ (bỏ hàm Int đi bạn sẽ thấy). Do vậy phải lồng int vào để lấy ngày chẵn như bạn mong muốn.

Nhìn chung là vậy, không biết bạn hiểu không

Cần lắm những người như bạn, ít ai làm cái việc này, thật đáng quý. Mình cũng hướng tới giống như bạn, giải thích cặn kẽ mấy công thức dài và phức tạp cho người cần giúp hiểu rõ (tất nhiên trong khả năng bản thân).

Cám ơn bạn rất nhiều.

 

[VetMini]

Để hiểu thì trước hết phải biết ô A2 không phải là định dạng ngày mà là dạng chuỗi (văn bản kiểu như "Nguyễn Văn A"). Nếu nó định dạng ngày tháng giống B2 thì rất đơn giản lấy B2-A2.
...

Hàm DateValue có khả năng đổi chuỗi thành ngày. Với điều kiện là nó hiểu được chuỗi. Xem công thức thứ 2 trong bài #3.
Tôi nghĩ có lẽ thót không dùng được là vì mặc định ngày tháng trong máy của thớt là kiểu Âu (VN, ngày trước tháng) và chuỗi trong file là kiểu Mỹ (tháng trước ngày).

 

[VetMini]

Ghi chú thêm cho hàm đổi tên tháng thành số:
Ngày xưa, bộ nhớ và đĩa cứng trên máy rất eo hẹp. Người ta tìm mọi cách để rút ngắn công thức.
Tên tháng tuy không duy nhất ở hai ký tự đầu nhưng duy nhất ở ký tự 2 và 3. Vì vậy, mẹo ngày xưa là chỉ dò 2 ký tự này:
=Search(ký tự 2 và 3, "#anebarprayunulugepctovec")/2
Ngắn hơn hàm Match nhiều.

Mẹo này sử dụng rất nhiều trong lập trình trước 1990.

 

[Nhattanktnn]

=Search(ký tự 2 và 3, "#anebarprayunulugepctovec")/2

Học được chiều mới từ bác @VetMini . Cái này hay quá (mà bữa sau đụng tới không biết có nhớ ra không hay lại "ngựa quen đường cũ" )

 

[bebo021999]

Biến đổi "Jul 14 2020" thành chuỗi "14Jul2020" sau đó dùng DATEVALUE để chuyển qua giá trị nhé.

=INT(B2)-DATEVALUE(MID(A2,5,2)&LEFT(A2,4)&RIGHT(A2,4))

 

[ndu96081631]

Cách hơi khác khác:

=INT(B2)-REPLACE(A2,7,1,", ")

Hoặc:

=INT(B2)-SUBSTITUTE(A2," ",", ")

 

[xuongrongdat]

Ghi chú thêm cho hàm đổi tên tháng thành số:
Ngày xưa, bộ nhớ và đĩa cứng trên máy rất eo hẹp. Người ta tìm mọi cách để rút ngắn công thức.
Tên tháng tuy không duy nhất ở hai ký tự đầu nhưng duy nhất ở ký tự 2 và 3. Vì vậy, mẹo ngày xưa là chỉ dò 2 ký tự này:
=Search(ký tự 2 và 3, "#anebarprayunulugepctovec")/2
Ngắn hơn hàm Match nhiều.

Mẹo này sử dụng rất nhiều trong lập trình trước 1990.

Giờ con mới hiểu chỗ tô đậm này, éc.