Results 1 to 6 of 6
Thread: Find date formula help

20100506, 10:16 #1
 Join Date
 Mar 2008
 Posts
 98
 Thanks
 0
 Thanked 0 Times in 0 Posts
I'm having trouble with a formula and need some help. I was thinking initially this type of formula may have to be a function but I wanted to avoid that if there is a way to do this with a formula. Basiclly what I'm trying to do is find a date in a list of dates where the day is a specific day. For example, in column A. I have a list of dates. At the end of each month, I consolidate data for the month and roll it up into 1 row of data instead of 3031 rows of daily data. In column A, the dates would be 1/1/10, 2/1/10, 3/1/10, and then starting with 4/1/10 I have every single date so 4/2/1012/31/10. I need to look through this list to find the first date where the day is a 2. In my example of dates, the formula or function should find the date of 4/2/10. Any help with this type of formula or function would be greatly appreciated.
Thanks in advance.
Bobby

20100506, 12:01 #2
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Assume 04/01/10 is in A5
Assume 04/02/10 is in A6
Assume 04/03/10 is in A7
etc.
Copy this formula beginning in B5
=IF(DAY(A5)=2,2,"")
Result in B6 should = 2

20100506, 12:16 #3
 Join Date
 Mar 2008
 Posts
 98
 Thanks
 0
 Thanked 0 Times in 0 Posts
Tim,
Thanks for the reply. Unfortunatly that formula will not work as I can not add any data to the sheet where the dates are listed. The sheet with the list of dates is populated through a complex macro that pulls data from a daily text file and loads it to the sheet.There are close to 200 hundred data points pulled from the text file daily. Due to the large amount of data, I consolidate the data after each month. Adding this formula to the end of the sheet is also not and option as the macro can identify new values in the text file and will auto add those to the end of the data sheet.
What I need specificlly is 1 formula or 1 function that can look through the list, starting at the beginning and find the first date in that list where the day in the date is a two. All data that gets consolidated has the 1st as the day. By finding the first date with a 2, I've identified my next month ready for consolidation. Since this is fairly complex to find the day within the date within a list of dates. My thought was a function might be the only way to go about this. I'd still prefer a formula if it's possible, but that is where I'm in need of help.
Thanks again for reading this post.

20100507, 07:04 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,280
 Thanks
 3
 Thanked 191 Times in 177 Posts
What do you actually want returned? A row number or the date itself?
Regards,
Rory
Microsoft MVP  Excel

20100507, 07:35 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The array formula (confirm with ctrlshiftenter) will give you the lowest date which has a day of 2.
=MIN(IF(DAY($A$1:$A$100)=2,A1:A100))
The array formula (confirm with ctrlshiftenter) will give you the lowest row which has a date which has a day of 2.
=MIN(IF(DAY($A$1:$A$100)=2,ROW(A1:A100)))
Steve

20100510, 08:27 #6
 Join Date
 Mar 2008
 Posts
 98
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Steve. I should have thought to use a MIN IF statement for this. I use MAX IF all the time to find max dates within date ranges. Should of just thought to include the day function in there. I apprecaiate the suggestion. That is what I needed.