Đặt Validation cho thời gian (1 người xem)

[apala]

Mình có làm một bài về đặt validation cho ngày tháng. Tuy nhiên khi làm xong kết quả không được như mong muốn. Nghĩa là mình nhập, ngày, tháng năm bắt đầu làm việc và so sánh với ngày hôm nay, nếu khoảng thời gian đó <= 10 tháng thì ô đó không hiện màu gì. Nếu khoảng thời gian từ 10 tháng đến 12 tháng thì ô sẽ hiện màu vàng. Nếu khoảng thời gian > 12 tháng thì ô sẽ hiện màu đỏ. Mình đã làm tuy nhiên > 12 tháng mà ô vãn hiện màu vàng. Mong các bạn cho mình hướng xử lý vấn đề này. Cám ơn.

 

[khang2303]

Công thức trong Conditional formatting không vấn đề gì. Công thức trong cột tính tổng ngày (cột E) của bạn bị sai nên mới bị vậy. Bạn chèn thêm cột và tách riêng lệnh =($AA$3-D4)/30 cho từng dòng là thấy rõ ngay: 08/8/2015= 12,03 tháng=> Ok nhưng 15/8/2015 chỉ = 11,8 tháng thôi trong khi cột E vẫn hiển thi 12m6d.

 

[quocgiacan]

Chào apala,

Xem công thức của bạn thì chắc là bạn coi 1 tháng = 30 ngày (do bạn chia cho 30).

Vd: Nếu cột "Signed on", ô D4 là ngày 04/08/2015 thì tính đến ngày hôm nay (03/08/2016) đúng 12 tháng 0 ngày.

Nếu đúng vậy, bạn có thể áp dụng công thức tại ô E4:

=IFERROR(DAYS360($D4-1,TODAY())/30,"")

Điều chỉnh Format Cells (Ctrl+1) tại E4:E17, tại Tab Number/Custom ô trống Type gõ vào:

#" months "?0"days"/"("30")"

Sau đó vào Conditional Formatting/Manage Rules:
1. Xóa điều kiện màu trắng của bạn (không dùng).
2. Vùng điều kiện màu vàng gõ: =AND($E4>10,$E4<12) , chọn Apllies to: $E$4:$E$17
3. Vùng điều kiện màu đỏ gõ: =AND($E4>=12,$D4<>0) , chọn Apllies to: $E$4:$E$17

Chúc bạn thành công

 

[apala]

Chào apala,

Xem công thức của bạn thì chắc là bạn coi 1 tháng = 30 ngày (do bạn chia cho 30).

Vd: Nếu cột "Signed on", ô D4 là ngày 04/08/2015 thì tính đến ngày hôm nay (03/08/2016) đúng 12 tháng 0 ngày.

Nếu đúng vậy, bạn có thể áp dụng công thức tại ô E4:

Điều chỉnh Format Cells (Ctrl+1) tại E4:E17, tại Tab Number/Custom ô trống Type gõ vào:
Sau đó vào Conditional Formatting/Manage Rules:
1. Xóa điều kiện màu trắng của bạn (không dùng).
2. Vùng điều kiện màu vàng gõ: =AND($E4>10,$E4<12) , chọn Apllies to: $E$4:$E$17
3. Vùng điều kiện màu đỏ gõ: =AND($E4>=12,$D4<>0) , chọn Apllies to: $E$4:$E$17

Chúc bạn thành công

Cám ơn bạn đã cho mình một giải pháp. Thật ra mình lấy 1 tháng = 30 ngày cũng chưa chính xác lắm vì có tháng 31 và có tháng 28 hoặc 29 ngày nữa. Nhưng mình chưa biết phải làm cách nào. Hiện tại mình đang thử kết hợp với hàm tính số ngày trong một tháng Day(date(year),(Month)+1,0) để chia chứ không chia cho 30 nữa nhưng máy mình đang gặp vấn đề, cứ sửa hàm là bị nhảy chữ nên chưa thấy được kết quả.

 

[quocgiacan]

Cám ơn bạn đã cho mình một giải pháp. Thật ra mình lấy 1 tháng = 30 ngày cũng chưa chính xác lắm vì có tháng 31 và có tháng 28 hoặc 29 ngày nữa. Nhưng mình chưa biết phải làm cách nào. Hiện tại mình đang thử kết hợp với hàm tính số ngày trong một tháng Day(date(year),(Month)+1,0) để chia chứ không chia cho 30 nữa nhưng máy mình đang gặp vấn đề, cứ sửa hàm là bị nhảy chữ nên chưa thấy được kết quả.

Chào apala,

Đúng như bạn nói là các tháng trong năm có số ngày không chính xác là 30 ngày. Ở đây, yêu cầu của bạn, tôi có thể nói khái quát lại như sau:

1. Hễ nó trọn 1 tháng: từ ngày đầu tháng đến ngày cuối tháng, bất kể 28, 29, 30, 31 thì cũng thể hiện "1 month".
2. Số ngày còn lại mới thể hiện "xx days".

Hàm Days360() đã làm cho bạn việc đó rồi, bởi vậy tôi mới lấy ví dụ: nếu ngày signed on là 04/08/2015 thì ngày hôm qua 03/08/2016 nó thể hiện "12 months 0 days", còn nếu so với hôm nay 04/08/2016 thì nó thể hiện "12 months 1 days". Bạn có để ý rằng, nó bất kể các tháng trong thời đoạn của ví dụ có số ngày là 29 (tháng 02/2016), 30, 31 nó cũng tính là 1 tháng nếu thời lượng đã đủ trọn từ ngày đầu tháng đến ngày cuối tháng.
Có thể bạn làm thử ở sheet khác cứ cho 1 ngày bất kỳ (Vd:01/01/2015), ô kế +1, rồi kéo dài xuống, sử dụng Days360() và định dạng Custom= # ?0/30 , sau đó dùng thử nhiều cách khác nữa để so sánh, thì sẽ quyết được cái nào phù hợp với bạn

Nói thêm, trong cách tính ngày như vậy có 2 hàm hỗ trợ bạn là Days360() và YearFrac(..,0)

Dù sao, cũng là dịp trao đổi thú vị với bạn.

Chúc bạn ngày vui.

 

[Lê Duy Thương]

Cám ơn bạn đã cho mình một giải pháp. Thật ra mình lấy 1 tháng = 30 ngày cũng chưa chính xác lắm vì có tháng 31 và có tháng 28 hoặc 29 ngày nữa. Nhưng mình chưa biết phải làm cách nào. Hiện tại mình đang thử kết hợp với hàm tính số ngày trong một tháng Day(date(year),(Month)+1,0) để chia chứ không chia cho 30 nữa nhưng máy mình đang gặp vấn đề, cứ sửa hàm là bị nhảy chữ nên chưa thấy được kết quả.

Dùng công thức này thử xem

=DATEDIF(D4,TODAY(),"m")&" months "&DATEDIF(D4,TODAY(),"md")&" days"