Bob has a workbook with several web queries in it. In each of the web queries, the first column is a “date”. He needs to find the minimum and maximum date for all web queries.
The first problem this presents is that the “dates” aren’t really dates; they’re text. That’s why I kept putting quotes around “date”. I’ll stop now. He could format the cells all he wants, but Excel won’t coerce them into dates. Even if he fixed the text to be real dates, when the web queries refresh, they’ll be right back to text. The dates look like this:
Dec 12, 2006, 5:00 pm
That seems like a great candidate for the DATEVALUE function, but sadly it doesn’t work. I needed to manipulate the text a little. I used this formula:
The DATEVALUE function converts the first part into a date, then adds it to the TIMEVALUE function, which converts the second part. The parts are divided by a comma. The second comma to be precise. The two FIND functions find the location of the second comma. The inner FIND sets the start for the outer FIND: One space to the right of the first comma found.
I put this formula in a column adjacent to the web query. I also checked the “Fill down formulas in columns adjacent to data” option in the Properties dialog box associated with the web query. This ensures that as the query grows, the formulas will fill down the right amount.
The second problem was using MIN and MAX across the sheets. The problem here was that some of the sheet names contain spaces. I knew that I needed some quotes around the sheet name, but I can never remember where. I ended up with this:
The single quotes go around both sheet names (not individually) and before the bang. It took me a few iterations to figure that one out. If the queries ever get larger than 100 rows, I’m in trouble.