+ Reply to Thread
Results 1 to 21 of 21

Return the earliest and latest dates selected from a list of dates.

  1. #1
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Return the earliest and latest dates selected from a list of dates.

    Hello Everyone,

    I'm having issue with returning earliest and latest dates for each species recorded - could I please ask you to take a look at the issue on the attached file.

    More details in red on on row 2550 of the attached file on the 'index' worksheet, thanks.

    Hawkmoth1
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    Your choice of a sample sheet hasn't helped produce a final answer. Is there one big sheet containing neither micro nor macro moths that you have omitted?

    I just applid a formula to the four filtered cells. Both are array formulae:

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


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


    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 01-30-2017 at 10:57 PM.
    Glenn



  3. #3
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Return the earliest and latest dates selected from a list of dates.

    Using the species Latin names in col E as unique identifiers, with the real dates data in col K, here's one way

    Get Earliest date in say, 2016 (earliest date == smallest date number for the specified year)
    In "index", in G493, array entered (press CTRL+SHIFT+ENTER to confirm):
    =MIN(IF((('Macro Moths'!$E$4:$E$15=E493)*(YEAR('Macro Moths'!$K$4:$K$15)=2016))>0,'Macro Moths'!$K$4:$K$15))

    Get Latest date in say, 2016 (latest date == largest date number for the specified year)
    In "index", in H493, array entered (press CTRL+SHIFT+ENTER to confirm):
    =MAX(IF((('Macro Moths'!$E$4:$E$15=E493)*(YEAR('Macro Moths'!$K$4:$K$15)=2016)),'Macro Moths'!$K$4:$K$15))
    -----------------------------------------------------------------------
    Above any good? Wave it, hit the little star at the bottom left

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    Incidentally, one of your dates is clearly wrong. Ir was in 1014. I changed it to 2014. I assume that the text entries incl ealiest and latest will always be there? If not, it can still be done, but will need a helper column.

    Let me know.

  5. #5
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Return the earliest and latest dates selected from a list of dates.

    To Glenn – thanks very much, your offering does seem to work. However rather than having to pre fill columns G & H on the ‘index’ worksheet with formula, is there any way that the formula can be automatically generated once a new species is added to the ‘index’ worksheet, thanks,

    To Max – your option also worked, but only for records for a specific year, I need the earliest date across all years, thanks.

    Hawkmoth1

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    Are you actually using Excel 2003?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    I have modified the formula and copied it to all current rows in INDEX. The formula will work on both the micro- and macro-moth sheets (and ANY other sheets that you need to add) down as far as Row 3000. You cannot simply have the formula "appear", if I understand your question correctly. I have had to zip it to meet forum size limitations. It would be smaller if you were using Excel 2007 or later...
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Return the earliest and latest dates selected from a list of dates.

    Re Glenn's' suggested formula - I have just noticed that when a new species is added to the system that I get a #N/A showing on appropriate cell of columns G & H of the 'Index' worksheet when your formula is entered - am I doing something wrong here?

    Hawkmoth1

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    Where did you add what? Be explicit, please!!

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    add the new species to the latest version of the sheet that I posted.

  11. #11
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Return the earliest and latest dates selected from a list of dates.

    OK, this is the procedure when a new species is added to the system :-

    A new record, which may or may not be a new species is added to bottom of the list of species names in column D of the 'Macro moths' worksheet or in column E of the 'Micro moths' worksheet. This information is then automatically loaded into the 'index' worksheet where the user can view various options by selecting the filter in cell A2 to view all species found in the UK, to various other options of what the species he has recorded. I hope this helps you.

    I have used the file you sent me for working on, but I'm still getting #N/A showing on appropriate cell of columns G & H of the 'Index' worksheet when your formula is entered into the cell of the earliest and latest columns of a newly added species.

    Just an idea, if this is the easiest option. Could you possibly expand the 'index' worksheet by selecting 'All' in the filter in cell A2, then enter your formula in columns G & H down to the bottom of my list then resend me the file, thanks.

    Hawkmoth1

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    The formula that you are using on macro moth sheet only went down as far as row 946 on Index. That probably explains the error (and not one in my formula!!). Try it now. I have added tow macro moths from near the top of INDEX. seems OK to me.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Return the earliest and latest dates selected from a list of dates.

    The date still doesn't load for a newly added species, but I have just realized why this is happening - where I have entered earliest and latest in column J of the 'Macro moths' and 'Micro moths' worksheets this was just for your information so that you could quickly check whether your formula was correct or not, the words 'earliest' and 'latest' would never usually be in these columns.

    Sorry about any confusion I may have caused on this.

    Hawkmoth1

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    You should read, and answer, the questions that we ask you. Itvsaves a lotbof time. See my Q at post 4. I do lnow how tpbsolve this and will dons5o tomorrow. It's late evening here (Asia). Back in the morning.

  15. #15
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Return the earliest and latest dates selected from a list of dates.

    Hello Glenn - so sorry, I did miss your comment "I assume that the text entries incl earliest and latest will always be there" - it's good to hear that you're still on the case, thanks.

    Hawkmoth1

  16. #16
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Return the earliest and latest dates selected from a list of dates.

    Quote Originally Posted by Hawkmoth1 View Post
    To Max – your option also worked, but only for records for a specific year, I need the earliest date across all years, thanks.
    Hawkmoth1
    Yup that's correct, as that's what I thought you specified in your file:
    " ... so that it will select the earliest and latest dates that each species has been recorded. By earliest and latest I mean earliest and latest in the year and not the earliest and latest years."

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    In a case like this, first sighting of a species:

    13/3/2015: day 72 of 2015 (a non-leap year) and also
    12/3/2016: day 72 of 2016 (a leap year)

    which date do you want to get back as the earliest?

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    OK. I think I have resolved this (finally). I went for the 12/3/16 solution in the end. Two helper columns (U and V on macro- and micro- sheets (these can be hidden, if needed).
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Return the earliest and latest dates selected from a list of dates.

    Many thanks for every ones input and to Glenn - it seems as though you've cracked this, thanks very much, there is certainly some mega formula you have used to get the desired result.

    Could I please ask you for another formula which will go into columns J & K of the 'index' worksheet. Against each species recorded I would like to enter a formula (again hidden until a species has been recorded) to show the figure which will state how many times each species has been recorded e.g. on the file you last sent back to me 'GK V4' the figure for the Small Purple-barred should read 6 in column J of the 'index worksheet 'Macro species recorded', thanks.


    Hawkmoth1

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Return the earliest and latest dates selected from a list of dates.

    Here you go. If there are parts of the formulae you can't figure out; just ask!!

    For now, though, I'm glad to have helped! 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.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Return the earliest and latest dates selected from a list of dates.

    Hello Glenn,

    Thanks, the count formulae does exactly what I wanted.

    Many thanks for all your hard work in helping me to sort out the formula issues - I shall now spend a bit of time checking the program, before it get used by any of my recorders.

    Hawkmoth1.

+ 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. Help with sorting dates from earliest to latest
    By probuddha in forum Excel General
    Replies: 11
    Last Post: 06-15-2015, 03:53 PM
  2. [SOLVED] Finding the Earliest and Latest Dates for each Task Code
    By BenTFleury in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-07-2014, 03:45 PM
  3. Formula - Determining Earliest and Latest dates for a Category of records
    By haleakala17 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2014, 11:35 AM
  4. Find 5 latest dates and return values for each
    By Fett2oo5 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2013, 05:21 PM
  5. [SOLVED] Return latest Date from cell containing several dates?
    By Motox in forum Excel General
    Replies: 2
    Last Post: 09-10-2012, 07:26 AM
  6. Earliest and Latest Dates from a list of names?
    By TimMatrix in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2008, 07:34 PM
  7. [SOLVED] Return the latest date from a list of dates
    By davidp in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-17-2005, 09: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