tenthofjune

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.

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