Solved

Minimum and Maximum Dates

Posted on 2011-03-09
29
421 Views
Last Modified: 2012-05-11
Hi:

Attached is an Excell SS with a simple column of dates, headed at the top with example "results" of what I need two Excel formulas for.

I simply need formulas for determining the earliest month & year -- and the latest month & year --from the column, and return the results in the format, for example, "December, 2010"

Bear in mind the column of dates will always be a different number of rows. In one reporting period there may be 50 rows of dates, in others it may be 200 rows of dates. The first row will always be fixed but the last row, obviously, will always be different based on the varying number of dates.

Thanks in advance!

Dates.xlsx
0
Comment
Question by:Cactus1994
  • 10
  • 6
  • 5
  • +3
29 Comments
 
LVL 8

Expert Comment

by:Sean Scissors
ID: 35086513
One possible solution could be instead of inserting the data as text insert it with the cells to be formatted in the "date" format. That should then be able to determine the actual date, based on month, day, and year and sort it accordingly.
0
 
LVL 8

Expert Comment

by:Sean Scissors
ID: 35086584
Also if you wanted to add the time you would have to create a custom format. The following should work in your case. Give it a go, simply highlight all of the cells and right-click and go to Format Cells. Choose "Custom" on the left hand side and in the dialogue box copy this in "m/d/yyyy h:mm AM/PM" without the quotes. That should work.
0
 

Author Comment

by:Cactus1994
ID: 35086607
The data comes from an SQL query and loads into a spreadsheet as in the attached example, so it can't be inserted as text. Thus, I need formulas for determing beginning and end months based on the dates, as entered. Currently, I have for the end period:

=TEXT(DATEVALUE(LEFT(A4,3)&" " &MID(A4,FIND(" @",A4)-4,4)),"mmmm, yyyy")

I mostly need the ending month & year, but am not sure how to do this with varying numbers of rows based on varying number of dates.
0
 

Author Comment

by:Cactus1994
ID: 35086663
CORRECTION: The data comes from an SQL query and loads into a spreadsheet as in the attached example, so it can't be inserted as text. Thus, I need formulas for determing beginning and end months based on the dates, as entered. Currently, I have for the end period:

=TEXT(DATEVALUE(LEFT(A4,3)&" " &MID(A4,FIND(" @",A4)-4,4)),"mmmm, yyyy")

I mostly need the BEGINNING month & year, but am not sure how to do this with varying numbers of rows based on varying number of dates
0
 
LVL 8

Expert Comment

by:Sean Scissors
ID: 35086813
What I would suggest is the current function you have is finding the ending date. Could you not simply run a sort afterwards and simply run the function again however this time the top column will in fact be the earliest date? I will look into exactly how to do this but I think that will work.
0
 

Author Comment

by:Cactus1994
ID: 35086878
No. The whole idea is to write a formula similar to my end period formula so I don't have to sort, run formulas twice, or reformat cells.
0
 
LVL 8

Expert Comment

by:Sean Scissors
ID: 35087752
The following function will find the cell number of the last row within the given column. In other words this returns 30 in your case. I am trying to see if you can define a variable for this function then call it in the other function.

=IF(ISERROR(MATCH(9.999999E+306,A:A)),MATCH("*",A:A,-1),
IF(ISERROR(MATCH("*",A:A,-1)),MATCH(9.999999E+306,A:A),
MAX(MATCH(9.999999E+306,A:A),MATCH("*",A:A,-1))))

Essentially something like =IF(ISERROR(MATCH(9.999999E+306,A:A)),MATCH("*",A:A,-1),
IF(ISERROR(MATCH("*",A:A,-1)),MATCH(9.999999E+306,A:A),
MAX(MATCH(9.999999E+306,A:A),MATCH("*",A:A,-1)))) == "lastCell"



