formula to extract month and year

1. formula to extract month and year

my financial period runs from Oct to sept, where period 1= oct, 2 = Nov, 3 = Dec etc

I have extracted the period number from the file name as well as the year

I now need a formula to extract the month and year based on the period number and year extracted. I have manually extracted the Month & Year -see item in Yellow

I have attached sample data. The period in the example is 9 and the year is 2019, so I need June 2019 extracted in a date format

Your assistance in this regard is most appreciated

2. Re: formula to extract month and year

Whatwould a typical code look like in months 10-12 of your FY? How can we tell whether it's one digit or two that needs to be extracteed. SEVERAL representative samples are required.

3. Re: formula to extract month and year

Try this:

=DATE(MID(A\$1,FIND("_",A\$1)+1,4),MONTH(DATEVALUE(VLOOKUP(--MID(A\$1,FIND("_",A\$1)-1,1),\$C\$1:\$D\$12,2,0)&" 1")),1)

4. Re: formula to extract month and year

Hi Glenn

I have highlighted the period in below blue in the name of the file name which I have extracted and manually shown the Month and June applicable to the period

It would be appreciated if you could kindly set up a formula to extract the Month and Year, which should be displayed as for eg period 9 being June-2019 , 10 being July-2019 etc it should be able to be format use date format

5. Re: formula to extract month and year

This, then:

=DATE(MID(A\$1,FIND("_",A\$1)+1,4),MONTH(DATEVALUE(VLOOKUP(--MID(A\$1,FIND("_",A\$1)-4,2),\$C\$1:\$D\$12,2,0)&" 1")),1)

6. Re: formula to extract month and year

thanks Ali for your brilliant formula

There was an error in the File name, which I corrected and uploaded an amended file

Kindly amend your code accordingly and then I will try and then go through the formula to try and understand it

7. Re: formula to extract month and year

I have already amended it based on your second file (post #5).

8. Re: formula to extract month and year

Or:

=DATE(MID(A1,FIND("_",A1)+1,4),MID(A1,FIND("_",A1)-4,2)-3,1)

9. Re: formula to extract month and year

Or just this:

=DATE(MID(A1,FIND("_",A1)+1,4),MID(A1,FIND("_",A1)+5,2),1)

10. Re: formula to extract month and year

Does this work?
B6
=EDATE(REPLACE(MID(A1,5,4),3,,"/"),-3)

11. Re: formula to extract month and year

Hi Ali

I understand the formula, except the last portion of your formula

``Please Login or Register  to view this content.``

kindly explain what this does

12. Re: formula to extract month and year

The last 1 is the day argument:

=DATE(year,month,day)

The DATEVALUE trick is one I learnt a long time ago from here: https://www.extendoffice.com/documen...number.html#a1

It adds a " 1" to a month name typed into a cell, and in so doing, Excel sees it as a date instead of text and can then return the month number.

So in the formula, an entry of Jan will be converted to Jan 1 and therefore becomes a date.

13. Re: formula to extract month and year

Hi Bo_Ry

You formula works perfectly

please explain how the whole formula works to extract the required data.

14. Re: formula to extract month and year

It's clever!

Just use the Evaluate Formula feature on the Formulas ribbon (as I did just now) and you will see exactly what it's doing.

15. Re: formula to extract month and year

Thanks for the help, Phuocam

16. Re: formula to extract month and year

Thanks Ali, I have used the evaluate formula feature, but still don't understand the section in the formula highlighted in colour ,3,, ….

``Please Login or Register  to view this content.``

17. Re: formula to extract month and year

This bit:

REPLACE(...,3,,"/")

is looking at this (returned by the MID function): 1019

REPLACE is looking for the 3rd character and instead of replacing it, it is adding / before it (this is done by leaving the 2nd argument blank, hence the two commas. This is the clever bit!

This gives you 10/19 (or October 2019).

EDATE(..., -3) takes you thee months back to July 2019.

18. Re: formula to extract month and year

Thanks very much for the explanation. It is now crystal clear

19. Re: formula to extract month and year

I've popped this one onto my notebook.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1