Excel 2007: Date formats

Ever tried copying dates into Excel, to have Excel refuse to format them as a date?

If you press F2 on the cell containing the date and press enter, it may then update to the format, but what if you have a 1000 dates to update?

Well, you could always record a Macro that does ActiveCell.Value = ActiveCell.Value and then moves to the next line to do the same thing. However there is a "bug" with this. Excel VBA will always interpret dates as a US format, unless it encounters a date that cannot be US e.g. 25/12/2005, in which case it will interpret it as dd/mm/yyyy. Ouch!

Solution:
1. Set an empty cell to the date format that you require.
2. Set the cells containing the dates to the date format that you require.
3. Copy the empty cell, and "Paste Special" over the cells containing dates. Select "All" and "Add" then click OK.

Excel: Grabbing primary SMTP address from an Exchange 5.5 export

This is an Excel function that allowed me to grab the primary SMTP address from an Exchange 5.5 export of users: -

=IF(ISERROR(MID(R18,FIND("SMTP",R18)+5,FIND("%",R18,FIND("SMTP",R18)+5)-FIND("SMTP",R18)-5)), RIGHT(R18,LEN(R18)-FIND("SMTP",R18)-5), MID(R18,FIND("SMTP",R18)+5,FIND("%",R18,FIND("SMTP",R18)+5)-FIND("SMTP",R18)-5))