tenthofjune

So I plug a USB Microsoft Intellimouse into a PC running Microsoft Windows 7 and of course it recognises it as it already has the driver, errrr well no…

and then spends a few minutes looking on Windows update. Why doesn’t Windows 7 already have these driver preloaded?

Well, I would if I could…

If you need to calculate the number of days in a given month, then assuming your date is in cell A1, then

=DAY(DATE(YEAR(A1),MONTH(A1)+1,))

will give you this. This basically works by calculating the day part of the zeroth day of the following month which means the last day of the month.

Two questions I am often asked about the time format in Excel are: Why can’t it show times greater than 24 hours? and Why can’t it show negative time?

To help understand how to format time and date it’s best to understand a little of how Excel stores time and dates. By default on Windows based PCs, dates are stored in Excel sheets as the number of days since 0:00 1st Jan 1900 plus 1. Today (12th July 2010), is therefore stored as 40371. Time is stored as a fraction of a day, so 12:00 midday is stored as 0.5 and midday today would be 40371.5.

Times greater than 24 hours

By default, Excel displays times as hh:mm by taking the fractional part of the number stored. This works fine up to 23h59m59s but as soon as you go over 24 hours, then this method loses the fact that we’ve gone over a day and starts at 0:00 again, not very useful if you’re trying to create a timesheet.

To force Excel to take the digits to the left of the decimal point into consideration and display the real number of hours simply add square brackets to the ‘hh’ part of the format i.e. [hh]:mm

Negative Time

If you’ve ever created a calculation that results in a negative time (perfectly reasonable if you have a sheet which records say time owing) you will have seen the dreaded ########. Excel has two date systems, the older being the 1904 system used on Excel for Mac (Excel first appeared on the Mac before Windows even existed) and the 1900 system described above, which is used for increased compatibility with Lotus 1-2-3. While the 1904 system supports negative dates, the 1900 system does not.

To work with negative time values therefore requires switching to the 1904 system. On Excel 2000 this is found under Tools->Options on the Calculation Tab. Excel 2007 has it under Office Button->Excel Options-> Advanced.

Note that if you have already started your spreadsheet, all of your dates will now be out by 4 years and 1 day, so it is best to switch to the 1904 system before developing the sheet if you expect to require negative date display.

We’ve had an odd one today: Before installing a copy of Photoshop Elements 6 on a Vista machine, we thought it best to uninstall an older version 2 that was on the system in case anything got fouled up by the new version (or rather the old version fouled up the new install).

That was easier said than done. There is no uninstall option under Program files, so Control Panel was used, but it threw back the error that the “‘Uninst.isu’ file is not valid”. Reinstalling version 2 in an attempt to fix any corruption was also not successful, but we then noticed that the uninst.isu filedate had not changed.

After trashing the file, we again reinstalled v2 and uninstall then worked OK.

I’ve just run a manual Windows update on three Windows boxes today that are normally run by non-admin users. This is still necessary since, although they are all set to ‘auto-update’, the updates get downloaded but the actual updates do not take place unless an admin logs into the machine. I wonder how many PCs around the world are delayed in installing critical security updates because of this? However, that is an aside as it is not the reason for this post.

On all three machines, as well as the critical update, I also installed updates for .net 1.1, .net 2.0 and the latest Windows Media Player. After installing and rebooting, I always make a habit of logging in again as admin and forcing a recheck for any other updates: on all three machines it was then identified that critical security patches were required for all three of these new items installed.

This means that the initial downloads made available by Microsoft were not patched, despite the fact that Microsoft knows they need it because it has the patches ready for them.

Since the initial install involves the mandatory Microsoft ‘your mouse has moved, please restart to update changes’ reboot, it is quite likely that anyone installing it will then leave the machine to a non-admin user to continue using, blissfully unaware that the new software has a critical flaw.

Surely it makes perfect sense to have the initial download fully patched.

One of the very reasons I don’t want Windows Vista is the threat that Microsoft will screw up the WGA validation system, and surprise, surprise, guess what happened this weekend?

Interestingly, Microsoft’s WGA Blog says:

This validation failure did not result in the 30-day grace period starting and no one went into reduced functionality mode as a result.

but in the very next sentence then says:

The experience of a system that failed validation in this instance was that some features intended for use only on genuine systems were temporarily unavailable.

Err, sorry Microsoft, but temporary unavailability of features is very definitely reduced functionality.

Is it just me, or does Steve Ballmer look like the monster from Young Frankenstein?

The problem

You have a cell in Excel which you’ve got formatted just the way you want it. Then along comes another user and pastes data into that cell; unfortunately, that’s not all that gets pasted, the formatting gets changed too. Now if you were pasting it, you could have avoided this by using ‘paste special…’ and selecting values only, but you can’t force other users to do the same. The ideal solution would have been that Excel could lock the format, but it can’t without locking the cell completely.

The solution

One solution would be to have the user paste into another cell, and have your original nicely formatted cell simply reference the first (and lock it), but that doesn’t always work with your required layout.

Another solution is to have Excel watch for changes in the cell and reformat it back to how you want it. To do this, we need to use VBA code on the sheet.

The first step (unless you are happy writing VBA code to format the cell) is to record a macro of you formatting the cell as you wish.

Select the cell you wish to format, then use ‘Tools -> Macro -> Record New Macro’. Perform all necessary formatting, then press the Stop Button on the Macro toolbar. Don’t worry about naming the macro, it won’t be needed later.

Use ”Tools -> Macro -> Macros…’ followed by ‘Edit’ to see your recorded macro.

It should look something like this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 05/04/2007 by Ian Fitter
'

'
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
End Sub

The bit we’re interested in is the ‘With/End With’ section:

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With

Select and copy this section – we’ll need it later.

We now need to open the code window for the sheet in question. There are a number of ways of doing this, but one is to right-click on the sheet’s tab and select ‘View Code’. This will once again open the VBA code window. Enter the following and paste in your macro from earlier:

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

End Sub

Now give it a try. Select and copy a different cell with completely different formatting and paste it into your test cell. Excel will immediately reformat it to your original settings.

Obviously, this would not be necessary if Microsoft had made it possible to lock the format of the cell (instead of wasting its time on the paperclip)

I found the information on monitoring sheet changes on following page:

http://www.ozgrid.com/VBA/run-macros-change.htm

Don’t know which version of Microsoft Windows Vista to choose? Take a look at JoyOfTech’s recommendation.