Determine financial year end for a date

**Bezzie** · 11-20-2014, 06:05 PM

Want to create a VBA function that can return me the financial year end date of any date when I only have the month of financial year as a parameter.

Please Login or Register  to view this content.

For instance: If financial year ends February then a financial year runs from 1/3/???? to 28/2/???? and for a leap year it's 1/3/???? to 29/2/????.

So if I enter any date between 1/3/2006 and 28/2/2007, say 5/12/2006 (dd/mm/yyyy format) and the nMonth parameter as 2 (February) the function must return date as 28/2/2007 (dd/mm/yyyy format). For leap year any date between 1/3/2007 and 29/2/2008 will return 29/2/2008.

I have no idea how to attack this. Any ideas appreciated.

**pjwhitfield** · 11-20-2014, 06:45 PM

Please Login or Register  to view this content.

Written without Excel to hand so there may be some errors

**shg** · 11-20-2014, 07:20 PM

How about just a formula?

Row\Col	H	I	J	K
1	Date	Month	EoFY
2	02/28/2014	2	02/28/2014	J2: =EOMONTH(DATE(YEAR(H2) + (MONTH(H2) > I2), I2, 1), 0)
3	03/01/2014	2	02/28/2015

**Bezzie** · 11-21-2014, 12:50 AM

pjwhitfield:

Should the date function you used be dateserial function (if I use date as you had it it gives an error)? If so then it doesn't work. See YE returned below. It's not handling the leap year correctly.
Date: 01/01/2007 FY: 28/02/2007 - correct
Date: 30/01/2007 FY: 28/02/2007 - correct
Date: 28/02/2007 FY: 28/02/2007 - correct
Date: 29/03/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 27/04/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 26/05/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 24/06/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 23/07/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 21/08/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 19/09/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 18/10/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 16/11/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 15/12/2007 FY: 28/02/2008 - this should be 29/02/2008
Date: 13/01/2008 FY: 29/02/2008 - here it's correct
Date: 11/02/2008 FY: 29/02/2008 - here it's correct
Date: 11/03/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 09/04/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 08/05/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 06/06/2008 FY: 01/03/2009 - this should be 28/02/2009
Date: 05/07/2008 FY: 01/03/2009 - this should be 28/02/2009

**Bezzie** · 11-21-2014, 01:03 AM

shg:

Your example works like a charm in Excel cell but when using it in VBA it doesn't want to accept the function. Gives an error. I added Application.WorksheetFunction in front and changed the cell references to the parameter variables but can't get it to accept the function.

Please Login or Register  to view this content.

YEAR is highlighted and error message is "Compile error: Expected: )"

Working with dates is a real pain in the @$$

**pjwhitfield** · 11-21-2014, 03:43 AM

Schoolboy error on my behalf!

To get the last day of month I was setting the date to the 1st of the following month and deducting a day.....which works fine however I was then deciding if it needed to be the year after and adding a year on rather than doing it the other way round!

Please Login or Register  to view this content.

**Bezzie** · 11-21-2014, 06:17 AM

Thanks. It's working now from VBA.

**shg** · 11-23-2014, 07:17 PM

Another way:

Please Login or Register  to view this content.

Determine financial year end for a date

LinkBack

Thread Tools

Rate This Thread

Display

Determine financial year end for a date

Re: Determine financial year end for a date

Re: Determine financial year end for a date

Re: Determine financial year end for a date

Re: Determine financial year end for a date

Re: Determine financial year end for a date

Re: Determine financial year end for a date

Re: Determine financial year end for a date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] To calculate last date of a financial year

Input a year to determine the start date for 4-4-5 Calendar Year.

Determine if date is before end of fiscal year of previous year

Last date of Financial year

Totalling up a Financial Year to date

Bookmarks

Bookmarks

Posting Permissions