+ Reply to Thread
Results 1 to 10 of 10

Latest Date in Any Month

Hybrid View

andrewc Latest Date in Any Month 08-11-2015, 05:00 AM
Pete_UK Re: Latest Date in Any Month 08-11-2015, 05:07 AM
andrewc Re: Latest Date in Any Month 08-11-2015, 05:09 AM
Pete_UK Re: Latest Date in Any Month 08-11-2015, 05:17 AM
andrewc Re: Latest Date in Any Month 08-11-2015, 05:24 AM
Pete_UK Re: Latest Date in Any Month 08-11-2015, 05:27 AM
Pete_UK Re: Latest Date in Any Month 08-11-2015, 05:29 AM
Pete_UK Re: Latest Date in Any Month 08-11-2015, 05:31 AM
  1. #1
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    839

    Latest Date in Any Month

    Hi,

    I have in column A a time series of consecutive daily dates covering around 5 years.

    Can someone please suggest a formula that will return the latest date represented in column A for each month and year (e.g. 29 October for the month of October 2011 if column A contains the dates 1 to 29 October 2011) in the series.

    Thanks very much!

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

    Re: Latest Date in Any Month

    How do you want this displayed? Do you have a separate list of month and year (in column B?) and you want the latest date in column C, or do you want the latest date to appear in column B against all dates?

    Pete

  3. #3
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    839

    Re: Latest Date in Any Month

    Hi,

    I'd prefer the latest date to appear in column B against all the dates.

    Sorry I wan't clear earlier!

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

    Re: Latest Date in Any Month

    Okay, try this array* formula in B1:

    =MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A$1:A$1500),A1))

    Format as a date in the style you want to see, then copy down.

    *An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter, instead of the usual <Enter>.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    839

    Re: Latest Date in Any Month

    Thanks for the quick reply, Pete.

    The formula doesn't seem to work - apparently there are too few arguments?

    Thanks

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

    Re: Latest Date in Any Month

    Sorry, I missed a bit. Try this instead:

    =MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A$1:A$1500,"mmyyyy"),A1))

    Still need to commit it with CSE.

    Hope this helps.

    Pete

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

    Re: Latest Date in Any Month

    No, still not right - try this instead:

    =MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A1,"mmyyyy"),A1))

    That should do it.

    Pete

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

    Re: Latest Date in Any Month

    Agh! I'm not thinking clearly this morning. This what it should be:

    =MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A1,"mmyyyy"),A$1:A$1500))

    Use CSE to commit, then copy down.

    Hope this helps. (Think I need a coffee)

    Pete

  9. #9
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    839

    Re: Latest Date in Any Month

    Thanks works a treat, thanks very much!!!

    Enjoy the coffee

+ 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. Replies: 8
    Last Post: 04-08-2015, 10:10 PM
  2. Replies: 18
    Last Post: 09-08-2014, 05:06 PM
  3. Replies: 9
    Last Post: 11-28-2013, 05:20 PM
  4. [SOLVED] Pivot Table Not Grouping by Month for Latest Month (groups > 7/20/2013)
    By justforthis1 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 09-04-2013, 12:28 PM
  5. Powerpivot - Returning latest update based on latest date
    By Kehjz in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-22-2013, 02:45 PM
  6. Copy latest date of every month from a column in excel 2007
    By rt99 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-07-2013, 11:49 AM
  7. [SOLVED] get the latest day of the previous month
    By Laurent M in forum Excel General
    Replies: 2
    Last Post: 01-26-2005, 12:06 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