PDA

View Full Version : công thức tính ngày cuối cùng trong tháng



letung
02-02-07, 06:54 PM
Mình đang tìm 1 công thức mà nó trả về giá trị ngày cuối cùng của 1 tháng cho trước.

VD: ô A1 có giá trị : 04/02/2007
ô A2 đặt công thức gì để có kết quả : 28 ?+-+-+-+
Các bạn giúp mình với nhé.

ruadangyeu
02-02-07, 09:25 PM
Cái này thì đơn giản lắm
Ngày cuối tháng này thì bằng ngày đầu tháng sau trừ đi 1, mà ngày đầu tháng thì luôn là ngày mùng 1, vậy ta có công thức

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Chúc thành công!

SA_DQ
03-02-07, 05:46 AM
http://giaiphapexcel.com/forum/showthread.php?t=26&highlight=EOMONTH

Mr Okebab
05-02-07, 12:28 PM
Cái này thì đơn giản lắm
Ngày cuối tháng này thì bằng ngày đầu tháng sau trừ đi 1, mà ngày đầu tháng thì luôn là ngày mùng 1, vậy ta có công thức

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Chúc thành công!

Công thức rất hay. Nhưng sao lại phải thêm trừ 1 làm gì cho phức tạp vậy Qui Thần :
=DATE(YEAR(A1);MONTH(A1)+1;0)

Thân!

Đào Việt Cường
06-02-07, 01:27 PM
Dear all,
--------
Xin mọi người tập trung chuyên môn!
Theo em thì công thức
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
tuy có dài hơn nhưng lại dễ hiểu hơn vì:

Ngày cuối tháng này thì bằng ngày đầu tháng sau trừ đi 1, mà ngày đầu tháng thì luôn là ngày mùng 1
Tuy nhiên ý đồ thì hơi khác một chút so với diễn đạt, nên viết gọn lại thế này (dạng công thức):
Đặt Name Ngayhienhanh= $A$1 (làm công thức dễ đọc hơn)
Ngày đầu của tháng sau (sau tháng hiện hành) là:
NgaydauThangsau =DATE(Year(Ngayhienhanh);MONTH(Ngayhienhanh)+1;1)
Suy ra ngày cuối tháng: NgaydauThangsau -1
Còn công thức này
= DATE(YEAR(A1);MONTH(A1)+1;0)
thì phải thật hiểu Excel mới... hiểu được!

SA_DQ
06-02-07, 05:20 PM
=EOMONTH(TODAY(),0)
=EOMONTH(L8,0)

& nhờ Mod nào ghé qua xoá giùm bài trên của mình với; Xin cảm ơn!

DVC's student
26-02-07, 11:13 AM
Còn công thức này dich la: ngay thu 0 tuc la ngay truoc ngay dau tien (ngay 1) cua thang toi /-*+/
= DATE(YEAR(A1);MONTH(A1)+1;0)

thì phải thật hiểu Excel mới... hiểu được!
Sf noi ro hon duoc ko a?/-*+/

Đào Việt Cường
26-02-07, 02:11 PM
DATE(year;month;day)


*

Bạn có biết:


