Category Archives: MS Office Tricks

Excel: Using Regional Settings to Localize Date with Time Formats

Excel date and time formats will automatically localize based on the user’s system setting – if the format is set to a specifier denoted by an asterisk in the cell format dialog.

To format a date with time, we’d typically set a custom format like “mm/dd/yyyy hh:mm:ss AM/PM”. But this format will not fully use the regional settings. If we want to automatically localize a date with time format, we have to do something different. I tried a lot of things but the following “trick” was the only way I found that worked for all regional settings including the AM/PM indicator.

First, add the following VBA to the Workbook_Open event. Column A contains the date values we want to localize. Cell D1’s NumberFormat is set the the default date format and cell E1 to the default time format.

Private Sub Workbook_Open()

    Range("D1").NumberFormat = "m/d/yyyy" 'set to default date format
    Range("E1").NumberFormat = "h:mm:ss AM/PM" 'set to default time format
    Columns("A:A").NumberFormatLocal = Range("D1").NumberFormatLocal + _
            " " + Range("E1").NumberFormatLocal

End Sub

Enter date/time data in column A. Column B contains the same date values as A and is set to the regional date format. I did this just to compare to our localized date/time to see if it is formatted correctly. You may want to create a regional time formatted column as well.

Column C shows the date/time custom formatted with “m/d/yyyy h:mm:ss AM/PM”.

Now, open Control Panel and change your systems regional language setting.

Close your woorkbook and re-open. Here’s the result for Sanskirt (India). Notice how the custom format of column C uses the localized m/d/yyyy separators but does not use the local AM/PM or the correct order for day followed by month:

Spanish (Columbia):

Yi (PRC):

Yoruba (Nigeria):

Russian (Russia):

English (United Kingdom)

Chinese (Traditional, Taiwan):

InfoPath FormEvents.Sign without Using Full Trust

The Sign event for an InfoPath form is only available in templates with full trust . Here’s a trick to mimic the signed event without full trust by using the ContextChanged event instead.

The idea is to change the focus from the signature box to a hidden textbox and, thereby, fire the ContextChanged event for the form after the signature change event has completed. During the ContextChanged event, the form is editable so your signature data is available and you can copy it to a different form object.

First, create your form with a section to sign and two text boxes. One textbox is for the signature name and the other is for the signature date. Then add another text box that we’ll use to receive focus and fire the ContextChanged event.

Now open Visual Studio Tools for Applications (ALT-SHFT-F12).
Add an event handler for the ContextChanged event of the form and another for the Changed event of the signature.

// NOTE: The following procedure is required by Microsoft Office InfoPath.
// It can be modified using Microsoft Office InfoPath.
public void InternalStartup()
{
   EventManager.FormEvents.ContextChanged += new ContextChangedEventHandler(FormEvents_ContextChanged);
   EventManager.XmlEvents["/my:myFields/my:signatures1/my:signatures2"].Changed += new XmlChangedEventHandler(signatures2_Changed);
}

Define the new event handlers as follows:

public void FormEvents_ContextChanged(object sender, ContextChangedEventArgs e)
{
   if (e.ChangeType == "ContextNode")
   {
      string NodeName = e.Context.Name.ToString();
      XPathNavigator nav = MainDataSource.CreateNavigator();

      if ((NodeName == "my:TextBox-1"))
      {
         //System.Windows.Forms.MessageBox.Show("View Context has changed to: " + e.Context.Name.ToString() + " and CanEdit of this Context is: " + e.Context.CanEdit.ToString());
         nav.SelectSingleNode("//my:TextBox-1", NamespaceManager).SetValue("This text box receives focus during the digital signature changed event handler. The change in focus fires a view context changed event. During the view context changed event the form is editable and we transfer digital signature data. In practice, this text box would be 'hidden' by making its size 0x0. Select the 'View Code' view to see the VSTA C# code.");
         TransferDigitalSignatureData();
      };
         return;
   }
}

public void signatures2_Changed(object sender, XmlEventArgs e)
{
   XPathNavigator nav = MainDataSource.CreateNavigator();
   this.CurrentView.SelectText(nav.SelectSingleNode("//my:TextBox-1", NamespaceManager));
}

Finally, define the TransferDigitalSignatureData function to do whatever is needed with the newly added signature information. I usually want to capture the name and date of the signature so I can promote them to a SharePoint list column or display them on a formatted print view without showing the actual signature box.

private void TransferDigitalSignatureData()
{
   XPathNavigator xnDocument = this.MainDataSource.CreateNavigator();
   XPathNavigator xnEmployeeSig, xnEmployeeSigName, xnEmployeeSigDate; 

   XmlDocument xmlDoc = new XmlDocument();
   Byte[] byteSignature;
   X509Certificate certSignature;
   String strSignature;
   DateTime dateSigned;

   //Employee Signature Fields
   xnEmployeeSig = xnDocument.SelectSingleNode("//my:signatures1/my:signatures2", this.NamespaceManager);
   xnEmployeeSigName = xnDocument.SelectSingleNode("//my:EmployeeSigName", this.NamespaceManager);
   xnEmployeeSigDate = xnDocument.SelectSingleNode("//my:EmployeeSigDate", this.NamespaceManager);

   if (!xnEmployeeSig.Value.Equals(String.Empty))
   {
      xmlDoc.LoadXml(xnEmployeeSig.OuterXml);
      byteSignature = Convert.FromBase64String(xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[2].FirstChild.FirstChild.InnerText);
      certSignature = new X509Certificate(byteSignature);
      strSignature = certSignature.Subject.Substring(3, certSignature.Subject.IndexOf(",") - 3);
      dateSigned = Convert.ToDateTime(xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[3].FirstChild.FirstChild.ChildNodes[1].FirstChild.InnerText);

      xnEmployeeSigName.SetValue(strSignature);
      xnEmployeeSigDate.SetValue(dateSigned.ToShortDateString());
   } 
}

And that’s it! I can’t say if this technique is a best practice or supported by Microsoft or if it will always work in every situation. But when you can’t use a fully trusted template, this trick will allow the signature data to be transferred automatically and will save your users an extra click or two when submitting an InfoPath form.