Notes Ninjas Notes Ninjas
Lotus Notes hints, tips, error messages
Unambiguous Date formats for excel from notes view column

Hint Short Cut: Add this to your code & documentation to help you find this page.
http://www.notestemplates.com/#ExcelDateAmbiguous or http://www.notestemplates.com/A555F9/nn.nsf/ByAlias/ExcelDateAmbiguous


If you are exporting date values, sometimes excel can get confused with d/m/y and m/d/y. Here is some code to get around it.





This is a problem with multivalue dates in particular.

This will make the dates unambiguous in excel.
Put his in the column formula of the view for the date fields in stead of @implode(@text(cdate);"-")
NB cdates is the field holding the date values, so change this.


ms:="Jan":"Feb":"Mar":"Apr":"May":"Jun":"Jul":"Aug":"Sep":"Oct":"Nov":"Dec"
dups := ""
@For(i := 1; i <= @Elements(cdates); i := i + 1;
dups := dups : (
@Text(@Day(cdates[i]))+"-"+ms[@Month(cdates[i])]+"-"+@Text(@Year(cdates[i]) ))
);
@Implode(@Trim(dups);"")


NB:
Excel struggles when they are multivalue, so the cell format will be general not date for the multivalue ones.  
Possibly would have to export each date into a separate cell..

 

 Jump to:     Notes Tips
    Lotus Notes Index 
  Register for Tips
Previous Lotus Notes Tip 
    Next Lotus Notes Tip
Your company logo here?
Adam Foster
Free Cash Making Web Site
Free Revenue Earning Blog Site
 Free Revenue Earning Newsletter Site   
Free Search Pages Free Web stats
Free Contact Us page
Cheap .com domain names
 Need a website or 
   Lotus Notes
  problem solved?
Adam Foster
Lotus Notes and Domino 6 Programming Bible by Brian Benz, Rocky Oliver
Covers all the programming techniques, concepts, and languages used with Notes and Domino, as well as the many new features of Domino 6 Thoroughly examines the Notes and Domino role with Java, XML, Web services, and other IBM products and technologies Provides extensive code and examples, many geared to the needs of administrators High-profile authors, known to their audience through conferences and articles Companion Web site shows example applications from the book running on a live Domino server and provides code download.
See at: Amazon.co.uk | Amazon.com |