+ Reply to Thread
Results 1 to 4 of 4

Formula to show the latest date in a given year from a column of dates

  1. 11-14-2018, 09:12 AM #1
    Mgc26133
    Mgc26133 is offline
    Forum Contributor
    Join Date
    11-13-2018
    Location
    Istanbul
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Formula to show the latest date in a given year from a column of dates

    Hello. I have a column of random dates ranging from 01/01/2002 to today. I need a cell in a different worksheet to show me the latest date in any given year. Using "Sort" takes too much time, and for various reasons I am unable to use pivot table for this, so I need to come up with a formula. For example if I want the latest date in 2009, I should change the year in formula to 2009. If I want the latest date in 2015, again I should just change the date in formula to 2015 and the cell would show me the latest date in 2015. The dates are stored in column A and I keep receiving new dates every other day, so you can consider the range A:A. The formulas I come up with either give me the latest date in this year, or 0. Any help will be appreciated.
    Reply With QuoteRegister To Reply
  2. 11-14-2018, 09:20 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Formula to show the latest date in a given year from a column of dates

    With the desired year in A1:

    =AGGREGATE(14,6,Sheet1!$C$1:$C$40/(YEAR(Sheet1!$C$1:$C$40)=A1),1)
    Attached Files Attached Files
    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
    Reply With QuoteRegister To Reply
  3. 11-14-2018, 09:51 AM #3
    Mgc26133
    Mgc26133 is offline
    Forum Contributor
    Join Date
    11-13-2018
    Location
    Istanbul
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Formula to show the latest date in a given year from a column of dates

    Works beautifully! Thank you very much
    Reply With QuoteRegister To Reply
  4. 11-14-2018, 10:00 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Formula to show the latest date in a given year from a column of dates

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to Look for value then for latest date then show value in third column
    By Jamidd1 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-12-2017, 11:16 AM
  2. [SOLVED] Show latest date in a column
    By guy13 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-06-2015, 04:51 AM
  3. [SOLVED] Check Contact Name in Column, Return Latest Date and 2 Cells Next to the Latest Date
    By nKife in forum Excel General
    Replies: 8
    Last Post: 04-08-2015, 10:10 PM
  4. [SOLVED] Require a formula to indicate the latest dates in a column
    By john dalton in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-11-2015, 04:16 AM
  5. Formula to calculate the percentage of a year using a start date and year to date dates
    By pammy66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2012, 05:14 AM
  6. Find Latest Date/Year Within a Column/Range of Dates
    By sable in forum Excel General
    Replies: 3
    Last Post: 08-11-2009, 11:46 AM
  7. formula at the top of the sheet to show the latest figure in column
    By karloss in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2009, 12:30 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1