C4= 1/1/2009
Eomonth()
eomonth(C4,0) ** you have to activate the Analysis Toolpak in the Addins for Excel 2003 and before**
Date()
=DATE(YEAR(C4),1+MONTH(C4),1)1
OR
DATE(YEAR(C4),1+MONTH(C4),0) **it is unusual to consider the "0th" of a month, but it works! **
Formulas to find End of Month
Forum rules
This is the place to put all of your Excel Tips & Tricks.
This is the place to put all of your Excel Tips & Tricks.
Re: Formulas to find End of Month
Thanks for posting!
I've been finding myself needing the last end of month, based on 'today'. i.e. a report run in Feb should always report 31st of Jan as the last end of month. So I use a very similar formula to yours
C5: =DATE(YEAR(C4),MONTH(C4),0)
I'd be interested to know if others have any tips and trick of this nature, as it has simplified our report maintenence.
I've been finding myself needing the last end of month, based on 'today'. i.e. a report run in Feb should always report 31st of Jan as the last end of month. So I use a very similar formula to yours
C5: =DATE(YEAR(C4),MONTH(C4),0)
I'd be interested to know if others have any tips and trick of this nature, as it has simplified our report maintenence.

 Posts: 9
 Joined: Fri Aug 01, 2008 1:23 pm
Re: Formulas to find End of Month
Here is a link to the Tool pack he mentioned above
http://www.microsoft.com/downloads/deta ... laylang=en
jhm
http://www.microsoft.com/downloads/deta ... laylang=en
jhm
Re: Formulas to find End of Month
This is great but is there a way to do this rolling forward or back if you are on a 445 fiscal calendar?
Re: Formulas to find End of Month
Unless Jet has some built in functions, I'd suggest you may have to build yourself an Excel table or similar to do the work for you.
If you can spell out an example of the scenario and the calculations you'd need, I can help you get started.
If you can spell out an example of the scenario and the calculations you'd need, I can help you get started.
 Sebastiaan Lubbers
 Posts: 213
 Joined: Tue Jun 02, 2009 12:46 am
Re: Formulas to find End of Month
Code: Select all
JetReports: 12.0.12150.0
Dynamics: 6.0.32012.0
7.0.33280.0
Excel: 14.0.6112.5000
Windows 7 Enterprise  Dutch Regional
Re: Formulas to find End of Month
I was more interested in the types of calculations required, i.e. based on a give date, i.e. 'today', what other dates are needed to be derived  such as end of week, current week, previous week, previous period, current period, same week/period last year, etc.
Re: Formulas to find End of Month
I had put together a list of calcuations for a variety of commonly used dates.
A few of the calculations (those on fiscal year) work with a NAV database only.
These functions were set up in Excel 2007, not sure about 2003 functionality...
A few of the calculations (those on fiscal year) work with a NAV database only.
These functions were set up in Excel 2007, not sure about 2003 functionality...
 Attachments

 Dates.xlsx
 (14.77 KiB) Downloaded 576 times
Re: Formulas to find End of Month
If you are using Excel 2007 there is a new EOMONTH function as well that does the job just great.
=EOMONTH(cell reference,0) returns end of that month
changing the zero to 2 returns end of 3 months from that date i.e. Quarters.
=EOMONTH(cell reference,0) returns end of that month
changing the zero to 2 returns end of 3 months from that date i.e. Quarters.

 Posts: 1
 Joined: Thu Mar 24, 2011 1:19 pm
Formulas to find fixed day in the current week
My offering for start of the week and others days in the current week or the start of next week.
This is useful in operational reports that need to present the current week based on a fixed start day.
=Today()  Weekday(Today(),1) will give the previous Saturday.
The 1 determines the start date of the week, so 2 will return Sunday, 3 Monday etcetera up to 7
=Today()  Weekday(Today(),3) + 7 will give the next Monday
This is useful in operational reports that need to present the current week based on a fixed start day.
=Today()  Weekday(Today(),1) will give the previous Saturday.
The 1 determines the start date of the week, so 2 will return Sunday, 3 Monday etcetera up to 7
=Today()  Weekday(Today(),3) + 7 will give the next Monday
Re: Formulas to find End of Month
I simply follow below mentioned link to find the end of Month and It's work for me and by the way thanks jmatulevich to providing the link it's also helped me.
http://www.exceleasy.com/examples/last ... month.html
http://www.exceleasy.com/examples/last ... month.html
Return to “Excel Tips & Tricks”
Who is online
Users browsing this forum: No registered users and 1 guest