I have an export script (LS) which uses DateOnly to return a string date…
Set dateTime = New NotesDateTime( item.text )
xlsheet.cells (rows,cols).value = dateTime.dateonly
When run by a 4.6 client this returns the date in the expected format (e.g. 12/05/1999) but using R6 this returns a shortened year for pre-2000 dates (e.g. 12/05/99)
Any quick ideas of a way to format the DateOnly returned value ?
L.
Subject: DateOnly format change?
Use the Format function. There is no point in using a NotesDateTime at all for this kind of thing.You should also be aware that New NotesDateTime initializes the value from a String, so your code might fail if it runs on a machine where the date format is not what you expect. In your example, 12/05/1999, no one can tell whether you mean December or May. I know that Designer Help encourages you to do this, but I would encourage you not to.
Subject: Thanks…
Thank you Rod.
any comment over which of the following is more efficient?
xlsheet.cells (rows,cols).value = Format$( dateTime.dateonly ,“Short Date” )
xlsheet.cells (rows,cols).value = Format$( dateTime.dateonly ,“dd/mm/yyyy” )
Lynne
Subject: RE: Thanks…
I wrote: “There is no point in using a NotesDateTime at all for this kind of thing.” Do not use any NotesDateTime object. Use:Format$(item.Values(0), “dd/mm/yyyy”)
Short Date depends on settings in the machine where the code happens to be running, so it might cause unwelcome surprises in the furute if those settings change. It is better to avoid any dependencies like that.
Subject: Formatting dates
I have to disagree with Rod here.
Whether the user’s computer uses mm/dd/yyyy format or dd/mm/yyyy or yyyy/mm/dd (Japan), Notes and Excel will use the same date format on that computer.
If you hardcode the dd/mm/yyyy format as Rod suggests, then the code will work only on computers whose formatting happens to match that. My computer expects mm/dd/yyyy; if I tried to use an export script that puts the value Format$(item.Values(0), “dd/mm/yyyy”), into Excel, either Excel would not recognize it as a valid date, or it would swap the day and the month.
I don’t understand what’s wrong with your original code (though you could have shorted it to item.datetimevalue.dateonly). In this case, why does it matter if the year is converted to text as 1999 or 99? If Excel recognizes the value as a date, it’ll display it in its own format in any case. On my computer Excel displays 99 whether I type 99 or 1999.
If for some reason you must have a string that uses 4 year digits and is in the workstation’s formatting, there’s no easy way to do it using the programming commands. You can format a field to display it that way, but there’s no argument to Format or @Text that combines those requirements. You would have to code it yourself. E.g.:
Function Local4DigitFormat( ) As String
Static localFormat As String
If localFormat = "" Then
localFormat = Replace(Cstr(Datenumber(2003,1,2)), Split("1,01,2,02,03,2003", ","), Split("mm,mm,dd,dd,yyyy,yyyy", ","))
End If
Local4DigitFormat = localFormat
End Function
…
Set dateTime = New NotesDateTime( item.text )
xlsheet.cells (rows,cols).value = format(item.values(0), Local4DigitFormat)
Incidentally, have you tried this?
xlsheet.cells (rows,cols).value = item.values(0)