Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Minimum and Maximum Dates

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
Tim Jackoboice
Asked:
Tim Jackoboice
  • 10
  • 6
  • 5
  • +3
1 Solution
 
Sean ScissorsProgram Analyst IICommented:
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
 
Sean ScissorsProgram Analyst IICommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
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
Lessons on Wi-Fi & Recommendations on KRACK

Simplicity and security can be a difficult  balance for any business to tackle. Join us on December 6th for a look at your company's biggest security gap. We will also address the most recent attack, "KRACK" and provide recommendations on how to secure your Wi-Fi network today!

 
Tim JackoboiceOwnerAuthor Commented:
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
 
Sean ScissorsProgram Analyst IICommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
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
 
Sean ScissorsProgram Analyst IICommented:
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
 
nutschCommented:
As array formulas

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

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

Thomas
0
 
Sean ScissorsProgram Analyst IICommented:
@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
 
nutschCommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
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
 
nutschCommented:
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
 
Sean ScissorsProgram Analyst IICommented:
Good I am glad you figured it out and we could be of some service.
0
 
Tim JackoboiceOwnerAuthor Commented:
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
 
nutschCommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
Here's the dataset. See what you can do. Thanks, nutsch.
Load-File-Sub-Data-Set.xlsx
0
 
nutschCommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
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
 
barry houdiniCommented:
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
 
TommySzalapskiCommented:
Wow. I didn't know you could use arrays like that without array entering the formula. Very impressive.
0
 
Tim JackoboiceOwnerAuthor Commented:
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

 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

  • 10
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now