Help with formula - summarize dates

**OBA** · 06-14-2017, 01:22 PM

Hi,

I have a spreadsheet that I use as my expense report.

At the top it has a cell with the DATE, then many expenses in a list each with thier own date.

With the below dataset I would like to solve both of following solutions:
*Note format is (MM/DD/YY)

03/22/16
04/01/15
06/14/16
01/29/16
11/03/15
05/20/16

Soluiton 1) for the DATE cell to be equal to the last of all the dates: 06/14/16
Solution 2) for the DATE cell to be equal to: earliest date-last date (ex. 04/01/15-06/14/16)

Anyone able to help?

Thanks!

**Raphaelp** · 06-14-2017, 01:29 PM

Solution 1: =TEXT(MAX(B3:B8),"MM/DD/YYYY")

Solution 2: =TEXT(MIN(G3:G8),"MM/DD/YYYY")&"-"&TEXT(MAX(G3:G8),"MM/DD/YYYY")

See attached.

**CK76** · 06-14-2017, 01:39 PM

Assuming all dates are actually stored as date value and not text.

Solution 1)
=MAX(Date Range)

Solution 2)
=TEXT(MIN(Date Range),"MM/DD/YY")&"-"&TEXT(MAX(Date Range),"MM/DD/YY")

If dates are in text, you'd need helper column added to data range.
=DATE("20"&RIGHT(A1,2),LEFT(A1,2),MID(A1,4,2))

See attached.