+ Reply to Thread
Results 1 to 6 of 6

Formula for matching year value with date value and generating the result

  1. #1
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    167

    Formula for matching year value with date value and generating the result

    Hi gurus and experts,

    I am trying to create a formula that will match year value from date and return the result for instance:

    Date Value Year (Input) Value (Output needed by formula)
    01/01/2016 367.1 2020 451.67
    01/01/2017 386.7 2019 429.87
    01/01/2018 410.95 2018 410.95
    01/01/2019 429.87 2017 386.7
    01/01/2020 451.67 2016 367.1

    The output is my requirement in the fourth column.

    I would appreciate for your advice.

    Thank you
    Roshan

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,511

    Re: Formula for matching year value with date value and generating the result

    Try this in D2:

    =SUMIFS(B:B,A:A,">="&DATE(C2,1,1),A:A,"<="&DATE(C2,12,31))

    Then copy down as required.

    I see that all your dates are for the first of January, so if that is true for all your data then a simpler approach would be:

    =SUMIF(A:A,DATE(C2,1,1),B:B)

    Hope this helps.

    Pete

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,415

    Re: Formula for matching year value with date value and generating the result

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    167

    Re: Formula for matching year value with date value and generating the result

    Many thanks @Pete_UK. That is fabulous.

    Thank you
    Roshan

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,415

    Re: Formula for matching year value with date value and generating the result

    @Pete: just too quick for me! I assumed, from the sample data, there was only one entry per year

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,511

    Re: Formula for matching year value with date value and generating the result

    Could be, Trevor, but then you could use VLOOKUP.

    Pete

+ 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. FIND matching date generating Runtime Error 91
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-24-2019, 05:22 PM
  2. generating year of birth based on date and age
    By isawalha in forum Excel General
    Replies: 6
    Last Post: 06-29-2016, 07:07 AM
  3. [SOLVED] formula that if the year is 2015 the cell i input the formula it wil result a year of 2015
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-12-2015, 04:49 AM
  4. Deference Between Two Date and result in a year
    By midoo77 in forum Excel General
    Replies: 5
    Last Post: 02-10-2013, 07:26 PM
  5. Replies: 3
    Last Post: 08-14-2012, 05:14 AM
  6. Replies: 1
    Last Post: 06-09-2006, 12:45 PM
  7. Result of date as Month-2 digit Year
    By Linda in forum Excel General
    Replies: 3
    Last Post: 06-04-2005, 12:05 PM

Tags for this Thread

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