More Criggo

Excel - extract month from date

Lets say that cell A1 contains the date July 21, 2004 (entered as 21/7/2004 or 7/21/2004 depending on your date system). You want to extract the year (i.e. 2004) from that date and put it in cell C1. Simply enter the formula =YEAR(A1) in cell C1. Same thing for month and day. Use =MONTH(A1) or =DAY(A1).Then reformat as text

Some Criggo favourites

All from http://criggo.com/

Excel: Prevent VLOOKUP from showing N/A

=IF(ISNA(VLOOKUP($A12,'MIS Data'!$A$2:$AK$74,21,FALSE)),0,VLOOKUP($A12,'MIS Data'!$A$2:$AK$74,21,FALSE))

Word: Fill to the end of the line

Press Ctrl+A to select all the text in the document. Select Tabs from the Format menu and click on the Clear All button. Create one tab just inside the right margin. For example, if the right margin is 6 inches, set it to 5.99 inches. Under Alignment, check Right. In the Leaders area, select periods, dashes, or underscores. Click on OK. Type a tab at the end of each paragraph, or use Find and Replace to replace ^p with ^t^p (^p represents a paragraph mark, and ^t represents a tab). Word will fill the line to the right margin with your leader character.

Excel - Remove all named ranges in a sheet

1. Copy the code. 
2.  

   Option Explicit 
    
   Sub RemNamedRanges() 
        
       Dim nm              As Name 
        
       On Error Resume Next 
       For Each nm In ActiveWorkbook.Names 
           nm.Delete 
       Next 
       On Error Goto 0 
        
   End Sub 

3. Open any workbook.
4. Hit Alt+F11 to open the Visual Basic Editor (VBE).
5. On the left, choose a file.
6. From the menu, choose Insert-Module.
7. Paste the code into the code window at right.
8. Hit the save diskette and close the VBE.
9. Run the macro by going to Tools-Macro-Macros and double-click RemNamedRanges.

Excel - Hide N/As on VLOOKUP fails

=IF(ISERROR(), "", )

Excel - remove all hyperlinks

Select all the cells (ctrl-A (twice in xl2003))
hit alt-f11 (to get to the VBE, where macros live)
hit ctrl-g (to see the immediate window)

type this and hit enter:

selection.hyperlinks.delete

Close the VBE and back to excel to see if it worked.

Mac: rsync issues fixed?

For some reason, when running my rsync scripts, I kept getting output saying that files had been deleted when in fact they weren't. It looks like these were hidden files. Fortunately I found this site - http://wiki.ljackson.us/Rsync#Hidden_Files - and have slightly changed the syntax which now seems to work much better.

My revised scripts are:

rsync --progress /usr/bin/ssh -ave --stats --delete --exclude ".*" --exclude /iTunes\ Music/Downloads/Podcasts/  ~/Music/iTunes/"iTunes Music"/ /Volumes/1tb/"iTunes Music"

and

rsync --progress /usr/bin/ssh -ave --stats --delete  --exclude ".*" --exclude /iPod\ Photo\ Cache/ --exclude /iPhoto\ Library --exclude /Photo\ Booth/ ~/Pictures/ /Volumes//Mac_backup/Pictures/