+ Reply to Thread
Results 1 to 19 of 19

formula to extract month and year

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    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.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    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)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: formula to extract month and year

    Hi Glenn

    I made a error.

    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
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    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. #6
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: formula to extract month and year

    I have already amended it based on your second file (post #5).
    Last edited by AliGW; 07-25-2019 at 10:03 AM.

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: formula to extract month and year

    Or:

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

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    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. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: formula to extract month and year

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

  11. #11
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: formula to extract month and year

    Hi Ali


    Your formula is working perfectly


    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. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    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.
    Last edited by AliGW; 07-25-2019 at 10:36 AM.

  13. #13
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    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. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    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. #15
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: formula to extract month and year

    Thanks for the help, Phuocam

  16. #16
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    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. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    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. #18
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: formula to extract month and year

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

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: formula to extract month and year

    I've popped this one onto my notebook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extract month number and year
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-28-2019, 12:28 PM
  2. [SOLVED] How to extract year and month from a date and combine them into a text
    By billj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2017, 09:24 AM
  3. Extract month and year from a cell
    By Wojtek-AU in forum Excel - New Users/Basics
    Replies: 24
    Last Post: 08-15-2016, 02:56 AM
  4. [SOLVED] Extract Year, Month, Day from date
    By stoicy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-28-2016, 11:48 PM
  5. Extract Year, Month, Day from date
    By stoicy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2016, 01:41 PM
  6. [SOLVED] Extract 2 corresponding fields by Date/Month/Year selection
    By suchetherrah in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2015, 08:31 AM
  7. [SOLVED] Formual to Extract month and year from sheet name
    By rizmomin in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-26-2014, 10:49 PM

Bookmarks

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