Results 1 to 6 of 6
  1. #1
    Star Lounger
    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 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.
    Thanks in advance.

    Bobby

  2. #2
    5 Star Lounger
    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

  3. #3
    Star Lounger
    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.

  4. #4
    WS Lounge VIP rory's Avatar
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #6
    Star Lounger
    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.

Posting Permissions

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