year: Là một chuỗi số gồm bốn ký tự. MS Excel biểu diễn năm phụ thuộc vào thiết lập thời gian trên hệ thống của bạn. Có 2 thiết lập biểu diễn năm: "1900 date system" và "1904 date system". Mặc định MS Excel sử dụng hệ thống "1900 date system". Các bạn có thể thay đổi thiết lập mặc định này trong tuỳ chọn Tools/Options/Calculation/Workbook options: 1904 data system (Alt+T+O, thẻ Calculation: Alt+D).
Hiểu thế nào là 1 năm trong Excel? Hãy bắt đầu bằng con số 0!
Với thiết lập mặc định ("1900 date system" và chúng ta chỉ bàn luận thời gian trong Excel với thiết lập này), khi bạn định dạng 1 cell chứa giá trị là số 0 với mã định dạng "dd/MM/yyyy" thì Excel biểu diễn chuỗi ngày tháng là 00/01/1900. Ngược lại, với thiết lập "1904 date system" thì bạn nhận được kết quả: 01/01/1904.
Lý do MS phải có hai tuỳ chọn này có lẽ là khả năng tương thích của Excel đối với các hệ điều hành: với "1900 date system" Excel tương thích với hệ điều hành MS Windows, với "1900 date system" Excel hoạt động tốt với hệ điều hành Macintosh. Nhưng điều mà chúng ta - những người sử dụng Excel trên MS Windows - quan tâm không phải là vấn đề tương thích mà là thời gian được Excel biểu diễn như thế nào.
Theo hệ thống này, một điều lý thú nữa mà ít người sử dụng MS Excel để ý rằng:
- Nếu giá trị của đối số year trong hàm DATE là một số thuộc dãy (0:1899) thì Excel sẽ hiểu rằng năm mà bạn muốn biểu thị là một số tổng của 1900 cộng với chính số đó. Ví dụ =DATE(107;1;1) thì kết quả biểu diễn sẽ là ngày 01/01/2007 (year = 1900 + 107).
- Nếu giá trị của đối số year trong hàm DATE là một số thuộc dãy (1900:9999) thì giá trị của đối số year được biểu diễn chính là giá trị mà bạn đưa vào. Ví dụ =DATE(2007;1;1).


*

month: là một chuỗi số biểu diễn số tháng trong năm. Nếu một month là một số > 12 thì Excel sẽ tự động cộng vào year một số TRUNC(month/12):

year= year + TRUNC(month/12).
(trong đó hàm TRUNC là hàm lấy số nguyên)
còn month được xác định:
month= MOD(month/12)
Ví dụ =DATE(2006;13;1), kết quả: 01/01/2007


*

day: Như đã đề cập, với thiết lập mặc định thời gian bắt đầu (giá trị 0) được Excel biểu diễn dạng ngày tháng là 00/01/1900. Đây là có thể là lý do giải thích tại sao bạn có thể biểu diễn được số 0 cho đối số day trong hàm DATE như một ngày trước ngày đầu tiên của tháng kế tiếp. Còn thời gian giới hạn tối đa mà MS biểu diễn được là ngày 31/12/9999 (vì 1 năm chỉ được phép biểu diễn bởi 4 ký tự).
Như vậy sự cố "Y10K" chắc chắn sẽ xảy ra nếu bạn muốn làm việc với dữ liệu thời gian từ sau ngày 31/12/9999 trở đi. Lỗi #NUM! phát sinh khi mà bạn cố gắng tính toán giá trị ngày tháng không nằm trong giới hạn này!
Cũng giống như month, nếu day > ngày cuối cùng của month thì month được cộng bổ sung một số TRUNC(day/<ngày cuối cùng của month>) và day được xác định lại: day= MOD(day;<ngày cuối cùng của month>)
ví dụ: = DATE(2006;12;32), kết quả: 01/01/2007.


*

Bạn có thể tìm hiểu thêm các thông tin trên đây trong phần trợ giúp về hàm DATE của MS Excel.

Mr Okebab
30-03-07, 09:57 PM
Dear all,
--------

Theo em thì công thức
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
tuy có dài hơn nhưng lại dễ hiểu hơn vì:

Tuy nhiên ý đồ thì hơi khác một chút so với diễn đạt, nên viết gọn lại thế này (dạng công thức):
Đặt Name Ngayhienhanh= $A$1 (làm công thức dễ đọc hơn)
Ngày đầu của tháng sau (sau tháng hiện hành) là:
NgaydauThangsau =DATE(Year(Ngayhienhanh);MONTH(Ngayhienhanh)+1;1)
Suy ra ngày cuối tháng: NgaydauThangsau -1
Còn công thức này
= DATE(YEAR(A1);MONTH(A1)+1;0)
thì phải thật hiểu Excel mới... hiểu được!


