Find last cell with data entered and combine with first cell (date range)

[psuedodragon]

I tried searching through the forum for a problem/solution already posted similar to mine and while I found a few, the solutions posted either give me an error or don't return the value I need.


So, specifically, I'm looking to combine the first and last dates to form a date range in a separate cell. (I want it to look like "November 1, 2013 - December 1, 2013") I've got a CONCATENATE formula set up and while it grabs the data from two cells and combines them for me how I want, I can't figure out how to get it to search for the last date listed in the column. I've tried some MATCH and COUNTIF formulas and like I mentioned above, I either get an error or the data returned is different from what I want displayed.


Thanks in advance!

 

[Jonmo1]

For finding the last date...

Are the dates sorted in ascending order?
If so, you can just use the MAX function.
=MAX(A:A)


to concatenate a date that is in a real date format, you need to use the text function..

=CONCATENATE(TEXT(A1,"mmmm d, yyyy")," - ",TEXT(A10,"mmmm d, yyyy"))

 

[psuedodragon]

Thanks actually just found the mmm-dd-yy thing on Microsoft's Excel formula help pages (on a completely unrelated formula page, but eh).

What if the dates aren't sorted? Is there a way to force a column to auto-sort?

 

[wrightyrx7]

WHen you said last date in column do you mean "the most recent" or "the last one in the list (one at the bottom of the column)"

 

[psuedodragon]

Most recent

 

[wrightyrx7]

Lets say your dates are in columns B. Try this

=CONCATENATE(TEXT(MIN(B:B),"mmmm d, yyyy")," - ",TEXT(MAX(B:B),"mmmm d, yyyy"))

This SHOULD be "Earliest Date - Most Recent Date"

 

[psuedodragon]

Thanks so much!

 

[wrightyrx7]

Your welcome