Formulas to find End of Month

Put your best Excel related tips and tricks here.
Forum rules
This is the place to put all of your Excel Tips & Tricks.
User avatar
LegacySubaru
Posts: 149
Joined: Tue Jan 29, 2008 2:23 am

Formulas to find End of Month

Postby LegacySubaru » Mon Feb 23, 2009 9:55 pm

C4= 1/1/2009

Eomonth()

eomonth(C4,0) ** you have to activate the Analysis Toolpak in the Add-ins 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! :D **

markl
Posts: 219
Joined: Thu Jun 19, 2008 8:58 am

Re: Formulas to find End of Month

Postby markl » Tue Feb 24, 2009 9:33 am

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.

jmatulevich
Posts: 9
Joined: Fri Aug 01, 2008 1:23 pm

Re: Formulas to find End of Month

Postby jmatulevich » Tue Feb 24, 2009 12:34 pm

Here is a link to the Tool pack he mentioned above
http://www.microsoft.com/downloads/deta ... laylang=en

jhm

david
Posts: 6
Joined: Tue Aug 04, 2009 10:15 am

Re: Formulas to find End of Month

Postby david » Tue Aug 04, 2009 10:22 am

This is great but is there a way to do this rolling forward or back if you are on a 4-4-5 fiscal calendar?

markl
Posts: 219
Joined: Thu Jun 19, 2008 8:58 am

Re: Formulas to find End of Month

Postby markl » Tue Aug 04, 2009 10:55 am

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.

User avatar
Sebastiaan Lubbers
Posts: 213
Joined: Tue Jun 02, 2009 12:46 am

Re: Formulas to find End of Month

Postby Sebastiaan Lubbers » Tue Aug 04, 2009 11:39 pm

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
JetReports Certified Professional & Business Developer @ Herke ICT Group (http://www.herke.nl/)

markl
Posts: 219
Joined: Thu Jun 19, 2008 8:58 am

Re: Formulas to find End of Month

Postby markl » Wed Aug 05, 2009 5:47 am

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.

SJL4782
Posts: 170
Joined: Mon Dec 28, 2009 3:51 pm

Re: Formulas to find End of Month

Postby SJL4782 » Thu Jul 29, 2010 5:40 pm

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...
Attachments
Dates.xlsx
(14.77 KiB) Downloaded 576 times

meemz
Posts: 32
Joined: Mon Jun 02, 2008 8:40 pm

Re: Formulas to find End of Month

Postby meemz » Wed Sep 29, 2010 6:01 am

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.

timparrott
Posts: 1
Joined: Thu Mar 24, 2011 1:19 pm

Formulas to find fixed day in the current week

Postby timparrott » Thu Jun 23, 2011 1:03 am

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

Tris123
Posts: 1
Joined: Thu Nov 06, 2014 10:09 pm

Re: Formulas to find End of Month

Postby Tris123 » Sun Nov 09, 2014 10:37 pm

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.excel-easy.com/examples/last ... month.html


Return to “Excel Tips & Tricks”

Who is online

Users browsing this forum: No registered users and 1 guest