How to get month and year of previous and current financial year?

Hi friends,

I want to get a first month and year of previous and current financial year; last month and year of previous and current financial year. I used the following formula to get it but not succeed.

Please Login or Register  to view this content.

In the attached workbook I have shown the require output. I want to get it in local language. So I used ‘vlookup and text’ function combination to get it.

Any help will be highly appreciated.

Thanking you,

I used four independent formulae, based around this construction:

=IF(MONTH(TODAY())>=4,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY())-2,4,1))

This gives first month of previous FY

OMG... JT has been there 20 minutes ago. Guess who forgot to refresh before posting!!

Hi John,
Thank you. I'm getting output in the binary code number of date.
I require a output in local language so I require a combination of 'vlookup, text, edate' function. the vlookup table is in column 'CA:CB' to get local month name in output.
Thanking you,

So ...

=VLOOKUP(TEXT(BD4,"MMMM"),$CA:$CB,2,0) & TEXT(BD4,"[$-4000000]yyyy")

Copy down

OR

replace BD4 with the formula in BD4 (and same for BD5)

=VLOOKUP(TEXT(IF(MONTH(TODAY())<=3,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY()),4,1)),"MMMM"),$CA:$CB,2,0) & TEXT(IF(MONTH(TODAY())<=3,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY()),4,1)),"[$-4000000]yyyy")


=VLOOKUP(TEXT(EDATE(BD4,11),"MMMM"),$CA:$CB,2,0) & TEXT(EDATE(BD4,11),"[$-4000000]yyyy")

Originally Posted by JohnTopley

......IF(MONTH(TODAY())<=12.......

Hello John,

Won't that always be TRUE?

Ooops ...Yes ... I changed formula but obviously posted an earlier version...Careless

so if month is Jan to Match, then previous financial year, otherwise current financial year

=VLOOKUP(TEXT(IF(MONTH(TODAY())<=3,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY()),4,1)),"MMMM"),$CA:$CB,2,0) & TEXT(IF(MONTH(TODAY())<=3,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY()),4,1)),"[$-4000000]yyyy")

As per my post #2 !!

Hi John,
Thank you. It's correct for current fy and wrong for previous fy. Please see the attached workbook. The required output is in column 'BC'.

Thank you.

Hi Glenn,

Thank you. It's working fine for first month-year of previous year but how to get last month-year of previous fy? How to combine it with 'vlookup and text' formula to get output in local language? It's giving output in English.

Please Login or Register  to view this content.

Thank you.

If you re-read my post you will see that I said that I had used FOUR independent formulae. Did you look at the sheet in my previous post?

I stopped looking at this thread after John joined in. So, please open the sheet to see the FOUR formula, that are variants of this one:

=VLOOKUP(TEXT(IF(MONTH(TODAY())>=4,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY())-2,4,1)),"mmmm"),$CA$2:$CB$13,2,FALSE)&" "&TEXT(IF(MONTH(TODAY())>=4,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY())-2,4,1)),"[$-4000000]yyyy")

Can you see the Hindi script for all of the months?? I can only see April.

Hi Glenn,
Perfect! Thank you. It's working fine. I have not written April in Hindi so you can not see it. Now in my original workbook it's written; working fine.

Thank you to both of you for your kind assistance.

You're welcome!!

Hi Glenn,

Sorry for trouble. What change should I do in the following formula to get output 'March 2016' in local language?

Please Login or Register  to view this content.

I changed '-1' to '-2' but in vain so I posted it here for your assistance.

Thank you.

Post 12, BC3 = March 2016??? What is the problem??

@John,
Please don't be angry. I appreciate you for your time to time assistance to solve the problems. Yours are also correct suggestion.
Please don't mind.

@Glenn,
BC3 output is March 2017 I require March 2016.

Thank you to both of you and have a nice time.

I was a bit confused at Post 16. yes it does give March 2017. But... MSO3, surely you are WRONG. March 2017 is the correct answer.

We are NOW in the 17/18 financial year (remember the formula is based on TODAY's date). So the previous FY started in April 2016 (BC2) and ended in March 2017 (BC3). That is what you asked for (BA2 and BA3). If that is not what you had intended to ask for... then try again.

Hi Glenn,
Sorry for confusion. Yes, you are right. The formulas are correct as per my request/requirement. Now to get the second previous year balance I require the fy 'April 2015 March 2016' I require a formula which has nothing to do with the formulas you provided before. It's a totally new request which is a continuation of this post so I posted it here instead of starting a new thread for a single formula.

Hope it will clear your confusion.

Thank you.

MSO3 - in these circumstances, as it is a different request, you should start a new thread. By all means provide a link back to this one for reference, but make sure that you state your requirements in full in the new thread.

This thread will now be closed. Thanks for your co-operation.