Finding the Last Day of a Given Month

I think you need to mention that the Analysis ToolPak Add-In needs to be installed before the EOMONTH() function is available.

If not sure that the Analysis ToolPak Add-In is installed, then the safest formula to use is your second one:
=DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0) where num_mths = 0 for the end of month for the current month and num_mths = 1 for the end of the month for the next month, etc.

I think in the third formula, if the date in Col. A is December, then using A2+1 is bound to give error. Please clarify on [email protected]

=DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0) where num_mths = 0 for the end of month for the current month and num_mths = 1 for the end of the month for the next month, etc.
1. Tried the formula if num_months = 0, it gives the end date for the previous month and if num_months = 1, it gives the end date for the current month
2. To get the EO month the formula should be DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0)
3. The third formula DATE(2005,A2+1,0) does seem to work

=DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0) where num_mths = 0 for the end of month for the current month and num_mths = 1 for the end of the month for the next month, etc.
1. Tried the formula if num_months = 0, it gives the end date for the previous month and if num_months = 1, it gives the end date for the current month
2. To get the EO month the formula should be DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0)
3. The third formula DATE(2005,A2+1,0) does seem to work

I would request to all those who give excel tips and the ones who post it on this forum to list their tip/suggestion with example. That way it will be a lot easier for less advance excel user to understand the working of it in a real example and than able to apply whenver the face that particular situation.

Thanks to all.

Problem:

Calculating the date at the end of the current month, as well as the date at the end of each month (serial number) listed in column A.

Solution:

To calculate the date at the end of the current month, use the EOMONTH and TODAY functions as shown in the following formula:
=EOMONTH(TODAY(),0)

To calculate the date at the end of next month, use the EOMONTH and TODAY functions as shown in the following formula:
=EOMONTH(TODAY(),1)

To calculate the date at the end of each month listed in column A, use the DATE function as shown in the following formula:
=DATE(2005,A2+1,0)

You have to be sure anyone viewing your worksheet has the Analysis ToolPak Add-In installed, or that you have converted the formula to values (copy, paste special, values).

=DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0) where num_mths = 0 for the end of month for the current month and num_mths = 1 for the end of the month for the next month, etc.
1. Tried the formula if num_months = 0, it gives the end date for the previous month and if num_months = 1, it gives the end date for the current month
2. To get the EO month the formula should be DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0)
3. The third formula DATE(2005,A2+1,0) does not seem to work