Mình lại thấy chẳng có gì khác nhau cả, vì đã làm được công thức 1 thì ắt sẽ hiểu công thức 2
Công thức 1 : Tính ngày 01 của tháng sau; Sau đó -1 thì ra ngày 0 của tháng sau (Tức là ngày cuối của tháng hiện tại)
Công thức 2 : Tính ngày 0 của tháng sau (Tức là ngày cuối của tháng hiện tại)
Không biết như thế có gọi là bảo thủ không nhỉ ???

Đào Việt Cường
31-03-07, 11:58 AM
Dear MrOkeBab,
---------------
Đúng là về kết quả tính toán thì không có gì khác nhau cả. Như theo thường lệ cuộc sống hàng ngày chúng ta vẫn hiểu rằng ngày bắt đầu của một tháng là ngày mùng 1.
Công thức 1:
Muốn tính ngày trước ngày đó bao nhiêu ngày thì chỉ việc lấy ngày đó trừ đi khoảng cách ngày. Vậy nên muốn biết ngày trước ngày đầu tiên của tháng hiện hành (ngày cuối tháng trước) chỉ việc lấy ngày đầu tiên của tháng trừ đi 1. (Trong một số trường hợp chúng ta cần phải sử dụng giá trị thời gian đến hàng giờ, hàng phút, hàng giây, hàng tích tắc...(hàng phù phù..ù...:=\+ ...) để tính toán khoảng cách ngày. Ví dụ từ ngày 00:00:00 giờ ngày 31/03/2007 đến 24:00:00 giờ ngày 31/03/2007 là 1 ngày).
Công thức 2:
Không có giải thích nào cắt nghĩa được "Ngày 0 của tháng sau là ngày cuối của tháng hiện tại". Có lẽ khái niệm "Ngày thứ Không" chỉ được đề cập trong Thời kỳ Trước Công Nguyên - khi mà con người đang hình thành ý niệm về thời gian.
Nhưng dù sao, Con Số Không này cũng cho thấy trật tự của Thời gian": Một ngày được bắt đầu từ sau 00 giờ: 00 phút: 00 giây: 00 tích tắc: 00 khắc: 00 ...phù..phù...:=\+... gì... gì...ì.ì.. đó..ó.. (cho tới khi thời gian >0). Nghĩa là một khi thời gian chưa vượt quá Con Số Không thì thời điểm đó vẫn còn là của ngày trước.
Biết quý Thời gian và biết tiết kiệm nó mới thấy Thời gian thật giá trị biết bao!

Mr Okebab
31-03-07, 01:57 PM
Dear MrOkeBab,
---------------

Không có giải thích nào cắt nghĩa được "Ngày 0 của tháng sau là ngày cuối của tháng hiện tại". Có lẽ khái niệm "Ngày thứ Không" chỉ được đề cập trong Thời kỳ Trước Công Nguyên - khi mà con người đang hình thành ý niệm về thời gian.



Lịch sử con số không


Con số "không" mà chúng ta quen và thấy mọi ngày, được ra đời khoảng 200 năm sau Thiên Chúa giáng sinh Con số "không" đã được tượng hình do người Hindu Ấn độ . Người Hindu là những người đầu tiên đưa ra con số này để để trình bày quan niệm "không có số lượng".

Những nền văn minh trước đó, ngay cả người Hy lạp, khái niệm "không" vẫn chưa xảy ra mặc dù rất cần có một con số để chỉ sự vắng mặt của một số đồ vật nào đó.

Liên quan với khái niệm trước của con số zéro, nghĩa thứ hai là có thật, phải biết và phải được phân biệt với sự "không" (nulle, null).

Ðiều rõ ràng là những dân tộc trước đây không đủ khả năng để cảm nhận sự phân biệt giữa không (zéro) và không có gì (rien, nothing).

Hãy thí dụ một khái niệm :

Một người không có một sổ ngân hàng nào hết thì người đó thuộc vào hạng " không có gì". Còn một người có sổ ngân hàng nhưng không có đồng nào trong công thì kết toán sẽ là "zéro".

