Earliest/Latest Calendar Day

[joferder]

I have a spreadsheet that lists the earliest and latest day that snow fell each year. The Earliest date is listed in column "R" and the Latest in column "T. The rest of the spreadsheet is set up so that I can in put a start date and an end date and it will only give me the applicable data between the two. It does this by eliminating the irrelavent data in the next colum to the right ("S" for earliest and "U" for the latest), which works perfectly for what I need it to do.

The problem that I am having is getting excel to find the earliest date in the column S (range S5:S489) and the latest date in column U (range U5:U489) without taking year into consideration. I want it to show me that the earliest date in the range is 13 October 2011 and not 29 October 2005 since it is the earliest in the range. I have tried going with a simple "=MIN(S5:S489)" (and "=MAX(U5:U489)" for the latest date), but all that shows me is the earliest date based off of year.

This is one of the last things I need to fix on this spreadsheet and the solution has been eluding me for some time now. Any suggestions and ideas are greatly appreciated. If there is anything that I need to clarrify on, please let me know and I will do what I can so that a solution can be reached.

Thanks in advance for any and all help with this.

v/r,

John

 

[Joe4]

The problem that I am having is getting excel to find the earliest date in the column S (range S5:S489) and the latest date in column U (range U5:U489) without taking year into consideration.

Maybe use a helper column where you just pull the month and day in "mmdd" format (then 1013 would be less than 1029).

That equation would look something like this:
=TEXT(A1,"mmdd")+0

Then you could take the MIN/MAX of these helper column value to determine the earliest/latest dates.

 

[Excelestial]

=TEXT(MIN(DATE(1,MONTH(S5:S489),DAY(S5:S489))),"m/d")

 

[joferder]

Joe4,

Your advice is pretty close to what I need it to do. I already have a helper column for each of the two since that is where it will only show the data that falls within the two time frames. The problem that I am running into is that there are blank cells and with the formula that you gave me, it will fill in a date of 9-Apr-00 if the cell that I am pointing to is blank. I rewrote your formula with a simple IF function in it so that it looks like this:

=IF(R220="","",TEXT(R220, "mmdd")+0)

This leaves the cell empty if the one to the left is empty and eliminates that problem. The issue that I am working figuring out right now is how to make it so that it will only look at the data that falls within the start and stop dates. The original formula that I started with is:

=IF(R220="","",IF(AND(B220>=$A$2,B220<=$B$2),R220," "))

I am trying to work your formula into what I already have so that it will only get the data between the start date in A2 and the stop date in B2. I'm not 100% sure where to go from here, but I am going to keep playing with it and see what I can come up with. If you know of how to make this work I would greatly appreciate the info.

Thanks for moving me in the right direction.

v/r,

John

 

[joferder]

=TEXT(MIN(DATE(1,MONTH(S5:S489),DAY(S5:S489))),"m/d")

Excelestial,

I tried your formula and I'm not sure if I did something wrong or what happened, but all I get is "#VALUE!". Your formula is more complicated than Joe4's so I'm not really sure how to edit that one.

If there is something specific I need to do please let me know and I will try it.

Thanks for the quick response and the help though. Without the people on this forum I would still be banging my head 50 excel spreadsheet projects ago.

v/r,
John

 

[Joe4]

If A2, B2, and B220 are all in date format, and you want to ignore the year, I think you may need to apply the same logic to ignore the year portion as we used with the helper column, i.e.

=IF(R220="","",IF(AND((TEXT(B220,"mmdd")+0)>=(TEXT($A$2,"mmdd")+0),(TEXT(B220,"mmdd")+0)<=(TEXT($B$2,"mmdd")+0)),R220," "))

 

[joferder]

