+ Reply to Thread
Results 1 to 30 of 30

Formula to extract the year from the date based on a condition ?

  1. #1
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Formula to extract the year from the date based on a condition ?

    How can I write the formula keep to the current year 2018 same until 10/1/2019 ? When the date hits 10/1/2019, the year should become 2019. Similarly when the date hits 10/1/2020, the year becomes 2019.

    Example below:

    HTML Code: 

  2. #2
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    Did you try this:

    =YEAR(A1)

    Or maybe this:

    =IF(MONTH(A1)>9,YEAR(A1)+1,YEAR(A1))
    Last edited by AliGW; 10-25-2018 at 04:18 PM.
    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.

  3. #3
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    The year becomes 2019 for the date 10/1/2018 when I use your formula. I need to keep the year as 2018 until 9/30/2019. On 10/1/2019, the year becomes 2019.

  4. #4
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    It shouldn’t, since the formula is looking for a month LARGER than 9. Did you actually try it? I can’t at present as I am away from my PC.

  5. #5
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    I did try it. The year is showing up as 2019 using your formula when I use the date 10/1/2018 as A1 cell value.

  6. #6
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    It shouldn’t. Can you attach a file?

  7. #7
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by donny007 View Post
    keep to the current year 2018 same until 10/1/2019 ? When the date hits 10/1/2019, the year should become 2019. Similarly when the date hits 10/1/2020, the year becomes 2019.
    Hi,

    Above in Red, assuming you meant 2020:

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    1
    Date Year
    2
    9/1/2018
    2017
    3
    10/1/2018
    2018
    4
    11/1/2018
    2018
    5
    12/1/2018
    2018
    6
    1/1/2019
    2018
    7
    2/1/2019
    2018
    8
    3/1/2019
    2018
    9
    4/1/2019
    2018
    10
    5/1/2019
    2018
    11
    6/1/2019
    2018
    12
    7/1/2019
    2018
    13
    8/1/2019
    2018
    14
    9/1/2019
    2018
    15
    10/1/2019
    2019
    16
    11/1/2019
    2019
    17
    12/1/2019
    2019
    18
    1/1/2020
    2019
    19
    2/1/2020
    2019
    20
    3/1/2020
    2019
    21
    4/1/2020
    2019
    22
    5/1/2020
    2019
    23
    6/1/2020
    2019
    24
    7/1/2020
    2019
    25
    8/1/2020
    2019
    26
    9/1/2020
    2019
    27
    10/1/2020
    2020
    Sheet: Sheet121

    Excel 2016 (Windows) 64 bit
    C
    1
    Year
    2
    =IF(OR(MONTH(A2)={10,11,12}),YEAR(A2),YEAR(A2)-1)
    Sheet: Sheet121

    Formula copied down.

  8. #8
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    Sorry - I got it wrong. Give me a minute.

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

    Re: Formula to extract the year from the date based on a condition ?

    Never mind - you have a solution now.

  10. #10
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    Alternatively:

    =IF(MONTH(A1)>9,YEAR(A1),YEAR(A1)-1)

  11. #11
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Thanks jtakw! The condition is actually like this

    If year = '2018' and month(date) in (10,11,12) then year = '2018'
    If year = '2019' and month(date) in (01,01,03,04,05,06,07,08,09) then year = '2019'

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

    Re: Formula to extract the year from the date based on a condition ?

    I believe that is what Jtakw’s and my last formula both do.

  13. #13
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by AliGW View Post
    Alternatively:

    =IF(MONTH(A1)>9,YEAR(A1),YEAR(A1)-1)
    Thanks! Your formula still shows 2018 for 2019 Jan & Feb dates.

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

    Re: Formula to extract the year from the date based on a condition ?

    Yes ... and? It’s not supposed to show 2019 until October, is it?

  15. #15
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by AliGW View Post
    Yes ... and?
    I apologize if I'm not clarifying the requirement correctly. Here is the actual requirement.

    The year should be 2019 for January through September 2019 when the current year is 2018 and months in (10,11,12)
    The year should be 2020 for January through September 2020 when the current year is 2019 and months in (10,11,12)

  16. #16
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    No, sorry - you’ve lost me.

  17. #17
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by donny007 View Post
    If year = '2018' and month(date) in (10,11,12) then year = '2018'
    If year = '2019' and month(date) in (01,01,03,04,05,06,07,08,09) then year = '2019'
    This is different then the description in your OP post #1, post another sample.

    As your can see, my sample in Post # 7 Matches your sample in OP.

  18. #18
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    So does my solution in post #10. I think Donny needs to define what exactly he means by ‘current year’.

  19. #19
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by jtakw View Post
    This is different then the description in your OP post #1, post another sample.

    As your can see, my sample in Post # 7 Matches your sample in OP.


    Apologize again. Let me explain it clearly.

    My formula shows MM-YY as 10-18 using this formula =TEXT(DATE(YEAR(TODAY()),10,1),"MM-YY")

    The problem is I want the 10-18 header to remain same until next year 10/01/2019 but using the formula above I'm afraid that Year(Today()) will change to 2019 by 01/2019. I want 10-18 to be 10-18 until 10-19. When the date hits 10/01/19, the MM-YY header should be automatically rollover to 10-19.

  20. #20
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    How about this?

    =IF(MONTH(TODAY())>9,TEXT(DATE(YEAR(TODAY())-1,10,1),"MM-YY"),TEXT(DATE(YEAR(TODAY()),10,1),"MM-YY"))

  21. #21
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Formula to extract the year from the date based on a condition ?

    Your last description in Post # 19 is completely different than your OP.

    Replace your current formula to this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by AliGW View Post
    How about this?

    =IF(MONTH(TODAY())>9,TEXT(DATE(YEAR(TODAY())-1,10,1),"MM-YY"),TEXT(DATE(YEAR(TODAY()),10,1),"MM-YY"))


    It shows 10-17 for the current month instead of 10-18.

  23. #23
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    Oh, I give up! Sorry, it’s getting late and I’m not following the twists and turns here. I hope you get it sorted. Goodnight, all!

  24. #24
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Formula to extract the year from the date based on a condition ?

    Goodnight AliGW zzzzzzzzzz...

  25. #25
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Formula to extract the year from the date based on a condition ?

    I am just wondering, did you try my formula in Post # 21 ??

  26. #26
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by jtakw View Post
    Your last description in Post # 19 is completely different than your OP.

    Replace your current formula to this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thank you! The formula works but I have to wait till January 1st,2019 to make sure it works exactly how it should.

  27. #27
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by AliGW View Post
    Oh, I give up! Sorry, it’s getting late and I’m not following the twists and turns here. I hope you get it sorted. Goodnight, all!
    Good Night AliGW!

  28. #28
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Formula to extract the year from the date based on a condition ?

    Quote Originally Posted by donny007 View Post
    Thank you! The formula works but I have to wait till January 1st,2019 to make sure it works exactly how it should.
    LOL, No you don't, just change the TODAY() reference in the formula to a Cell reference to Test, like this (You can put ANY date you want in Column A for testing):

    Excel 2016 (Windows) 64 bit
    A
    B
    I
    J
    1
    Date 10-18
    2
    9/1/2018
    10-17
    3
    10/1/2018
    10-18
    4
    11/1/2018
    10-18
    5
    12/1/2018
    10-18
    6
    1/1/2019
    10-18
    7
    2/1/2019
    10-18
    8
    3/1/2019
    10-18
    9
    4/1/2019
    10-18
    10
    5/1/2019
    10-18
    11
    6/1/2019
    10-18
    12
    7/1/2019
    10-18
    13
    8/1/2019
    10-18
    14
    9/1/2019
    10-18
    15
    10/1/2019
    10-19
    16
    11/1/2019
    10-19
    17
    12/1/2019
    10-19
    18
    1/1/2020
    10-19
    19
    2/1/2020
    10-19
    20
    3/1/2020
    10-19
    21
    4/1/2020
    10-19
    22
    5/1/2020
    10-19
    23
    6/1/2020
    10-19
    24
    7/1/2020
    10-19
    25
    8/1/2020
    10-19
    26
    9/1/2020
    10-19
    27
    10/1/2020
    10-20
    Sheet: Sheet121

    Excel 2016 (Windows) 64 bit
    I
    J
    1
    =TEXT(DATE(YEAR(TODAY())-IF(MONTH(TODAY())>9,0,1),10,1),"MM-YY")
    2
    =TEXT(DATE(YEAR(A2)-IF(MONTH(A2)>9,0,1),10,1),"MM-YY")
    Sheet: Sheet121

    Copy J2 formula down Column to test...

  29. #29
    Registered User
    Join Date
    10-24-2018
    Location
    Chicago,IL
    MS-Off Ver
    2007
    Posts
    42

    Re: Formula to extract the year from the date based on a condition ?

    Awesome! Thank you very much. I will mark this thread as resolved now.

  30. #30
    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,420

    Re: Formula to extract the year from the date based on a condition ?

    Morning, all! Glad to see you got there in the end.

+ 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. [SOLVED] Help with formula to return a value based on if that expiry date is within year
    By mw91 in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 01-08-2015, 11:37 AM
  2. [SOLVED] Count current year entries based on date and name formula
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-05-2014, 08:09 PM
  3. Replies: 5
    Last Post: 10-26-2012, 05:43 AM
  4. [SOLVED] Date formula to display day based off of year
    By Sovren in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-21-2012, 10:13 PM
  5. Replies: 3
    Last Post: 08-14-2012, 05:14 AM
  6. formula to return a year based on a date range
    By bankster in forum Excel General
    Replies: 12
    Last Post: 03-30-2012, 04:24 AM
  7. condition formula based on date
    By pervez in forum Excel General
    Replies: 3
    Last Post: 12-20-2011, 04:18 AM

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