Nhưng cuối cùng các nhà Toán học đã phát triển cách để viết những con số. Trước tiên ta đếm những đơn vị rồi đến bậc cao hơn là hàng chục rồi hàng chục của chục, hàng chục của chục của chục.. vân vân... Ta cũng trình bày được một trăm hai mươi ba bởi 123. Bởi vì số "mười" đóng một vai trò căn bản trong sự đo lường có lẽ bởi con người đếm bằng những ngón tay trên hai bàn tay và xem như số mười (10) là con số lớn nhất của đơn vị.

Vi trí của con số nói lên số lượng nên gọi là cách đếm theo vị trí.

Hệ thống đếm thập phân theo vị trí do người Hindus, tuy nhiên cũng trên cách xếp đặt đó trước đó hai ngàn năm người Babylone đã dùng nhưng trên căn bản 60 (thay vì 10) và dưới hình thức giới hạn vì họ chưa có số zéro.

Có hai cách sử dụng cực kỳ quan trọng của con số zéro:

- Thứ nhất là ý niệm "không có gì" và "giá trị không" như đã trình bày thí dụ ở chương trước

-Thứ hai là để chỉ giá trị sự không có gì trong hệ thống đếm số theo vị trí. Thí dụ trong số 2106 thì ở vị trí hàng chục là có giá trị không nhưng rõ ràng là nếu so sánh 2 số 216 và 2106 là hoàn toàn khác hẳn.

Cả hai cách dùng đều có một lịch sử không dễ gì giải thích được. Có thể do một người nào đó đã phát minh ra những ý nghĩ rồi thì mỗi người bắt đầu dùng. Cuối cùng cách xử dụng để chỉ con số zéro khác xa với khái niệm lúc đầu.

Ngày xưa toán học dùng để chỉ những vấn đề thực tế hơn là trừu tượng như hôm nay. Phải trải qua những bước đi khổng lồ về ý tưởng để đi từ 5 "con ngựa" sang 5 "vật" rồi cuối cùng ý nghĩ trừu tượng là con số "năm". Nếu như dân tộc xưa giải đáp một bài toán về số ngựa của một nhà chăn nuôi thì chắc chắn họ sẽ không có giải đáp là sẽ có 0 con ngựa hay -23 con ngựa.

Mặc dù người Babylone đã có hệ thống đếm giá trị theo vị trí từ trên 1000 năm nay nhưng chắc chắn là có rất nhiều sự lầm lẫn. Ðiều đáng kể là những có những câu văn nguyên thủy người Babylone viết bằng chữ hình góc (écriture cunéiforme) -cũng như họ đã kiếm ra con số Pi- từ thời đại Toán học Babylone. Người Babylone đã viết chữ hình góc trên những miếng đất sét không nung chín. Những ký hiệu được ấn vô những miếng đất sét. Có rất nhiều miếng đất sét mà số còn sống sót cỡ 1700 miếng trước Công Nguyên và chúng ta có thể đọc được những câu nguyên thủy.

Lẽ đương nhiên khái niệm của họ khác với khái niệm của ta hiện nay và không trên cách đếm căn bản 10 mà là 60 như đã nói trên. Thí dụ nếu dịch con số 2106 và 216 cho họ xem thì họ sẽ hòan toàn không phân biệt được.

Ðến năm 400 trước TC người Babylone đã để 2 số tượng trưng chêm vô nơi mà ta để con số zéro để biểu hiệu những con số 216 hay 21 hay 6.

Hai số tượng trưng chêm không phải là ký hiệu duy nhất được dùng. Người ta đã tìm thấy một viên đất sét nén nơi những người Kish (một thành phố cổ xưa của Mesopotamian ở phía Ðông của Babylone, bây giờ là Trung-Nam Irak) đã dùng một ký hiệu mới.

* Khoảng 700 năm trước CN, viên đất sét nén này có dùng 3 dấu móc để biểu hiệu một chỗ trống (endroit vide) cho cách trình bày vị trí.

