Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Fiscal Period Based on Two Dates

0

Our fiscal year starts in July.

I have a list of data that contains 3 calendar years.

With a table that has start date, end date and fiscal period, is there a way to dynamically determine the fiscal period if I input the first fiscal period in the table?

For instance, July 1, 2020 to July 31, 2020 will be July 2021. Then August 1, 2020 to August 31, 2020 will be August 2021. When I get to January 1, 2021 to January 31, 2021, the third column has to say January 2021.

excel
posted by: Dutch on Wed Jan 26, 2022 at 12:18 pm
Answer
Discuss

Answers

0

Hi Dutch and welcome to the Forum

In the attached sheet I have A2 in yellow, formatted as a date (US-style, like in your question). To determine the end of the month, you can use the Excel function EOMONTH (which needs a date, then a number of months negative or positive from that). Setting the latter to 0 makes the formula in B2:

=EOMONTH(A2,0)
and returns the last day in that month (e.g. July 31)..A2 can be copied down as far  as needed

If A3 is set to:

=EOMONTH(A2,0)+1
it gives the first day of the month after A2 (e.g. August 01). A3 can be copied down as far as needed and B2 copied down to match. All you need to change is A2 and all the dates will alter to suit.

To get column C to show date like January 2020 (iwhen it's really January 2021, you can use this formula in C2 (somewhat clumsy- but it's late here!):

=TEXT(DATE(YEAR(A$2)+1+ROUNDDOWN((ROW()-2)/12,0), MONTH(A2),1),"mmmm yyyy")
where the TEXT function qwraps around a DATE function which creates a data from year, month and date elements. The year portion depend on that of the $ fixed cell A2 and increments it after a year via the ROUNDDOWN to 0 decimal places. Copy that down.

Hope this helps.

posted by: John_Ru (rep: 6152) on Wed Jan 26, 2022 at 2:08 pm
Discuss

Discussion

John,

Thanks for your reply.  Unfortunately, it's not correct.

When I asked my question, I started off with the month of July 2020.  That is the first month of our Fiscal 2021.  I can use EOMONTH to get the third column to say July 2021, but once I get into January 2021, I need the third column to say January 2021, not January 2022.

Put another way, from July 1, 2020 to June 30, 2021, that is my fiscal year.  I'm looking for a way to get the month to proceed with the year to stay static.

Dutch
Dutch Jan 26, '22 at 2:23 pm
Dutch. Please see my revised Answer and file. There may be better colutions but I'm too tired to think!
John_Ru (rep: 6152) Jan 26, '22 at 5:32 pm
Dutch, did the revision work?
John_Ru (rep: 6152) Feb 4, '22 at 8:24 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login