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.notesninjas.com/#ExcelDateAmbiguous or http://www.notesninjas.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
Beginning Java Web Services by Andre Tost, Henry Andre Bequet, Meeraj Kunnumpurath, Sean Rhody
Web services are no longer a nascent technology in the programming industry; every major software vendor is rolling out web services-related APIs and products. Today Java developers are successfully adopting web services as the latest technology in application development in order to enable...
See at: Amazon.co.uk | Amazon.com |