< Back
Document Subject: 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://#ExcelDateAmbiguous or http://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..