Min and Max dates based on a string

**geelsu** · 06-13-2014, 08:20 AM

I'm having trouble finding min and max dates based on a string.

Example Table:

---A-----------B------------C----D-------------------------------E----------------F-------------------G
1 Date-------Offline---------Systems Noted Offline----Recurrance--Observed First-Observed Last
2 10-Jun-14--System1---------System1-----------------------3----------------10-Jun-14--------12-Jun-14
3 10-Jun-14--System2---------System2-----------------------2
4 11-Jun-14--System1---------System3-----------------------1
5 11-Jun-14--System2
6 12-Jun-14--System1
7 12-Jun-14--System3

(Sorry for all the dashes. Just trying to control the spacing.)
I used Data -> Advanced to get the unique list in D, and =COUNTIF to get the Recurrance in E. Now I am trying to figure out F and G. For Example, F should show System1 Observed First on 10-Jun-14 and Last on 12-Jun-14. I thought maybe =SMALL and =MAX would help if used in conjunction with some sort of string lookup function, but I haven't hit on the correct function or syntax yet. I've explored =VLOOKUP a bit, but I'm not sure that is the way to go. This spreadsheet example, of course, is only a small subset of a much larger one with dozens of systems and dates.

Any ideas would be greatly appreciated.
Kind regards to all for you ideas.

**Tony Valko** · 06-13-2014, 08:25 AM

Can you post a SMALL sample file and show us what results you expect?

20 rows worth of data is plenty.

**geelsu** · 06-13-2014, 08:53 AM

Ok. I'll have to recreate a small example on another system. The production spreadsheet is on a private network not connected to the Internet.

**geelsu** · 06-13-2014, 09:16 AM

Attached is a small example.

**Tony Valko** · 06-13-2014, 10:58 AM

I was hoping you would fill in those ?'s with the results you expect!

Here's what I think you want.

Data Range

	D	E	F	G	H
1	Systems Noted	Recurrance	------	First Date Observed	Last Date Observed
2	System1	9		6-Jun-14	14-Jun-14
3	System2	11		6-Jun-14	16-Jun-14
4	System3	8		13-Jun-14	20-Jun-14

This array formula** entered in G2:

=MIN(IF(B$2:B$29=D2,A$2:A$29))

Format in the date style of your choice.

This array formula** entered H2:

=MAX(IF(B$2:B$29=D2,A$2:A$29))

Format in the date style of your choice.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select G2:H2 and copy down as needed.

**geelsu** · 06-13-2014, 01:05 PM

That is exactly what I needed. Thank you very much. Now I have to figure out why some of the dates are showing a perfect 12-May-2014, for example, and other cells are showing 0-Jan-00. Arrgghh. It is always something. I've gone through to make sure date cells are date formatted and text cells are text formatted and such. Must be something simple I am missing.

**Tony Valko** · 06-13-2014, 01:30 PM

A result of 0-Jan-00 is actually 0 with a date format applied.

That means the formula returned 0. There are a couple of reasons that could happen. Either that system is not in the list or the cells that do correspond to a system are empty (no dates entered).

**geelsu** · 06-13-2014, 01:57 PM

Not sure what is going on. If I take one of the systems B8TVWV1 (simply the Dell Service tag), I see 6 recurrances, with a date range of 19-May-2014 through 23-May-2014. But the Min and Max show up as 0. I've repeated the Filter -> Advanced -> Copy to a new location (to a real new location i.e. not in the D column) and got the same results. Alas.

**Tony Valko** · 06-13-2014, 03:09 PM

Are you sure the dates are true Excel dates?

In Excel dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial number is the count of days starting from a base date. In PC Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2014 = date serial number 41640

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

You can test a date like this:

A1 = some date

=ISNUMBER(A1)
=COUNT(A1)

If A1 contains a true date ISNUMBER will return TRUE and COUNT will return 1.

**geelsu** · 06-20-2014, 09:19 AM

Hey all. Thank you for all your excellent help. Here is a second mystery which I guess was the real mystery all along. I am still getting the 0-Jan-00 date. Tony, your ISNUMBER and COUNT return the correct values. So, I started using the Formula --> Evaluate Formula tool. And this is where I found a problem I cannot seem to fix. I will use my badly fomatted table above as an example.

If I evaluate F2 I get a TRUE result. It says "TRUE", the value in D2 is found in Column B and the Min date is 10-Jun-14 which is displayed, as a result, in F2.

Next, System2, evaluates correctly and the min date 10-Jun-14 appears in F3 (which I don't show in my table, but pretend it is there.)

Then all of a sudden I evaluate for a value to appear in F4 which I expected to see 12-Jun-14 for System3, but get 0-Jan-00. The evaluation tool evaluates as follows:

MIN(IF(B2:B50=D4,A2:50))
MIN(IF("System1"=D4,A2:50))
MIN(IF("System1"="System3",A2:50))
MIN(IF("False,A2:A50))
MIN(FALSE)

For some reason, all of a sudden, it starts using ONLY the D2 value of System1 instead of the value I am asking for which is D4. And all subsequent evaluations do the same i.e. D5, D6, D7 and so on.

In my real spreadsheet, things work great for about 4 row (i.e. 4 evaluations). Then the formula starts using the D2 value over and over no matter what I do. Believe me I've tried all sorts of things to make it work to no avail. I've hand typed the value, I have hand typed the formula, vice cutting and pasting, I've used Forumula --> Show Formulas and everything looks ok. I am at a loss. Any ideas? Anyway, beside Formula Evaluate, to look behind the scenes a little more deeply?

**Tony Valko** · 06-20-2014, 09:28 AM

Does the sample file demonstrate these issues or is it just in your real file?

**geelsu** · 06-20-2014, 09:52 AM

I've updated the sample spreadsheet to use the MIN and MAX formulas. Yes, it happens here too. See H4. I also just noticed that MAX is not returning the correct value. System1 should MAX at 14-Jun-14. System2 should MAX at 16-Jun-14.

**Tony Valko** · 06-20-2014, 10:01 AM

A couple of issues.

You didn't enter the formulas as array formulas and you didn't make the range references row absolute.

Array entered** in G2:

=MIN(IF(B$2:B$30=D2,A$2:A$30))

Array entered** in H2:

=MAX(IF(B$2:B$30=D2,A$2:A$30))

**Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

Copy down as needed.

**geelsu** · 06-20-2014, 10:35 AM

I hang my head in shame!!!!! You even mentioned the array thing early on and I got so tied up in the details that I missed/forgot it.

Soooo....tell me where to send the gift card and letter of appreciation.

**Tony Valko** · 06-20-2014, 10:43 AM

No problem.

I forget to do stuff all the time even though I've done them a million times!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

**Tony Valko** · 06-20-2014, 02:55 PM

Here's an alternative to the array formulas.

Must be using Excel 2010 or later.

Normally entered in G2:

=AGGREGATE(15,6,A$2:A$30/(B$2:B$30=D2),1)

Format as Date

Normally entered in H2:

=AGGREGATE(14,6,A$2:A$30/(B$2:B$30=D2),1)

Format as Date

Min and Max dates based on a string

LinkBack

Thread Tools

Rate This Thread

Display

Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Re: Min and Max dates based on a string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need 2 formulas please 1. For dates covered based on admit 2. Include dates for one month

[SOLVED] Auto filling dates based on previously entered dates and averaging numbers if dates equal

[SOLVED] Calculating a future dates based on dates or birth.

Based on col dates create col of dates for the first day of that same month and year

Excel 2007 : fill formating cells based on 2 dates (beginning and ending dates)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions