» Finding the Last Day of a Given Month
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
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)
Book Store:
Recommended Books:
Analysis ToolPak Add-In
jwanegar
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 function use
Deepak
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 dsugandhi@vsnl.net
Finding the last day of a given month
gms_nm
=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
Date function use
MohanaSundaram G
=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 function use
MohanaSundaram G
=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
valubale suggestion
roomi
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.
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.