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

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

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

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

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

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

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

Originally Posted by Hawkmoth1

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."

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

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.

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.