This morning one of my colleagues asked what time it was in a certain part of the world. I typed the name of the place followed by “Current Time” into the Google search box. Google responded by displaying the current time for that location above it’s normal results list.
Absolute cell references allow you to “fix” part or all of a cell reference in a formula. This can be a great time-saver when copying formulas, however, there is more to it than meets the eye… Read More
Using a combination of the ROW and MOD functions and Conditional Formatting, you can easily shade alternative rows in Excel, making a list of data easier to read. Read More
Later this year, the company that I work for will be upgrading to Vista/Office 2007. Earlier this year I produced a short video to show to my colleagues (we will be delivering the training), outlining the major differences between Outlook 2000 (the version currently used) and 2007.
The MOD function in Excel returns the remainder after a number is divided by a divisor.
As an example, start a new spreadsheet and enter 172 into A1. This represents the number of minutes that a piece of machinery has been running (this figure is manually entered after the user has noted the value by reading the machine’s “Activity Log” which outputs a value in minutes).
Enter a formula in A2 to calculate the number of whole hours:
=INT(A1/60)
As we only want to display whole hours, the INT function is used to return the Integer part of the result (172 / 60 = 2.866667).
The formula in A3 which calculates the number of remaining minutes is:
=MOD(A1,60)
In other words, 172 divided by 60 is 2 remainder 52
I was recently presented with an Excel file which displayed a message every time it was opened indicating that it contained links to other files. I was assured by the owner of this spreadsheet that all links had been removed.
Thanks to officearticles.com for helping me to solve the problem:
Of course if you have Excel 2003 or later you can just select Edit > Links and there is a Break Link button in the dialog box!
In Word, selecting some text and pressing Shift+F3 will change the case of the selected text. It works on a cycle through the following - lower case , CAPITALS or Sentence Case.
Excel does not have this feature but you can select a cell and use =UPPER, =LOWER or =PROPER.
The format is =FUNCTION (CELL) where FUNCTION is the required function (upper, lower or proper) and CELL is the cell that contains the text.
For example, if A1 contains MIKE THOMAS, and in B1 you enter =PROPER(A1), B1 will contain Mike Thomas.
You can then use Copy and Paste Special / Values to copy the contents of B1 into A1, thus overwriting the original text.
I’ve used this feature many times for clients who have been given a list of, for example, names, that have been pulled off another system (say a payroll system) where the original data has been stored as capital letters.
Last year I wrote found a piece of VBA code (well I did find it but then adapted it so back to my original comment - I wrote a piece of VBA code) that split a Word mail merge output document (i.e. the one that is produced as a result of running a mail merge) into seperate files which could then be emailed to the recipients.
Well yesterday one of my colleagues asked me to dig out the code and apply it to his mail merge - and he’d make me a cup of his famous coffee if I could do it.
So 20 minutes later and with a few changes to the code, the job was done and I was sat back with a mug of steaming coffee.
And the code…You’ll find a downloadable zip file here.