+ 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,164

    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 2212
    Posts
    37,018

    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



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

    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

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

    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
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    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,164

    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
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    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,584

    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
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    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,090

    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,164

    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
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    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,164

    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
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    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,164

    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,164

    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
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    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,164

    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
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    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