Excel DATEVALUE Function

When a Microsoft Excel spread sheet has a column of date data in this format:

Nov 24 1995

Then the Excel function you’ll find useful is DATEVALUE

DATEVALUE function.

This formula here:

=DATEVALUE((TRIM(MID(R2,5,2))&"-"&TRIM(LEFT(R2,3))&"-"&TRIM(RIGHT(R2,4)))

Basically grabs the Day, then the Month, then the Year from the column, then converts it to a serial number that Excel recognises as a date. Then format this to display however you want it to.

Convert multiple Microsoft Excel files to .csv files with 3 clicks

I often get given Microsoft Excel files to import them into a database. Normally we’ll work with the files in the Excel to clean up data (phone numbers, Addresses, Titles, etc) but then save them as .csv files to do the actual import.

On my MacBook this is now an easy task because I’ve setup a ‘Service’ with Automator. In Finder I simply select the files I want converted, right click, select Services and select Convert Excel to csv files. Job done. Heaps quicker than doing them one at a time.

Screenshot of Convert Excel files to csv service in Automator
Screenshot of Convert Excel files to csv service in Automator
Screenshot showing service in use
Screenshot showing service in use

Excel formula to convert minutes into Hours and Minutes

On occasion I end up with an Excel spreadsheet that has a list of items, each of which has a duration. It’s easy to sum up the minutes and get a total number of minutes (see B7 in the picture below).

But it would be nice to see that in “x hours y minutes.” don’t you think? Yup, so do I. And that’s what I’ve done in the C9 cell in the picture below.

Screen snippet showing a column of minutes summed up, and then convert that into human readable hours and minutes
Human Readable Hours and Minutes

So without further ado, here is the Excel formula to do this

=ROUNDDOWN(B7/60,0)&" hours "&MOD(B7,60)&" minutes."

Let me know if you find this helpful.

Export ACT! reports to Excel

Had a client today need some information out of ACT!, and ideally it would be in an Microsoft Excel spread sheet.

Given that one of the default ACT! reports gave the bare minimum required (Group Membership) we spent some considerable time editing a copy of that report to display the actual fields we wanted, both contact fields and group fields. What we wanted was a spread sheet that showed each contact in a particular groups sub-groups, detailing which sub-group each contact was a member of, and also showing some groups specific information for each.

This was all fine except that it still wasn’t in a spread sheet once we produced the report, printing it, out to PDF, were no problem, but no .xls file.

This is where KB 14690 came in handy. It details how to modify the Windows Registry such that the output options for ACT! reports will then include such welcome and handy options like Microsoft Excel, Microsoft Word, TIFF file and Paged html.

Now when we produce the report, we get a beautiful Excel spread sheet with just the information the client requested.

Hope you find that useful, and if you do, let me know how you’ve applied it.