February 20, 2006

Goodbye Date Confusion Part 2 - Date Details Toolbar

After I wrote my last post, I thought it might be handy to have a toolbar to show date details all of the time. So I made this.

Select any cell in any file and it will show the date details automatically (assuming there is a date in the selected cell)

How to Read
On the left is a regular date. You can switch between Day Month Year and Month Day Year formats by pushing the toolbar itself. The setting is kept between sessions.

Then, the weekday is shown along with the instance in the month (as in today is the third Monday in February).

This is followed by the week number and day number starting from January 1 of that year (in this case 2006).

Finally, we have the ISO Date shown as Year, Week Number, Day. (For further information, refer to this link)

The toolbar is free floating, you can drag it to dock with any other toolbar if you wish.

You can download the Date Details Toolbar here.

Unless I get sidetracked again, I'll continue with my Visibility in Excel series next time :-)  

Posted by andrewe at 21:43

February 17, 2006

Visibility In Excel Part 2 - Goodbye Date Confusion

Let's consider this date.


Is it the second of March? Or the third of February? We are talking about Year 2004 right? Here in Japan, dates can be written as Year, Month, Day...at least we're probably right in thinking it's the 21st Century...? Hmm...

Certainly would be nice to know for sure. And while we're wishing, how about some other information like Day of the Week, Instance in the Month (as in today is the 3rd Friday in February), Absolute Week and ISO Week numbers and even the day of the year?

Something like this?

Here's an adaption to my previous code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    ' ISO week formula used is by Evert van den Heuvel, others are mine
    Dim nthWeekday As Variant, absWeek As Integer
    If IsDate(Target) Then
        nthWeekday = Int((DateSerial(Year(Target), Month(Target), Day(Target)) - _
                DateSerial(Year(Target), Month(Target), 1)) / 7) + 1
        Select Case nthWeekday
        Case 1: nthWeekday = "(" & nthWeekday & "st)"
        Case 2: nthWeekday = "(" & nthWeekday & "nd)"
        Case 3: nthWeekday = "(" & nthWeekday & "rd)"
        Case Else: nthWeekday = "(" & nthWeekday & "th)"
        End Select
        absWeek = Int((Target - DateSerial(Year(Target), 1, 1)) / 7) + 1
        With Target
            .EntireColumn.Validation.Delete ' Optional
            With .Validation
                .Add Type:=xlValidateInputOnly
                .InputMessage = Format(Target, "mmmm d, yyyy") & Chr(10) & _
                    Format(Target, "dddd ") & nthWeekday & Chr(10) & _
                    "Absolute Week " & absWeek & Chr(10) & _
                    "ISO Week " & 1 + Int((Target - DateSerial(Year(Target + 4 - _
                    Application.Weekday(Target + 6)), 1, 5) + _
                    Application.Weekday(DateSerial(Year(Target + 4 - _
                    Application.Weekday(Target + 6)), 1, 3))) / 7) & Chr(10) & _
                    "Day " & Target - DateSerial(Year(Target), 1, 1) + 1
            End With
        End With
    End If
End Sub

No more confusion? Fingers crossed ;-)  
Posted by andrewe at 22:03

February 12, 2006

Visibility In Excel Part 1 - Dynamic Comments

It occurred to me the other day that visibility in spreadsheet design is not always the best. This may be due to several reasons, the fact is key data may not be readily accessible when needed - it could be in hidden rows or columns, not visible on the current screen or perhaps in another sheet or workbook.

This is a possible solution using Input Messages from Data Validation combined with a Selection Change event. An example workbook is attached for your reference here. And below is an image of a "Dynamic Comment" in action. (By "Dynamic Comments", I mean comments that update automatically. Select the cell where they show, and the latest data will be updated instantly)

A bit of explanation on how it is set up. Here is the example code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Cells(1, 1) = False Then Exit Sub
    With Target
        If .Column = 2 And _
            .Row > 2 And _
            .Row < ActiveSheet.UsedRange.Row + _
            ActiveSheet.UsedRange.Rows.Count Then
            With .Validation
                .Add Type:=xlValidateInputOnly
                .InputTitle = "" ' Optional
                .InputMessage = "Customer: " & Cells(Target.Row, 2) & Chr(10) & _
                    "Invoice Number: " & Cells(Target.Row, 14) & Chr(10) & _
                    "Item Ordered: " & Cells(Target.Row, 6) & Chr(10) & _
                    "Quantity: " & Format(Cells(Target.Row, 12), "#,##0") & Chr(10) & _
                    "Amount: " & Format(Cells(Target.Row, 16), "$ #,##0.00") & Chr(10) & _
                    "Total Sales: " & Format(Application.Sum(Range("TotalSales")), "$ #,##0.00")
            End With
        End If
    End With
End Sub

1. Automatic Update Mode vs. Edit Mode
First, you will notice that if Cells(1,1) (Range A1) is False, the subroutine is exited. This added to make it possible to edit the worksheet without the SelectionChange event interfering with Undo. The Forms Toolbar checkbox you can see is linked to Range A1, the font is not visible as I have changed the color to White but you can see whether the code is enabled or not by looking at the Checkbox. When checked, A1 is TRUE (Automatic Update Mode), otherwise it is FALSE (Edit Mode). If you are just referring to data rather than editing (or you feel comfortable without Undo), you might want to just leave it in Automatic Update Mode all of the time.

2. Set the Range
I've limited the range to just once cell in one column (In this case Column B). It might be tempting to add more columns or rows and there is nothing to stop you, I prefer to limit the validation to keep the file size down.

3. The Input Message
This is where it all happens. I've made a string to join data from several cells. The input message in this case is just an example. It probably would not be necessary to show data from cells that are already visible. So you can use the "comments" to refer to where you can't see... as mentioned above, hidden rows or columns, not visible on the current screen or in another sheet or workbook, even outside of Excel (for example, the Registry). Not only that, you can use VBA to make calculations to add to the string and also format it as you like.

A few things to keep in mind,

There is a limit of characters that can be used in the Input Messages (254 by my testing)

I've used Chr(10) to force linebreaks. It doesn't always work as expected, but you can add an extra row of characters at the bottom to help. The number of of characters used in this case should exceed the maximum number of characters used in any other row.

An easy way to get the Column numbers is to go to Tools, Options, General, then check R1C1 reference style to show the numbers in the Column Headers. Uncheck when finished.

Why not use regular comments? I experimented using them - I found this way to work better.

Any questions?  
Posted by andrewe at 22:54

February 05, 2006

Avoiding Weekends

I live for the weekends but in some jobs you have to avoid them. Say you are in a delivery business, these formulas might be useful...

To calculate the preceding Friday,


Change the 5 to a 6 to get preceding Saturdays.

And to calculate the following Monday,


Change the 5 to a 6 to avoid Sundays only. (I can't believe I'm saying this)

Posted by andrewe at 20:47