Có một dấu hiệu trên một tấm bảng cùng thời kỳ đó cũng dùng một dấu móc để chỉ chỗ trống. Sự bố trí chung cho cách dùng những dấu hiệu khác nhau để chỉ chỗ trống.

Dấu hiệu trống đó chỉ nằm giữa hai con số chớ không bao giờ nằm hai đầu mút của con số. Như vậy ta thấy rằng với 21 và 6, sẽ không có số 216. Ta có thể giả sử rằng vì ngày trước, tình cảm xưa hơn ngữ cảnh (contexte) và đủ để chỉ những gì đã dự kiến để dùng trong những két (caisse).

Nếu lời chỉ dẫn cho ngữ cảnh có vẻ đần độn thì ta cũng phải lưu ý rằng nhờ những lời chỉ dẫn đó đã giúp ta giải thích những con số hiện nay. Nếu tôi hỏi giá tiền xe buýt đi từ nhà tôi đến thành phố bên cạnh mà người ta trả lời là "ba năm mươi" thì tôi hiểu là 3 bảng Anh và 50 pence. Tuy nhiên cũng với câu trả lời tương tự cho câu hỏi về giá tiền chuyến bay từ Edimbourg đến New York thì tôi lại hiểu là 350 bảng Anh, như đã dự kiến.

Từ việc này, ta có thể thấy cách dùng ngắn hạn của zéro để biểu hiệu một chỗ trống thì zéro không phải là một con số mà chỉ là một loại dấu chấm câu (ponctuation) để diễn tả con số chính xác.

Do đó sự chậm ra đời trong mấy ngàn năm đã quyến rũ ta nhất là nó trở thành căn bản cho hệ thống đếm hiện nay.



Rất cảm ơn những chia sẻ của VCường.
Mình không muốn rằng chúng ta quá đi sâu vào từng chi tiết một.
Hiểu được rằng ngày 1 của tháng trước trừ đi 1 sẽ ra ngày của cuối của tháng sau. Như vậy có nghĩa là đã hiểu được rằng thời gian là một sự liên tiếp, không có dừng lại.

Cũng giống như thế ta còn có cả ngày không; ngày âm. .
Nhưng do thói quen mọi người không nhận ra rằng mình đang sử dụng nó hàng ngày :
----VD : Không giờ ngày hôm nay chính là 24 giờ ngày hôm qua.
Vì thế không có gì là khó hiểu khi nó rằng ngày không của tháng này chính là ngày cuối cùng của tháng sau ( chỉ do là mọi người không quen thôi)

Mọi người hay nói : Ngày hôm qua (của ngày hôm nay); thực ra diễn đạt theo con số thì đó chính là ngày âm 1 (của ngày hôm nay)

Và ngày mai (của ngày hôm nay) chính là ngày dương 1 (của ngày hôm nay)

Chính vì thế mình mới nói là hai công thức kia không khác nhau là mấy (ở mặt tư duy logic)

Do đó mình không nói rằng VCường sai, nhất định là không sai rồi. Nhưng cái mà mình muốn nói là cái mà mọi người hay dùng, nhưng do thói quen, chỉ cần diễn đạt khác đi chút xíu là cảm thấy nó xa lạ lắm. Vì thực ra Tin học cũng là từ tư duy của con người mà ra thôi, chẳng qua là nó thể hiện theo một dạng khác.

Thân!

hoaicf
22-01-08, 08:40 AM
Vậy các bạn cho mình hỏi muốn biết ngày cuối của tháng là ngày 30 hay 31 thì phải tính như thế nào?

Duong gia
22-01-08, 08:56 AM
Vậy các bạn cho mình hỏi muốn biết ngày cuối của tháng là ngày 30 hay 31 thì phải tính như thế nào?

Mình xin góp vui về hàm ngày chót sau : (xin chép đoạn code sau vào trong 1 module của file excel)

Public Function NgayChot(Thang, Nam) As Date
Dim StartDate As Date, StopDate As Date
StartDate = DateSerial(Nam, Thang, 1)
StopDate = DateAdd("m", 1, StartDate) - 1
NgayChot = StopDate
End Function