=TEXT(DATEVALUE(LEFT(A(lastCell),3)&" " &MID(A(lastCell),FIND(" @",A(lastCell)-4,4)),"mmmm, yyyy")

So it would then be looking at the value of A30 and return it in the format you want it. I am not sure if it's possible to create a global variable like that without a macro. For what you are wanting to do a macro would be much more versatile and simpler actually.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35088605
As array formulas

=TEXT(MIN(DATEVALUE(LEFT(A4:A30,12))),"MMMM YYY")

=TEXT(MAX(DATEVALUE(LEFT(A4:A30,12))),"MMMM YYY")

Thomas
0
 
LVL 8

Expert Comment

by:Sean Scissors
ID: 35088662
@nutsch The problem is though is that he said the number of items will always be different. Your above formula works if the last item is in A30 but that is not always the case. That is why I was trying to find a workaround for figuring out how to determine the last item in the column and then run the function before to parse the string accordingly.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35088710
jaja, I hadn't updated the range

=TEXT(MIN(IFERROR(DATEVALUE(LEFT($A$4:A1048576,12)),99999)),"MMMM YYY")
=TEXT(MAX(IFERROR(DATEVALUE(LEFT($A$4:A1048576,12)),0)),"MMMM YYY")

Thomas
0
 

Author Comment

by:Cactus1994
ID: 35097854
Nutsch and Scissors:

Thanks so much for your responses and help.

What I've found was that the simplest, non-array, varying row formula for determing the earliest date is this:

=TEXT(LEFT(LOOKUP("zzz",A:A),12),"mmmm, yyyy")

Nutsch was the closest on this, but this fomula works the best and is the simplest.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35097920
This will only work if your report sorts the date, in which case you're not asking for the minimum date, but the date on the last row, which is fine, but a different question altogether (actually the one you asked in your previous question).

Thomas
0
 
LVL 8

Expert Comment

by:Sean Scissors
ID: 35097922
Good I am glad you figured it out and we could be of some service.
0
 

Author Comment

by:Cactus1994
ID: 35098835
Nutsh:

I didn't thiink of that, although my report does sort the date. I would rather use your formula now, in case the third party data ceases to sort the date. However, plugging in the formula works for the month, but returns the years 2173 and 1900 for the Begin and End Period, respectively. Do I need to tweak the formula somehow here?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:nutsch
ID: 35098928
Maybe, can you do a load file with the full dataset of dates you're using? I'm assuming you don't get these results on your template file?

T
0
 

Author Comment

by:Cactus1994
ID: 35099138
Here's the dataset. See what you can do. Thanks, nutsch.
Load-File-Sub-Data-Set.xlsx
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35099240
OK, the formulas are entered as regular formulas. To correct that:
unmerge range B2:C2 and b3:c3 (you can't put array formulas in merged cells)
enter cell B2 (F2 or click twice)
hold ctrl and shift then hit enter. {} should appear around the formula
repeat for B3

Check out the attached screencast

T
nutsch-421006.flv
0
 

Author Comment

by:Cactus1994
ID: 35099298
Remember our threads with Barry Houdini yesterday? He was the one that came up with the

=TEXT(LEFT(LOOKUP("zzz",A:A),12),"mmmm, yyyy")

formula I liked, because it wasn't an array. Like I think I mentioned yesterday, I don't like arrays because i don't understand them, and thought that whenever the numbers or rows or cell data increased or decreased, the array would have to be reset into another formula, etc.

Maybe I'm missing out on a whole other world with arrays, but I've always used regular formulas for everything I've done with Excel.

Is there any way to do what you suggest without the formula being an array? Thanks.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35099560
I would say you should take this opportunity to learn the basics about array fomulas.
Since you are using Excel 2007 or greater, you can use A:A in an array formula and it will work. So, updating nutsch's formulae:
=TEXT(MAX(IFERROR(DATEVALUE(LEFT($A:$A,12)),0)),"MMMM YYY")
=TEXT(MIN(IFERROR(DATEVALUE(LEFT($A:$A,12)),99999999)),"MMMM YYY")
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35099582
The array part just means that it will run the datevalue function on each item in the range before caculating the min. This is a nice simple array formula and a good one to learn on.
Don't forget to use Ctrl+Shift+Enter instead of just Enter when putting the formulas in your sheet.
0
 

Author Comment

by:Cactus1994
ID: 35099860
This works, but reminds me of why I didn't like arrays when I learned the basics.

I process lots of data from exported SQL queries that export in .xls and .xml format. Cutting and pasting this data into Excel template reports I'm working with (and need these formulas for) works well the first time. However, when I cut, paste, insert, and delete rows of data, the array formulas get messed up.

I also can't merge & center cells with array formulas in them, which is minor, but it seems there are several restrictions working with arrays. I beliieve in my case for this question, the array formulas would work if I had links within the cells to data, but won't work with consistency when I'm cutting and pasting rows of information ... hence, my wanting a formula that wasn't an array but a basic Excel formula.

I take it there's no way to do what I'm needing here in Excel without using an array formula that I'll constantly need to tweak?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35099975
Well, the $A:$A thing means you shouldn't have to change it ever really, but I really can't see how it would even be close to possible to do it without an array formula unless you
1. Change the way the data arrives
2. Add a helper column
3. Make a custom function (macro)

None of those solutions are any better (in fact, I would say they are all much harder to move around then the array formula version, except maybe option 1, but that's been thrown out already).

For the record, I don't use array formulas very often at all myself, but sometimes you just can't do it any other way.
0
 

Author Comment

by:Cactus1994
ID: 35100259
teylyn:

Thanks. I noticed my default zone happened to be "Anti-Spyware" afterI first posted the question, and realized it didn't make much sense, either!  I've been getting great help and feedback here, however, so I'm even more impressed with EE that Anti-Spyware experts are also so adept at answering Excel array questions, too!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35101281
Is this what you want? Please press Control + Shift + Enter for these. I changed dates to test it and it is working just fie.

Begin Period

=TEXT(DATEVALUE(MIN(MONTH(LEFT(A4:A31,3)&1))&"/"&"1"&"/"&MIN(YEAR(1&"/"&1&"/"&MIN(YEAR(1&"/"&1&"/"&MID(A4:A31,FIND(", ",A4:A31,1)+2,4)))))),"mmmm yyyy")

End Period

=TEXT(DATEVALUE(MAX(MONTH(LEFT(A4:A31,3)&1))&"/"&"1"&"/"&MAX(YEAR(1&"/"&1&"/"&MAX(YEAR(1&"/"&1&"/"&MID(A4:A31,FIND(", ",A4:A31,1)+2,4)))))),"mmmm yyyy")

Sample Attached

Sid
Dates-Formula.xlsx
Begin.jpg
End.jpg
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35101290
It took me almost an hour to figure this out. I am sure Barry would have done this in minutes. I am not too proud of myself at the moment :(

Sid
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35114174
If you want formulas that don't need to be "array entered" then try this for beginning month/year

=TEXT(LARGE(INDEX(LEFT(A7:A1000&0,12)+0,0),COUNTA(A7:A1000)),"mmmm yyyy")

and for end

=TEXT(MAX(INDEX(LEFT(A7:A1000&0,12)+0,0)),"mmmm yyyy")

Assumes that dates start at A7 and may go as far as A1000, adjust as required. Note this works for US regional settings, may not work for other regional settings.

see attached

regards, barry
26875028.xlsx
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35116715
Wow. I didn't know you could use arrays like that without array entering the formula. Very impressive.
0
 

Author Closing Comment

by:Cactus1994
ID: 35182886
I appreciate all the input to this question. In fact, I thought the string ended without the exact answer I needed, until I noticed Barry Houdini had come in at the end and provided exactly what I required.

Clearly Barry's answer in a non-array format was the response I needed for my specific application, and I thank you all for your contributions to that end.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now