Quite often if you open a CSV file in Excel with American date formats Excel will do its best to convert them (badly) and you end up with some in date format and some in text format.
This formula helps to sort it out.
DATEVALUE(IFERROR(MONTH(A2),SUBSTITUTE(MID(A2,FIND(“/”,A2)+1,2),”/”,””))&”/”&IFERROR(DAY(A2),SUBSTITUTE(LEFT(A2,2),”/”,””)&”/”&RIGHT(A2,2)))
So 6/21/16 becomes 21/06/2016 and 5/29/16 becomes 29/05/2016
Find the Monday for a week that a date falls into
=A2-(MOD(WEEKDAY(A2)-2,7))
Where A2 is the field with the date.
The great thing is that
This is something I do regularly, wanting to look at log activity over a 30 minute range
Use datediff and “mins” datepart like this
(datediff(mins,’2016-05-04 15:50′,DATE) between 0 AND 30)
This looks at a 30 minute window around 16:00 hrs
Simple really
This should be easy
url_domain || url_path || url_query_params as request
really should work.
BUT if any one of these is null it will deliver a null string
So you need
nvl(url_domain,”) || nvl(url_path,”) || nvl(url_query_params,”) as request
This replaces a Null with a blank.
Why can’t it do this anyway? Who knows.
This is quite straight forward but seems to cause confusion on the internet
TO_CHAR(DATE,’HH’) || ‘:’ || trim(TO_CHAR((ROUND((DATEPART (MINUTE,DATE) / 10),1)*10),’09’)) AS mins10
so you get
12:00 – 12:00
12:01 – 12:00
12:03 – 12:00
12:11 – 12:10
This imports into excel as a time
Just change the multiplier
TO_CHAR(DATE,’HH’) || ‘:’ || trim(TO_CHAR((ROUND((DATEPART (MINUTE,DATE) / 5),1)*5),’09’)) AS mins5
date_trunc(‘minute’, Date)
date_trunc(‘hour’, Date)
date_trunc(‘week’, Date)
This puts you to the nearest Monday in that week which is more useful than the week number
to_char(DATE,’HH24:MI’)
It is quite easy and very useful if you are running complex and long calculations.
As far as I can tell you can only run two versions at a time.
We all love Webpagetest.org
To get the best quality images and video you can use this version of the URL
http://www.webpagetest.org/?pngss=1&iq=95
This saves images as PNG at a 95% qualtiy. It also improves video capture quality
This is documented in this video
https://www.youtube.com/watch?v=6UeRMMI_IzI
minute 27
Lots more really interesting stuff in this video
This method is almost instant (using 500,000 record lookups)
IF(M56=INDEX(‘Sitemap Keyword Categories’!$G$1:$G$300016,MATCH(M56,’Sitemap Keyword Categories’!$G$1:$G$300016,1),1),”Supported”,”Not Supported”)
This does an “nearest match” (using status “1”) and then tests to see if the nearest match is correct (ie “M56=”).
This Updated 500,000 records in under 1 second – previous methods (exact match using “0” and vlookup function ) would take 5 minutes.
It is vital that the column G in “Sitemap Keyword Categories” is sorted A-Z
It may sound complicated but this is really useful and not that complex.
If you are into web design and performance you will have Firebug loaded in Firefox (if not do so now)
http://getfirebug.com/releases/netexport/ – copy the url for the latest version and paste it into firefox.
Reboot Firefox and you will find in youur “Net” tab
Export as HAR (HTTP Archive) file.
How load Har2Csv from there. This is a command line tools specifically for converting HAR files to CSV so they can be read into EXCEL.
This gives you a tabbed delimited file
These formulas work well and are easy to adapt for any word in a string of text – just change the SUBSTITUTE number through out the fomula to 1 less than the word you are looking for. The IFERROR returns a blank but it can be useful to return a character something like “^” instead for lookups.
4th
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256)))),””)
3rd
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256)))),””)
2nd
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256)))),””)
1st
=TRIM(MID(A23,1,FIND(” “,A23&” “)))
