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:
=DATEVALUE(LEFT(A4,FIND(“,”,A4,FIND(“,”,A4,1)+1)-1))+TIMEVALUE(MID(A4,FIND(“,”,A4,FIND(“,”,A4,1)+1)+2,255))
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:
=MIN(‘Abused News:Wipeout’!$F$4:$F$100)
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.
Why bother with DATEVALUE and TIMEVALUE? You’d get the same result using =–SUBSTITUTE(A4,”,”,””,2) as long as you have Transition Formula Evaluation disabled.
The @#$% blog software ate a negative sign. There are supposed to be two of them immediately following the equal sign.
fzz:
All I can get that formula to generate is a big fat ‘#VALUE!’. If I drop both the negative signs I get the string (sans 2nd negative sign), but I can’t see a way to cast this to a date value, without the use of the datevalue+timevalue functions.
I’m using Excel 2003 with Transition Calculation disabled. To me it just seems that this isn’t in a format Excel is willing to parse as a date. Perhaps it’s different in 2007, or with your locale settings?? I’m in Australia, and mine is set to “English (Australia)”. What are you using?
Wouldn’t the formula below work just as well? It should also eliminate your restriction on 100 rows.
=MIN(‘Abused News:Wipeout’!$F:$F)
With the following in cell A4
Dec 12, 2006, 5:00 pm
on my system running Excel 2003 with transition formula evaluation disabled and US regional settings, the following formula in A5
= – – SUBSTITUTE(A4,”,”,””,2)
returns 39063.70833 as expected. If I change A4 to
12 Dec 2006, 5:00 pm
and A5 to
= – – SUBSTITUTE(D4,”,”,””,1)
the formula still returns 39063.70833.
How does Dick’s formula work under your regional settings?
Hmmm,
It appears that using either your or Dick’s formulae I get the same result:
Having in A4:
Dec 12, 2006, 5:00 pm
results in #VALUE!
but reversing the date to:
12 Dec, 2006, 5:00 pm
allows either function to work correctly
result = 39063.70833
Also, if I remove the first comma and change A4 to:
12 Dec 2006, 5:00 pm
and modify the other two formulae to:
=DATEVALUE(LEFT(A4,FIND(“,”,A4,1)-1))+TIMEVALUE(MID(A4,FIND(“,”,A4,1)+2,255))
and
= – – SUBSTITUTE(A4,”,”,””,1)
respectively, I get the correct result, but using the value:
Dec 12 2006, 5:00 pm
fails.
Changing my regional setting to US….
(cross fingers..)
Ha!
The formulae that use the data with two commas work in the opposite fashion (i.e. the formulae work for “Dec 12, 2006, 5:00 pm” but not “12 Dec, 2006, 5:00 pm”)
but the formulae that use the data with one comma work in the same fashion (i.e. the formulae work for “12 Dec 2006, 5:00 pm” but not “Dec 12 2006, 5:00 pm”.
Go figure.
I get the same as Rick
With Australian settings the day-month-year works, but month-year-day doesn’t. With US settings vice-versa.
That’s Excel 2007 with Vista. As reported, transition formula evaluation stops it working.
Nice use of “substitute” anyway :).
And I didn’t know about the – – to make a text formula evaluate as a number.
“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.”
RTFM and Excel will insert the quotes in the right place.
R = Roll
M = Mouse
{g,d,r}
Doug:
I hadn’t used “- -” before either, but I ahve use “*1? for the same effect.
Any yeah – nice use to substitute!
“Wouldn’t the formula below work just as well? It should also eliminate your restriction on 100 rows.
=MIN(‘Abused News:Wipeout’!$F:$F)”
Yes, but then someone will but something goofy in F8000. It’s a trade-off, but since it wasn’t my spreadsheet, it’ll be someone else’s problem. :)
“RTFM and Excel will insert the quotes in the right place.
R = Roll
M = Mouse”
Genius. Now I won’t have to remember where they go.
Hi All –
Given the 3 specified formats:
Dec 12, 2006, 5:00 pm
12 Dec 2006, 5:00 pm
Dec 12 2006, 5:00 pm
This UDF seems to handle them all:
Function DateIt(str As String)
Dim i As Integer, mon As Integer
Dim day As Variant
Dim mnth(12) As String * 3
Dim year As Variant
mnth(1) = "Mon"
mnth(2) = "Feb"
mnth(3) = "Mar"
mnth(4) = "Apr"
mnth(5) = "May"
mnth(6) = "Jun"
mnth(7) = "Jul"
mnth(8) = "Aug"
mnth(9) = "Sep"
mnth(10) = "Oct"
mnth(11) = "Nov"
mnth(12) = "Dec"
For i = 1 To 12
mon = InStr(1, str, mnth(i), vbTextCompare)
If mon > 0 Then
mon = i
Exit For
End If
Next i
For i = 1 To InStr(1, str, ",")
If Mid(str, i, 1) Like "[0-9]" Then
day = day & Mid(str, i, 1)
End If
Next i
If Len(day) > 2 Then day = Left(day, Len(day) - 4)
str = Replace(str, mnth(mon), "", 1, 1, vbTextCompare)
str = Replace(str, day, "", 1, 1, vbTextCompare)
For i = 1 To Len(str)
If Mid(str, i, 1) Like ("[0-9]") And Len(year)
Drat.
I never remember the greater than/less than escapes
Picking up at the bottom loop:
If Mid(str, i, 1) Like (“[0-9]”) And Len(year) < 4 Then
year = year & Mid(str, i, 1)
End If
Next i
str = Replace(str, year, “”, 1, , vbTextCompare)
str = Replace(str, “,”, “”, 1, , vbTextCompare)
DateIt = DateSerial(year, mon, day) + TimeValue(str)
End Function
FWIW.
…Michael
I didn’t see anyone suggest an array formula
{=MIN(Sheet1!A1:A100*1)}
dermot: ain’t no array formulas across worksheets. Check this topic’s subject line again.