When dates are not Excel friendly

I recently had an issue.

I have a report which exports into Excel. The date format from the report is US, and I work in the UK.
Excel tries to recognise the dates as UK, so for dates that fit (eg. month is 1-12) Excel gives me an incorrect date.  For dates that don't fit (eg. month is 13-31) then Excel doesnt recognise this as a date and this is formatted as plain text.

An example of this is below:

7/29/2011 7:23:26 AM
08/02/2011 05:45

These two dates should be 29/07/2011 and 02/08/2011 respectively. However the top one is formatted as text, whereas the bottom one is formatted as date (incorrect format in terms of UK to US!)
To rectify this issue I have the formula shown below (which assumes the cell A1 contains the top of the list of exported dates)

=IF(ISNUMBER(A1),DATE(YEAR(A1), DAY(A1), MONTH(A1)),DATE(MID(A1, FIND("/", A1, 4)+1, 4), LEFT(A1, FIND("/", A1)-1), VALUE(MID(A1, FIND("/", A1)+1, 2))))

This works exceptionally well.

A user came to me today with the same issue and following giving her this solution, I thought to blog this as being worth sharing.

XBMC rip audio CD to mp3

Now my XBMC is up and running :

• using wireless remote
• all media on mounted USB share (Samba installed so I can access across home network eg configured as a NAS also)
• using the Quartz skin currently
• All music, video, movies, photos cataloged

We have loads of CDs in the attic not currently on the media drive, so i thought - 'let the ripping begin' ;-)

Strange however that XBMC baulked at ripping discs to .mp3 with the LAME encoder.

I have PuTTY set up so I can look around on a terminal level whilst XBMC is running, and also FileZilla to give me a nice GUI representation of the directories and files.

It seemed XBMC was creating the correct folder structure for my rip, but creating a 0 size file.

A fair amount (which is why I'm blogging this) of googling, and I found a reference to the fact that the native install of Ubuntu (which XBCM live runs on) does not have the lame endoer installed!

That'll be it then.

A quick sudo apt-get install lame at the terminal and....

...yeah - its ripping... :o))

(I say its ripping, and it is trying to.... me thinks need a less prehistoric DVD drive...:)

Make an iPhone ringtone in iTunes 10

If you’ve made an iPhone ringtone before the process will be familiar to you. This will work the same on both Mac and Windows versions of iTunes 10:


Making the ringtone


• Launch iTunes 10
• Find and select the song you want to make a ringtone out of in iTunes 10

 • Right-click on the song name and select ‘Get Info’

• Then click on the Options tab

• Select the playback period of the song that you want to ringtone to be, make sure it’s not more than 30 seconds – tick both boxes Start Time and Stop Time.

• Now click “OK” and then right click on the song again, and select “Create AAC version” to create a new version of the song with the 30 second interval you specified

• This will create a 30 second track.  Right click the original track and choose Get Info and go back to the Options tab – untick the Start Time and Stop Time boxes and click OK. This will keep the original track at its correct length.

• Now click the new 30 second clip (it should be under the song).and drag the clip onto your desktop area.

• If it doesn’t have the .m4a on the end, then you need to change some windows properties – see section at the end.

• Right click it and choose Rename
• Change the .m4a to .m4r
• Click Yes when you get the next box.
• Drag the file from your desktop back into iTunes
• The file will now be added back into iTunes as a ringtone
• Drag it into your iPhone Ringtones to sync it.

Change Windows Folder Settings
If you cant see the .m4a on the end of the file:


• Open My Computer
• Choose Tools | Folder Options
• Click the View tab
• Untick Hide extensions for known file types
• Click OK
• Now the .m4a should be visible.