Công thức trả về ngày cuối tháng khác thứ bảy, CN

[handung107]

Làm sao khi nhập số tháng và số năm sẽ ra cho ra kết quả là ngày cuối tháng đó không trùng thứ bảy, chủ nhật. Nếu ngày cuối tháng đó là T7. CN thì lấy ngày Tsáu.

Có nhiều cách :

- Cách 1 :

Công thức đó như sau :

=DATE(YEAR(Ngay),MONTH(Ngay)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(Ngay),MONTH(Ngay)+1,0),2) -5))

trong đó Ngay sẽ là một cell chứa ngày tháng năm bạn cần biết

Thí dụ hàm : =DATE(2004,9+1,0) sẽ trả về ngày cuối tháng 30/09/2004. Nếu bạn không cộng thêm 1 mà chỉ nhập tháng là 9, hàm sẽ trả về ngày cuối tháng 31/08/2004.

Hàm WEEKDAY(DATE(2004,10,0),2) sẽ cho ta biết ngày cuối tháng là thứ mấy trong tuần. Weekday([noparse]******[/noparse]_number, return_type) . [noparse]******[/noparse]_number là Date(2004,10,0). Còn return_type là 1 nếu quy định chủ nhật là 1--> thứ bảy là 7, return_type là 2 nếu quy định thứ hai là 1--> chủ nhật là 7, return type là 0 nếu quy định thứ hai là 1-->thứ bảy là 6.

Nếu tôi chọn weekday(date(2004,10,0),2) thì khi ngày cuối tháng rơi vào thứ 6, hàm sẽ cho kết quả là 5. Hàm Max(0,weekday(date(2004,10,0),2)-5) sẽ cho kết quả là 0 nếu ngày cuối tháng rơi vào thứ 2 đến thứ 6 trong tuần và cho kết quả là 1 nếu là thứ bảy, 2 nếu là chủ nhật.

Do đó nếu ta chọn lùi lại 1 hoặc 2 ngày nếu ngày cuối tháng rơi vào thứ bảy hay chủ nhật thì bạn chỉ cần làm phép trừ ngày cuối tháng cho 1 hoặc 2 nữa là xong. Khi ấy hàm sẽ là : =DATE(2004,10,0)-(MAX(0,WEEKDAY(Date(2004,10,0,2) -5)).

- Cách 2 :

DATE(Năm, Tháng, Ngày)

DATE(2004,02, 15) = 15 / 02 / 2004
DATE(2004,02, 0 ) = 0 / 02 / 2004 = 31/01/2004 .Là ngày cuối của Tháng -1

Vì vậy, nếu muốn trả về ngày cuối tháng 02 thì phải Tháng +1 (02+1). Thực chất là việc bù trừ (02 +1) - 1=02
DATE(2004,02+1, 0 ) = 0 / 03 / 2004 = 29/02/2004 (Tháng -1)

A2=10/01/2004
Ngày cuối của tháng Month(A2) là: DATE(Year(A2), Month(A2)+1, 0)

Để giải bài toán trên tư duy bắt đầy như sau:

=Ngày cuối tháng -1 (nếu thứ của ngày cuối tháng là T7 ) hoặc -2 (nếu thứ của ngày cuối tháng là CN)
T7-1= CN-2 = T6

WEEKDAY(Ngay) = Ngày trong tuần: T7 là 7, CN là 1


Diễn giải theo CT như sau:

=DATE(Year(A2), Month(A2)+1, 0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=7,-1,0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=1,-2,0)

(Ngày đầu tháng này = ngày cuối của tháng trước)

- Cách 3 : Kết hợp với hàm EOMONTH()

=IF(WEEKDAY(EOMONTH(A1,0),1)=1,EOMONTH(A1,0)-2,IF(WEEKDAY(EOMONTH(A1,0),1)=7,EOMONTH(A1,0)-1,EOMONTH(A1,0)))

 

[tutkkt]

Bạn ơi ! Công thức của bạn đưa ra nhằm ứng dụng vào trường hợp nào vậy bạn. Mình thấy thích cái chuỗi công thức dài loằng ngoằng của bạn đấy, nhưng không biết nó áp dụng cho trường hợp nào. Mong bạn chỉ giáo ! Thanks !

 

[anhtuan1066]

Bài này tôi cũng có 1 cách làm khác (nhưng vẫn dựa trên cơ sở xem ngày cuối là ngày nào, nếu CN thì trừ 1, nếu thứ 7 thì trừ 2, ngược lại thì đễ nguyên):
=EOMONTH($B2,0)-WEEKDAY(EOMONTH($B2,0),2)+MIN(5,WEEKDAY(EOMONTH($B2,0),2))
ANH TUẤN

 

[phamngocanh19821988]

Diễn giải theo CT như sau:

=DATE(Year(A2), Month(A2)+1, 0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=7,-1,0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=1,-2,0)

Bây giờ em muốn như thế này :
="Bao cáo từ ngày 1/"&month(today())-1&"/"&year(today())&" đến ngày "&date(year(today()),month(today())+1,0) thì lại không được. Các bác giúp em với[/QUOTE]
Em làm như thế này được không các bác
="Báo cáo từ ngày 1/"&MONTH(TODAY())-1&"/"&YEAR(TODAY())&" đến ngày "&DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),0))&"/"&MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),0))&"/"&YEAR(DATE(YEAR(TODAY()),MONTH(TODAY()),0))

 

[ChanhTQ@]

Em làm như thế này được không các bác
="Báo cáo từ ngày 1/"&MONTH(TODAY())-1&"/"&YEAR(TODAY())&" đến ngày "&DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),0))&"/"&MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),0))&"/"&YEAR(DATE(YEAR(TODAY()),MONTH(TODAY()),0))

Làm như vậy, mình thử rồi & thấy đúng khi ngày hiện tại đang là 11 tháng cuối năm mà thôi!

Hàm của bạn có thể viết gọn lại như vầy:

="Báo cáo từ ngày 1/"&MONTH(A3)-1&"/"&YEAR(A3)&" đến ngày "&DAY(A3-DAY(A3))&"/"&MONTH(A3)-1&"/"&YEAR(A3)

Nhưng nếu tại [A3] đang là ngày nào đó trong tháng 1 thì . . . (Bạn tự thử đi nha!)

Vậy nên phải xử lý thêm cái tháng 0 to đùng ấy nữa!