The only part of the spreadsheet where I want to ignore the year is when it is telling me which date the snow fell first or last on. I have data that goes all the way back to 1988, but there are times when I am only concerned with the data between March 2005 and December 2009. So the year is still important, but when I was trying to figure out a MAX and MIN formula for this, excel would really only take into consideration the year, which isn't helpful since I only have one per year for the first snowfall and one per year for the latest snowfall. There is a row for every month of every year since 1988, but the latest or first snowfall date is only entered into the date that it applies to on that specific year. So the formula that you gave me does what I need it to do, but it also wants to fill in data for the blank cells. That is why I rewrote the formula that you gave me so that it would leave the cells that aren't used blank and that works well. I am just trying to figure out a possible formula that will make it so that I only need one helper column, but I am thinking that it might be easier to just add another one.

The other problem that have discovered is that I am putting a MIN formula at the top of the Earliest Snowfall helper column (which pulls the data that you helped me refine) and then I am putting a Lookup formula in the cell to the left of that, telling it to look up the number that the MIN formula comes up with and then give me the data in the cell to it's left, but it just comes up with "#N/A". I'm not sure why that is because if I change the date in one of the applicable area to Sept 09 (909), then it works just fine. Any ideas on that one? It doesn't make any sense to me why that would make any difference, so I am baffled by that now.

Thanks again for your help.

 

[Joe4]

I think you kind of lost me. I am having a hard time visualizing it without seeing it.

 

[joferder]

Joe4,

Thanks again for getting back to me. Sorry that I have made this confusing. It is really hard to describe a spreadsheet without making it as clear as mud and apparently I can't post attachments (I'm not sure why but that is what it says in the posting rules). I will try to describe the spreadsheet a little more and hopefully that will clear things up.

The spreadsheet has a row for every month of every year since January 1988. In each month we input the Max and Min temperature, the strongest winds, number of days with thunderstorms, total amount of snowfall and number of days below 0°F. We also list the seasonal total snow fall (for example, the snowfall for 1988-1989 was 43.0 inches) and the earliest and latest date that it snowed each year.

The seasonal snowfall total is always located in the row for May of that year (May 1989 covers the winter of 1988-1989). The Earliest and Latest day of snowfall is input into the row for the month that it occured that year, with earliest being in one column and the latest in a seperate column).

The spreadsheet was designed so that we can put a date when a person arrived here and the date when they are leaving and it will give us the maximum and minimum temp and wind speed, total number of days with thunderstorms and temps below 0°F, total number of inches of snow that fell, season with the most snow and the earliest and latest that it snowed.

I have been able to automate most of the data calculation to make this a lot easier. The one thing that I can't get it to search the list for is the earliest and latest snowfall. The formula that you gave me works pretty well to eliminate the year from consideration, but when it gives the user the data I still need it to give me the full date. So if it is giving me the earliest date from a list of

18 October 2008
09 October 2009
11 October 2010

I need it to pick out that 09 October was the earliest date that it snowed in that period and fill in the blank that it was 09-Oct-09 in the appropriate cell. I have it currently set up in the helper column so that it will only use your formula in the column to the right if the cell to the left contains data AND falls within the two dates given.

I have been able to get the IF formulas to work correctly, but now the problem that I am having is when I use the =MAX() function it will find the correct data but I need it to then fill the date thta it applies to into the cell to its left. I tried a LOOKUP function and get two completely different problems.

With the earliest snowfall section I get "#N/A" even though the information is correct. For the column that covers latest snowfall I do get a date, but isn't the date that is directly left of the number that is the max within the range. The date that it gives me whe I use the LOOKUP function for the max is actually the latest date that falls within the range.

The information that it gives me doesn't make any sense to me and I am not really sure how to get the correct information. If you have any ideas on where I should go with this I would really appreciate the nudge. If you are still confused and have a way for me to get you a copy of the spreadsheet I will do what I can.

Thanks again for your help. You have gotten me a lot closer to finishing this than I have been able to get to on my own. Only these last two things holding me back and I will be finished with it.

John

 

[Aladin Akyurek]

What are the earliest and latest items given:

18 October 2008
09 October 2009
11 October 2010