Sau đó quay ra sheet thử như sau :
VD tại D10 bạn gỏ bất kỳ ngày nào của tháng 01/2008. (VD 01/01/2008) sau đó tại D11 nhập công thức sau : =ngaychot(MONTH(D10),YEAR(D10))
sẽ có kết quả như ý.

zheng_1008
21-07-09, 10:52 AM
Mình xin góp vui về hàm ngày chót sau : (xin chép đoạn code sau vào trong 1 module của file excel)

"Public Function NgayChot(Thang, Nam) As Date
Dim StartDate As Date, StopDate As Date
StartDate = DateSerial(Nam, Thang, 1)
StopDate = DateAdd("m", 1, StartDate) - 1
NgayChot = StopDate
End Function"

Sau đó quay ra sheet thử như sau :
VD tại D10 bạn gỏ bất kỳ ngày nào của tháng 01/2008. (VD 01/01/2008) sau đó tại D11 nhập công thức sau : =ngaychot(MONTH(D10),YEAR(D10))
sẽ có kết quả như ý.

-------------

Cảm ơn bạn, mình rất quan tâm đến hàm này và đã thực hiện theo lời dẫn cua bạn, copy toàn bộ đoạn code trên vào Tool/Marcro/Visual Basic Editor/Module 1
sau đó copy công thức dưới của bạn, nhưng không ra được kết quả
Mình có phải làm thêm cài đặt gì nữa không ? (mình đã lựa chọn chế độ security ở mức độ Medium, và enable marcro khi mở file)

Best regards

MinhNguyệt
21-07-09, 11:24 AM
-------------

Cảm ơn bạn, mình rất quan tâm đến hàm này và đã thực hiện theo lời dẫn cua bạn, copy toàn bộ đoạn code trên vào Tool/Marcro/Visual Basic Editor/Module 1
sau đó copy công thức dưới của bạn, nhưng không ra được kết quả
Mình có phải làm thêm cài đặt gì nữa không ? (mình đã lựa chọn chế độ security ở mức độ Medium, và enable marcro khi mở file)

Best regards

Bạn kiểm tra lại, Code hoàn toàn chính xác. Lưu ý bạn ô tính ngày cuối tháng phải format dạng dd/mm/yyyy, nếu không muốn nó hiển thị số
Thân

thuykon
26-05-11, 11:03 AM
Bạn ui, công thức này mình thử làm nhưng ko có kết quả...hjc2.^^. trừ 1 thì ra nhưng cống thì ko. bạn có thể giúp mình ko. Thanks bạn nhìu.

minhcong.tckt
14-12-12, 03:01 PM
Anh chị cho em hỏi
Giả sử tại ô A1 em có năm 2012
Vậy làm thế nào để ô A2: biết ngày cuối cùng của tháng 1: VD : "31/01/2012"
tháng 2 ô A3: "29/02/2012"
Các ô khác tương tự

Mong nhận được sự giúp đỡ của các anh chị
Chân thành cảm ơn!!!

ndu96081631
14-12-12, 03:05 PM
Anh chị cho em hỏi
Giả sử tại ô A1 em có năm 2012
Vậy làm thế nào để ô A2: biết ngày cuối cùng của tháng 1: VD : "31/01/2012"
tháng 2 ô A3: "29/02/2012"
Các ô khác tương tự

Mong nhận được sự giúp đỡ của các anh chị
Chân thành cảm ơn!!!

Công thức cho A2:

=DATE($A$1,ROWS($1:2),0)
Kéo fill xuống

sealand
14-12-12, 04:58 PM
Cái này mình thích nhất công thức của Ndu đã giới thiệu trước đây, có lẽ nhất thời Ndu không nhớ:

Ngày 0 của tháng này chính là ngày cuối cùng của tháng trước, ví dụ tìm ngày cuối tháng 2/2012 sẽ là

=DATE(2012,3,0) (29/2/2012)

Công thức này đúng luôn cả trong VBA nên các hàm UDF về tìm ngày cuối tháng trở lên thừa.