# Thread: Find date formula help

1. 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 30-31 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/10-12/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.

Bobby

2. 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

3. 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.

4. What do you actually want returned? A row number or the date itself?

5. The array formula (confirm with ctrl-shift-enter) 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 ctrl-shift-enter) 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

6. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•